explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tC2G : 2

Settings
# exclusive inclusive rows x rows loops node
1. 148.799 718.392 ↓ 1.1 61,568 1

Sort (cost=41,072.78..41,218.31 rows=58,213 width=69) (actual time=701.296..718.392 rows=61,568 loops=1)

  • Sort Key: (COALESCE(pr.product_code_, ''::character varying))
  • Sort Method: external merge Disk: 5232kB
2. 42.276 569.593 ↓ 1.1 61,568 1

Hash Left Join (cost=20,493.00..34,758.00 rows=58,213 width=69) (actual time=269.825..569.593 rows=61,568 loops=1)

  • Hash Cond: ((pr.product_id_)::text = (pcaver.product_id_)::text)
3. 14.346 527.313 ↓ 1.1 61,568 1

Hash Left Join (cost=20,488.54..34,098.64 rows=58,213 width=100) (actual time=269.798..527.313 rows=61,568 loops=1)

  • Hash Cond: ((pr.product_id_)::text = (pclastcost.product_id_)::text)
4. 78.464 512.942 ↓ 1.1 61,568 1

Hash Left Join (cost=20,484.09..33,875.88 rows=58,213 width=94) (actual time=269.755..512.942 rows=61,568 loops=1)

  • Hash Cond: ((pr.product_id_)::text = (ypr.product_id_)::text)
5. 13.863 398.195 ↓ 1.1 61,568 1

Hash Left Join (cost=16,805.39..27,039.66 rows=58,213 width=88) (actual time=233.438..398.195 rows=61,568 loops=1)

  • Hash Cond: ((pr.product_id_)::text = (prabc.product_id_)::text)
6. 81.433 384.306 ↓ 1.1 61,568 1

Hash Join (cost=16,798.72..26,814.68 rows=58,213 width=86) (actual time=233.399..384.306 rows=61,568 loops=1)

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

Hash Join (cost=16.63..6,681.81 rows=61,743 width=47) (actual time=0.380..69.909 rows=61,568 loops=1)

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

Seq Scan on product_category_info pcilarge (cost=0.00..5,816.21 rows=61,743 width=63) (actual time=0.162..51.460 rows=61,568 loops=1)

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

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

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

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

11. 29.457 232.964 ↓ 1.1 61,568 1

Hash (cost=15,036.91..15,036.91 rows=58,015 width=113) (actual time=232.964..232.964 rows=61,568 loops=1)

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

Hash Join (cost=5,287.64..15,036.91 rows=58,015 width=113) (actual time=67.347..203.507 rows=61,568 loops=1)

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

Hash Join (cost=16.63..6,676.65 rows=61,368 width=40) (actual time=0.226..69.242 rows=61,568 loops=1)

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

Seq Scan on product_category_info pcimiddle (cost=0.00..5,816.21 rows=61,368 width=63) (actual time=0.078..51.739 rows=61,568 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
16. 0.046 0.046 ↓ 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.046 rows=357 loops=1)

17. 25.321 67.065 ↓ 1.0 61,568 1

Hash (cost=3,719.62..3,719.62 rows=61,551 width=73) (actual time=67.065..67.065 rows=61,568 loops=1)

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

Seq Scan on product pr (cost=0.00..3,719.62 rows=61,551 width=73) (actual time=0.713..41.744 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.000 0.026 ↓ 0.0 0 1

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

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

Nested Loop Left Join (cost=0.83..6.66 rows=1 width=39) (actual time=0.026..0.026 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.025 0.025 ↓ 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.025..0.025 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. 17.627 36.283 ↓ 1.0 63,674 1

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

  • Buckets: 8192 Batches: 2 Memory Usage: 2349kB
24. 18.656 18.656 ↓ 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.009..18.656 rows=63,674 loops=1)

25. 0.001 0.025 ↓ 0.0 0 1

Hash (cost=4.44..4.44 rows=1 width=43) (actual time=0.025..0.025 rows=0 loops=1)

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

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

  • Index Cond: (((site_id_)::text = 'JA0022'::text) AND ((product_cost_component_category_id_)::text = 'C034RECEIVECOST'::text))
27. 0.001 0.004 ↓ 0.0 0 1

Hash (cost=4.44..4.44 rows=1 width=43) (actual time=0.004..0.004 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
28. 0.003 0.003 ↓ 0.0 0 1

Index Scan using product_cost_idx04 on product_cost pcaver (cost=0.42..4.44 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))
Planning time : 4.059 ms
Execution time : 724.703 ms