explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h4SB : V2

Settings
# exclusive inclusive rows x rows loops node
1. 27.689 152.924 ↑ 3.9 14,984 1

Sort (cost=253,367.87..253,512.31 rows=57,779 width=876) (actual time=151.807..152.924 rows=14,984 loops=1)

  • Sort Key: sp.site_id_, pro.product_code_, identifier.frame_no_
  • Sort Method: quicksort Memory: 3370kB
2.          

CTE sp

3. 11.119 11.119 ↑ 8.1 14,984 1

Index Scan using cmm_serialized_product_info_idx01 on cmm_serialized_product_info seria (cost=0.51..6,641.25 rows=121,929 width=62) (actual time=0.032..11.119 rows=14,984 loops=1)

  • Index Cond: ((site_id_)::text = ANY ('{V100001,V100002,V123002,V123004,VA00001,VA00002,VA00003,VA00004,VA00005,VA00006,VA00007,VA00009,VA00010,VA00011,VA00012,VA23002,VA23005,VAVA001,VAVA003,VC23002,VE00001,VF00002,VH23001,VI23001,VI23002,VJ23001,VK23001,VL23001,VM23001,VM23002,VM23003,VM23004,VP23002,VA23001,VA23004,VAVA002,9V0001,9VM001,V123003,V500001,VA00008,VA23003,VA23006,VA23007,VA23008,VA23009,VB23002,VB23003,VB23005,VB23101,VB23103,VC23001,VD23001,VF00001,VG00001,VG23001,VL23002,VM00001,VP00001,VP23001,VQ00001}'::text[]))
4.          

CTE pro

5. 0.823 2.166 ↓ 22.6 2,192 1

Hash Left Join (cost=12.89..1,696.34 rows=97 width=171) (actual time=0.048..2.166 rows=2,192 loops=1)

  • Hash Cond: ((pro_1.color_id_)::text = (color.color_id_)::text)
6. 1.321 1.321 ↓ 22.6 2,192 1

Index Scan using index_product_01 on cmm_product pro_1 (cost=0.42..1,683.48 rows=97 width=110) (actual time=0.021..1.321 rows=2,192 loops=1)

  • Index Cond: ((product_classification_id_)::text = 'C059GOODS'::text)
  • Filter: (product_level_ = 1)
  • Rows Removed by Filter: 930
7. 0.015 0.022 ↑ 2.4 45 1

Hash (cost=11.10..11.10 rows=110 width=196) (actual time=0.022..0.022 rows=45 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
8. 0.007 0.007 ↑ 2.4 45 1

Seq Scan on cmm_color_info color (cost=0.00..11.10 rows=110 width=196) (actual time=0.004..0.007 rows=45 loops=1)

9. 10.438 125.235 ↑ 3.9 14,984 1

Nested Loop (cost=531.95..224,517.96 rows=57,779 width=876) (actual time=9.358..125.235 rows=14,984 loops=1)

10. 5.284 39.877 ↑ 3.9 14,984 1

Hash Join (cost=531.39..5,039.87 rows=57,779 width=924) (actual time=9.341..39.877 rows=14,984 loops=1)

  • Hash Cond: ((sp.site_id_)::text = (party.party_code_)::text)
11. 7.331 29.180 ↑ 3.9 14,984 1

Hash Join (cost=3.15..3,490.33 rows=59,136 width=908) (actual time=3.907..29.180 rows=14,984 loops=1)

  • Hash Cond: ((sp.product_id_)::text = (pro.product_id_)::text)
12. 17.987 17.987 ↑ 8.1 14,984 1

CTE Scan on sp (cost=0.00..2,438.58 rows=121,929 width=294) (actual time=0.036..17.987 rows=14,984 loops=1)

13. 0.658 3.862 ↓ 22.6 2,192 1

Hash (cost=1.94..1.94 rows=97 width=810) (actual time=3.862..3.862 rows=2,192 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 203kB
14. 3.204 3.204 ↓ 22.6 2,192 1

CTE Scan on pro (cost=0.00..1.94 rows=97 width=810) (actual time=0.049..3.204 rows=2,192 loops=1)

15. 0.890 5.413 ↑ 1.0 3,875 1

Hash (cost=479.80..479.80 rows=3,875 width=23) (actual time=5.413..5.413 rows=3,875 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 213kB
16. 2.388 4.523 ↑ 1.0 3,875 1

Hash Join (cost=163.24..479.80 rows=3,875 width=23) (actual time=1.600..4.523 rows=3,875 loops=1)

  • Hash Cond: ((org.party_id_)::text = (party.party_id_)::text)
17. 0.544 0.544 ↑ 1.0 3,875 1

Seq Scan on cmm_organization_info org (cost=0.00..248.75 rows=3,875 width=52) (actual time=0.002..0.544 rows=3,875 loops=1)

18. 0.849 1.591 ↑ 1.0 3,958 1

Hash (cost=113.66..113.66 rows=3,966 width=43) (actual time=1.591..1.591 rows=3,958 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 294kB
19. 0.742 0.742 ↑ 1.0 3,958 1

Seq Scan on cmm_party_info party (cost=0.00..113.66 rows=3,966 width=43) (actual time=0.004..0.742 rows=3,958 loops=1)

20. 74.920 74.920 ↑ 1.0 1 14,984

Index Scan using serialized_product_identifier_idx01 on cmm_serialized_product_identifier identifier (cost=0.56..3.79 rows=1 width=74) (actual time=0.005..0.005 rows=1 loops=14,984)

  • Index Cond: ((serialized_product_id_)::text = (sp.serialized_product_id_)::text)
Planning time : 0.711 ms
Execution time : 154.395 ms