explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nx9k : 1

Settings
# exclusive inclusive rows x rows loops node
1. 15.138 656.028 ↓ 1.0 61,568 1

Hash Left Join (cost=41,051.19..42,670.09 rows=58,656 width=292) (actual time=579.921..656.028 rows=61,568 loops=1)

  • Hash Cond: ((main.product_id_)::text = (pcaver.product_id_)::text)
2.          

CTE main

3. 141.911 601.497 ↓ 1.0 61,568 1

Sort (cost=40,896.48..41,043.12 rows=58,656 width=94) (actual time=579.865..601.497 rows=61,568 loops=1)

  • Sort Key: (COALESCE(pr.product_code_, ''::character varying))
  • Sort Method: external merge Disk: 7088kB
4. 82.590 459.586 ↓ 1.0 61,568 1

Hash Left Join (cost=20,556.48..34,100.93 rows=58,656 width=94) (actual time=238.857..459.586 rows=61,568 loops=1)

  • Hash Cond: ((pr.product_id_)::text = (ypr.product_id_)::text)
5. 12.891 345.066 ↓ 1.0 61,568 1

Hash Left Join (cost=16,877.78..27,108.29 rows=58,656 width=88) (actual time=206.896..345.066 rows=61,568 loops=1)

  • Hash Cond: ((pr.product_id_)::text = (prabc.product_id_)::text)
6. 65.511 332.152 ↓ 1.0 61,568 1

Hash Join (cost=16,871.11..26,881.65 rows=58,656 width=86) (actual time=206.862..332.152 rows=61,568 loops=1)

  • Hash Cond: ((pcilarge.product_id_)::text = (pr.product_id_)::text)
7. 17.658 60.098 ↑ 1.0 61,568 1

Hash Join (cost=16.63..6,681.69 rows=61,729 width=47) (actual time=0.292..60.098 rows=61,568 loops=1)

  • Hash Cond: ((pcilarge.product_category_id_)::text = (pcslarge.product_category_id_)::text)
8. 42.283 42.283 ↑ 1.0 61,568 1

Seq Scan on product_category_info pcilarge (cost=0.00..5,816.29 rows=61,729 width=63) (actual time=0.129..42.283 rows=61,568 loops=1)

  • Filter: ((product_category_type_id_)::text = 'PARTLARGEGROUP'::text)
  • Rows Removed by Filter: 64929
9. 0.072 0.157 ↓ 1.1 357 1

