explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z3fI : 2

Settings
# exclusive inclusive rows x rows loops node
1. 12.313 780.560 ↑ 290.4 61,568 1

Hash Left Join (cost=5,164,602.13..5,658,066.79 rows=17,879,154 width=292) (actual time=710.421..780.560 rows=61,568 loops=1)

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

CTE pcilarge

3. 22.547 76.251 ↓ 1.0 61,568 1

Hash Join (cost=16.63..6,679.32 rows=61,562 width=47) (actual time=0.307..76.251 rows=61,568 loops=1)

  • Hash Cond: ((m.product_category_id_)::text = (pcslarge.product_category_id_)::text)
4. 53.534 53.534 ↓ 1.0 61,568 1

Seq Scan on product_category_info m (cost=0.00..5,816.21 rows=61,562 width=63) (actual time=0.122..53.534 rows=61,568 loops=1)

  • Filter: ((product_category_type_id_)::text = 'PARTLARGEGROUP'::text)
  • Rows Removed by Filter: 64929
5. 0.107 0.170 ↓ 1.1 357 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
6. 0.063 0.063 ↓ 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.063 rows=357 loops=1)

7.          

CTE pcimiddle

8. 18.788 64.354 ↓ 1.0 61,568 1

Hash Join (cost=16.63..6,676.47 rows=61,355 width=40) (actual time=0.241..64.354 rows=61,568 loops=1)

  • Hash Cond: ((m_1.product_category_id_)::text = (pcsmiddle.product_category_id_)::text)
9. 45.413 45.413 ↓ 1.0 61,568 1

Seq Scan on product_category_info m_1 (cost=0.00..5,816.21 rows=61,355 width=63) (actual time=0.077..45.413 rows=61,568 loops=1)

  • Filter: ((product_category_type_id_)::text = 'PARTMIDDLEGROUP'::text)
  • Rows Removed by Filter: 64929
10. 0.105 0.153 ↓ 1.1 357 1

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

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

12.          

CTE main

13. 143.790 731.320 ↑ 290.4 61,568 1

Sort (cost=5,106,540.39..5,151,238.27 rows=17,879,154 width=145) (actual time=710.360..731.320 rows=61,568 loops=1)

  • Sort Key: (COALESCE(pr.product_code_, ''::character varying))
  • Sort Method: external merge Disk: 7088kB
14. 110.362 587.530 ↑ 290.4 61,568 1

Hash Join (cost=20,645.64..71,915.10 rows=17,879,154 width=145) (actual time=367.832..587.530 rows=61,568 loops=1)

  • Hash Cond: ((pcilarge.product_id_)::text = (pr.product_id_)::text)
15. 109.678 109.678 ↓ 1.0 61,568 1

CTE Scan on pcilarge (cost=0.00..1,231.24 rows=61,562 width=130) (actual time=0.309..109.678 rows=61,568 loops=1)

16. 29.755 367.490 ↓ 1.1 61,568 1

Hash (cost=18,217.58..18,217.58 rows=58,085 width=211) (actual time=367.490..367.490 rows=61,568 loops=1)

  • Buckets: 2048 Batches: 4 Memory Usage: 2332kB
17. 13.217 337.735 ↓ 1.1 61,568 1

Hash Left Join (cost=12,229.86..18,217.58 rows=58,085 width=211) (actual time=160.120..337.735 rows=61,568 loops=1)

  • Hash Cond: ((pr.product_id_)::text = (prabc.product_id_)::text)
18. 73.527 324.471 ↓ 1.1 61,568 1

Hash Join (cost=12,223.19..17,993.08 rows=58,085 width=209) (actual time=160.061..324.471 rows=61,568 loops=1)

  • Hash Cond: ((pcimiddle.product_id_)::text = (pr.product_id_)::text)
19. 91.154 91.154 ↓ 1.0 61,568 1

CTE Scan on pcimiddle (cost=0.00..1,227.10 rows=61,355 width=130) (actual time=0.242..91.154 rows=61,568 loops=1)

20. 24.265 159.790 ↑ 1.0 61,568 1

Hash (cost=10,642.22..10,642.22 rows=62,718 width=79) (actual time=159.790..159.790 rows=61,568 loops=1)

  • Buckets: 4096 Batches: 2 Memory Usage: 3427kB
21. 65.115 135.525 ↑ 1.0 61,568 1

Hash Left Join (cost=3,678.70..10,642.22 rows=62,718 width=79) (actual time=33.408..135.525 rows=61,568 loops=1)

  • Hash Cond: ((pr.product_id_)::text = (ypr.product_id_)::text)
22. 37.611 37.611 ↑ 1.0 61,568 1

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

  • Filter: (((site_id_)::text = '0000'::text) AND ((product_classification_id_)::text = 'C059PART'::text))
  • Rows Removed by Filter: 3540
23. 17.504 32.799 ↓ 1.0 63,674 1

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

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

25. 0.000 0.047 ↓ 0.0 0 1

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

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

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

  • Join Filter: (((abc.site_id_)::text = (prabc.site_id_)::text) AND ((abc.abc_id_)::text = (prabc.abc_id_)::text))
27. 0.045 0.045 ↓ 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.045..0.045 rows=0 loops=1)

  • Index Cond: (site_id_ = 'JA0022'::text)
  • Heap Fetches: 0
28. 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)
29.          

CTE pclastcost

30. 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))
31.          

CTE pcaver

32. 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))
33. 15.984 768.243 ↑ 290.4 61,568 1

Hash Left Join (cost=0.03..425,523.90 rows=17,879,154 width=358) (actual time=710.406..768.243 rows=61,568 loops=1)

  • Hash Cond: ((main.product_id_)::text = (pclastcost.product_id_)::text)
34. 752.236 752.236 ↑ 290.4 61,568 1

CTE Scan on main (cost=0.00..357,583.08 rows=17,879,154 width=326) (actual time=710.363..752.236 rows=61,568 loops=1)

35. 0.000 0.023 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
36. 0.023 0.023 ↓ 0.0 0 1

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

37. 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
38. 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)

Planning time : 1.901 ms
Execution time : 789.725 ms