explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BGre : V4

Settings
# exclusive inclusive rows x rows loops node
1. 32.706 166.061 ↑ 1.2 14,984 1

Sort (cost=86,890.89..86,935.24 rows=17,741 width=1,078) (actual time=164.039..166.061 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. 12.056 12.056 ↑ 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.028..12.056 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 org

5. 0.039 0.313 ↓ 1.0 61 1

Nested Loop (cost=77.91..351.87 rows=60 width=23) (actual time=0.090..0.313 rows=61 loops=1)

6. 0.014 0.091 ↑ 1.0 61 1

Bitmap Heap Scan on cmm_party_info party (cost=77.63..136.96 rows=61 width=43) (actual time=0.081..0.091 rows=61 loops=1)

  • Recheck Cond: ((party_code_)::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[]))
  • Heap Blocks: exact=3
7. 0.077 0.077 ↑ 1.0 61 1

Bitmap Index Scan on cmm_party_info_idx01 (cost=0.00..77.61 rows=61 width=0) (actual time=0.077..0.077 rows=61 loops=1)

  • Index Cond: ((party_code_)::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[]))
8. 0.183 0.183 ↑ 1.0 1 61

Index Scan using pk_cmm_organization_info on cmm_organization_info org_1 (cost=0.28..3.51 rows=1 width=52) (actual time=0.003..0.003 rows=1 loops=61)

  • Index Cond: ((party_id_)::text = (party.party_id_)::text)
9.          

CTE pro

10. 0.814 2.080 ↓ 22.6 2,192 1

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

  • Hash Cond: ((pro_1.color_id_)::text = (color.color_id_)::text)
11. 1.243 1.243 ↓ 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.026..1.243 rows=2,192 loops=1)

  • Index Cond: ((product_classification_id_)::text = 'C059GOODS'::text)
  • Filter: (product_level_ = 1)
  • Rows Removed by Filter: 930
12. 0.008 0.023 ↑ 2.4 45 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
13. 0.015 0.015 ↑ 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.015 rows=45 loops=1)

14. 6.711 133.355 ↑ 1.2 14,984 1

Nested Loop (cost=5.67..70,971.88 rows=17,741 width=1,078) (actual time=4.166..133.355 rows=14,984 loops=1)

15. 7.395 36.740 ↑ 1.2 14,984 1

Hash Join (cost=5.10..3,581.29 rows=17,741 width=1,126) (actual time=4.154..36.740 rows=14,984 loops=1)

  • Hash Cond: ((sp.product_id_)::text = (pro.product_id_)::text)
16. 5.874 25.592 ↑ 2.4 14,984 1

Hash Join (cost=1.95..3,263.55 rows=36,579 width=512) (actual time=0.395..25.592 rows=14,984 loops=1)

  • Hash Cond: ((sp.site_id_)::text = (org.party_code_)::text)
17. 19.360 19.360 ↑ 8.1 14,984 1

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

18. 0.016 0.358 ↓ 1.0 61 1

Hash (cost=1.20..1.20 rows=60 width=276) (actual time=0.358..0.358 rows=61 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
19. 0.342 0.342 ↓ 1.0 61 1

CTE Scan on org (cost=0.00..1.20 rows=60 width=276) (actual time=0.092..0.342 rows=61 loops=1)

20. 0.602 3.753 ↓ 22.6 2,192 1

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

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

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

22. 89.904 89.904 ↑ 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.006..0.006 rows=1 loops=14,984)

  • Index Cond: ((serialized_product_id_)::text = (sp.serialized_product_id_)::text)
Planning time : 0.703 ms
Execution time : 167.883 ms