Hash (cost=12.39..12.39 rows=339 width=42) (actual time=0.157..0.157 rows=357 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
10. 0.085 0.085 ↓ 1.1 357 1

Seq Scan on product_category_setting pcslarge (cost=0.00..12.39 rows=339 width=42) (actual time=0.004..0.085 rows=357 loops=1)

11. 24.500 206.543 ↓ 1.1 61,568 1

Hash (cost=15,104.33..15,104.33 rows=58,172 width=113) (actual time=206.543..206.543 rows=61,568 loops=1)

  • Buckets: 4096 Batches: 4 Memory Usage: 2223kB
12. 67.343 182.043 ↓ 1.1 61,568 1

Hash Join (cost=5,334.34..15,104.33 rows=58,172 width=113) (actual time=53.166..182.043 rows=61,568 loops=1)

  • Hash Cond: ((pcimiddle.product_id_)::text = (pr.product_id_)::text)
13. 17.372 61.757 ↓ 1.0 61,568 1

Hash Join (cost=16.63..6,677.81 rows=61,447 width=40) (actual time=0.196..61.757 rows=61,568 loops=1)

  • Hash Cond: ((pcimiddle.product_category_id_)::text = (pcsmiddle.product_category_id_)::text)
14. 44.267 44.267 ↓ 1.0 61,568 1

Seq Scan on product_category_info pcimiddle (cost=0.00..5,816.29 rows=61,447 width=63) (actual time=0.074..44.267 rows=61,568 loops=1)

  • Filter: ((product_category_type_id_)::text = 'PARTMIDDLEGROUP'::text)
  • Rows Removed by Filter: 64929
15. 0.067 0.118 ↓ 1.1 357 1

Hash (cost=12.39..12.39 rows=339 width=35) (actual time=0.118..0.118 rows=357 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
16. 0.051 0.051 ↓ 1.1 357 1

Seq Scan on product_category_setting pcsmiddle (cost=0.00..12.39 rows=339 width=35) (actual time=0.001..0.051 rows=357 loops=1)

17. 18.487 52.943 ↑ 1.0 61,568 1

Hash (cost=3,736.74..3,736.74 rows=62,718 width=73) (actual time=52.943..52.943 rows=61,568 loops=1)

  • Buckets: 4096 Batches: 2 Memory Usage: 3233kB
18. 34.456 34.456 ↑ 1.0 61,568 1

Seq Scan on product pr (cost=0.00..3,736.74 rows=62,718 width=73) (actual time=0.596..34.456 rows=61,568 loops=1)

  • Filter: (((site_id_)::text = '0000'::text) AND ((product_classification_id_)::text = 'C059PART'::text))
  • Rows Removed by Filter: 3540
19. 0.001 0.023 ↓ 0.0 0 1

Hash (cost=6.66..6.66 rows=1 width=39) (actual time=0.023..0.023 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
20. 0.001 0.022 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.83..6.66 rows=1 width=39) (actual time=0.022..0.022 rows=0 loops=1)

  • Join Filter: (((abc.site_id_)::text = (prabc.site_id_)::text) AND ((abc.abc_id_)::text = (prabc.abc_id_)::text))
21. 0.021 0.021 ↓ 0.0 0 1

Index Only Scan using product_abc_info_idx01 on product_abc_info prabc (cost=0.55..2.57 rows=1 width=75) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: (site_id_ = 'JA0022'::text)
  • Heap Fetches: 0
22. 0.000 0.000 ↓ 0.0 0

Index Scan using "index_siteId_05" on abc_definition_info abc (cost=0.28..4.07 rows=1 width=40) (never executed)

  • Index Cond: ((site_id_)::text = 'JA0022'::text)
23. 16.166 31.930 ↓ 1.0 63,674 1

Hash (cost=2,323.31..2,323.31 rows=63,631 width=43) (actual time=31.930..31.930 rows=63,674 loops=1)

  • Buckets: 8192 Batches: 2 Memory Usage: 2349kB
24. 15.764 15.764 ↓ 1.0 63,674 1

Seq Scan on yimm_product_info ypr (cost=0.00..2,323.31 rows=63,631 width=43) (actual time=0.007..15.764 rows=63,674 loops=1)

25.          

CTE pclastcost

26. 0.021 0.021 ↓ 0.0 0 1

Index Scan using product_cost_idx04 on product_cost (cost=0.42..4.00 rows=1 width=43) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: (((site_id_)::text = 'JA0022'::text) AND ((product_cost_component_category_id_)::text = 'C034RECEIVECOST'::text))
27.          

CTE pcaver

28. 0.003 0.003 ↓ 0.0 0 1

Index Scan using product_cost_idx04 on product_cost pclastcost_1 (cost=0.42..4.00 rows=1 width=43) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (((site_id_)::text = 'JA0022'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
29. 17.067 640.886 ↓ 1.0 61,568 1

Hash Left Join (cost=0.03..1,396.04 rows=58,656 width=358) (actual time=579.910..640.886 rows=61,568 loops=1)

  • Hash Cond: ((main.product_id_)::text = (pclastcost.product_id_)::text)
30. 623.795 623.795 ↓ 1.0 61,568 1

CTE Scan on main (cost=0.00..1,173.12 rows=58,656 width=326) (actual time=579.868..623.795 rows=61,568 loops=1)

31. 0.000 0.024 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=130) (actual time=0.024..0.024 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
32. 0.024 0.024 ↓ 0.0 0 1

CTE Scan on pclastcost (cost=0.00..0.02 rows=1 width=130) (actual time=0.024..0.024 rows=0 loops=1)

33. 0.000 0.004 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=130) (actual time=0.004..0.004 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
34. 0.004 0.004 ↓ 0.0 0 1

CTE Scan on pcaver (cost=0.00..0.02 rows=1 width=130) (actual time=0.004..0.004 rows=0 loops=1)