explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FMqi : 3

Settings
# exclusive inclusive rows x rows loops node
1. 26.950 838.089 ↑ 290.4 61,568 1

Hash Left Join (cost=5,164,602.09..5,745,685.89 rows=17,879,154 width=240) (actual time=743.085..838.089 rows=61,568 loops=1)

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

CTE pcilarge

3. 18.338 62.150 ↓ 1.0 61,568 1

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

  • Hash Cond: ((m.product_category_id_)::text = (pcslarge.product_category_id_)::text)
4. 43.629 43.629 ↓ 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.131..43.629 rows=61,568 loops=1)

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

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

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

7.          

CTE pcimiddle

8. 17.986 66.083 ↓ 1.0 61,568 1

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

  • Hash Cond: ((m_1.product_category_id_)::text = (pcsmiddle.product_category_id_)::text)
9. 47.961 47.961 ↓ 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.078..47.961 rows=61,568 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
11. 0.052 0.052 ↓ 1.1 357 1

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

12.          

CTE main

13. 157.944 769.934 ↑ 290.4 61,568 1

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

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

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

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

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

16. 33.426 410.144 ↓ 1.1 61,568 1

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

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

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

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

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

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

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

20. 27.198 184.240 ↑ 1.0 61,568 1

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

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

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

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

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

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

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

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

25. 0.000 0.035 ↓ 0.0 0 1

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

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

Nested Loop Left Join (cost=0.83..6.66 rows=1 width=39) (actual time=0.035..0.035 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.034 0.034 ↓ 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.034..0.034 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. 16.737 811.135 ↑ 290.4 61,568 1

Hash Left Join (cost=4.01..424,639.57 rows=17,879,154 width=332) (actual time=743.064..811.135 rows=61,568 loops=1)

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

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

31. 0.000 0.087 ↓ 0.0 0 1

Hash (cost=4.00..4.00 rows=1 width=43) (actual time=0.087..0.087 rows=0 loops=1)

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

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

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

Hash (cost=4.00..4.00 rows=1 width=43) (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

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

  • Index Cond: (((site_id_)::text = 'JA0022'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
Planning time : 1.948 ms
Execution time : 847.533 ms