explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FaEB

Settings
# exclusive inclusive rows x rows loops node
1. 20.454 794.038 ↓ 1.0 61,569 1

Hash Left Join (cost=52,774.31..95,040.72 rows=61,368 width=1,354) (actual time=292.227..794.038 rows=61,569 loops=1)

  • Hash Cond: ((temp.product_id_)::text = (pss.product_id_)::text)
2.          

CTE temp

3. 81.169 330.557 ↓ 1.0 61,568 1

Hash Join (cost=8,921.26..21,947.16 rows=61,368 width=117) (actual time=125.033..330.557 rows=61,568 loops=1)

  • Hash Cond: ((pcimiddle.product_id_)::text = (pr.product_id_)::text)
4. 59.645 185.865 ↓ 1.0 61,568 1

Hash Join (cost=3,695.32..12,949.29 rows=61,368 width=87) (actual time=61.465..185.865 rows=61,568 loops=1)

  • Hash Cond: ((pcimiddle.product_id_)::text = (ypr.product_id_)::text)
5. 19.627 65.134 ↓ 1.0 61,568 1

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

  • Hash Cond: ((pcimiddle.product_category_id_)::text = (pcsmiddle.product_category_id_)::text)
6. 45.349 45.349 ↓ 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.134..45.349 rows=61,568 loops=1)

  • Filter: ((product_category_type_id_)::text = 'PARTMIDDLEGROUP'::text)
  • Rows Removed by Filter: 64929
7. 0.090 0.158 ↓ 1.1 357 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
8. 0.068 0.068 ↓ 1.1 357 1

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

9. 29.035 61.086 ↓ 1.0 63,674 1

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

  • Buckets: 8192 Batches: 2 Memory Usage: 2502kB
10. 32.051 32.051 ↓ 1.0 63,674 1

Seq Scan on yimm_product_info ypr (cost=0.00..2,323.31 rows=63,631 width=47) (actual time=0.005..32.051 rows=63,674 loops=1)

11. 31.901 63.523 ↑ 1.0 65,108 1

Hash (cost=3,394.08..3,394.08 rows=65,108 width=104) (actual time=63.523..63.523 rows=65,108 loops=1)

  • Buckets: 4096 Batches: 4 Memory Usage: 2221kB
12. 31.622 31.622 ↑ 1.0 65,108 1

Seq Scan on product pr (cost=0.00..3,394.08 rows=65,108 width=104) (actual time=0.010..31.622 rows=65,108 loops=1)

13. 15.136 773.568 ↓ 1.0 61,569 1

Hash Left Join (cost=30,815.39..72,695.18 rows=61,368 width=1,354) (actual time=292.198..773.568 rows=61,569 loops=1)

  • Hash Cond: ((temp.product_id_)::text = (ppi.product_id_)::text)
14. 36.361 758.268 ↓ 1.0 61,569 1

Hash Left Join (cost=30,664.02..71,828.88 rows=61,368 width=1,352) (actual time=292.022..758.268 rows=61,569 loops=1)

  • Hash Cond: ((temp.product_id_)::text = (pcavg.product_id_)::text)
15. 67.142 714.825 ↓ 1.0 61,569 1

Hash Left Join (cost=21,392.19..53,673.60 rows=61,368 width=1,346) (actual time=284.921..714.825 rows=61,569 loops=1)

  • Hash Cond: ((temp.product_id_)::text = (pclast.product_id_)::text)
16. 37.882 623.533 ↓ 1.0 61,568 1

Hash Left Join (cost=11,620.81..36,438.95 rows=61,368 width=1,340) (actual time=260.734..623.533 rows=61,568 loops=1)

  • Hash Cond: (((temp.product_id_)::text = (prl.to_product_id_)::text) AND ((temp.site_id_)::text = (prl.site_id_)::text))
17. 69.733 522.088 ↓ 1.0 61,568 1

Hash Left Join (cost=5,459.09..24,599.08 rows=61,368 width=1,426) (actual time=197.151..522.088 rows=61,568 loops=1)

  • Hash Cond: ((temp.product_id_)::text = (prabc.product_id_)::text)
18. 380.297 380.297 ↓ 1.0 61,568 1

CTE Scan on temp (cost=0.00..1,227.36 rows=61,368 width=1,424) (actual time=125.038..380.297 rows=61,568 loops=1)

19. 25.269 72.058 ↓ 1.1 61,568 1

Hash (cost=4,776.01..4,776.01 rows=54,647 width=39) (actual time=72.058..72.058 rows=61,568 loops=1)

  • Buckets: 8192 Batches: 2 (originally 1) Memory Usage: 4097kB
20. 24.987 46.789 ↓ 1.1 61,568 1

Hash Left Join (cost=185.12..4,776.01 rows=54,647 width=39) (actual time=0.316..46.789 rows=61,568 loops=1)

  • Hash Cond: (((prabc.site_id_)::text = (abc.site_id_)::text) AND ((prabc.abc_id_)::text = (abc.abc_id_)::text))
21. 21.534 21.534 ↓ 1.1 61,568 1

Index Only Scan using product_abc_info_idx01 on product_abc_info prabc (cost=0.55..4,168.88 rows=54,647 width=75) (actual time=0.025..21.534 rows=61,568 loops=1)

  • Index Cond: (site_id_ = 'UA2201'::text)
  • Heap Fetches: 0
22. 0.103 0.268 ↑ 1.0 192 1

Hash (cost=181.69..181.69 rows=192 width=40) (actual time=0.268..0.268 rows=192 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
23. 0.115 0.165 ↑ 1.0 192 1

Bitmap Heap Scan on abc_definition_info abc (cost=5.77..181.69 rows=192 width=40) (actual time=0.060..0.165 rows=192 loops=1)

  • Recheck Cond: ((site_id_)::text = 'UA2201'::text)
  • Heap Blocks: exact=18
24. 0.050 0.050 ↑ 1.0 192 1

Bitmap Index Scan on "index_siteId_05" (cost=0.00..5.72 rows=192 width=0) (actual time=0.050..0.050 rows=192 loops=1)

  • Index Cond: ((site_id_)::text = 'UA2201'::text)
25. 2.190 63.563 ↓ 1.7 6,879 1

Hash (cost=6,100.44..6,100.44 rows=4,085 width=54) (actual time=63.563..63.563 rows=6,879 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 585kB
26. 9.786 61.373 ↓ 1.7 6,879 1

Hash Left Join (cost=4,843.93..6,100.44 rows=4,085 width=54) (actual time=48.392..61.373 rows=6,879 loops=1)

  • Hash Cond: ((prl.from_product_id_)::text = (prsp.product_id_)::text)
27. 3.463 3.463 ↓ 1.7 6,879 1

Seq Scan on product_relation prl (cost=0.00..445.03 rows=4,085 width=79) (actual time=0.194..3.463 rows=6,879 loops=1)

  • Filter: (((from_date_)::text <= '20190527'::text) AND ((to_date_)::text >= '20190527'::text) AND ((product_relation_classification_id_)::text = 'SUPERSEDING'::text))
  • Rows Removed by Filter: 2106
28. 19.907 48.124 ↑ 1.0 65,108 1

Hash (cost=3,394.08..3,394.08 rows=65,108 width=49) (actual time=48.124..48.124 rows=65,108 loops=1)

  • Buckets: 8192 Batches: 2 Memory Usage: 2604kB
29. 28.217 28.217 ↑ 1.0 65,108 1

Seq Scan on product prsp (cost=0.00..3,394.08 rows=65,108 width=49) (actual time=0.006..28.217 rows=65,108 loops=1)

30. 8.705 24.150 ↓ 1.4 32,474 1

Hash (cost=9,472.06..9,472.06 rows=23,946 width=43) (actual time=24.150..24.150 rows=32,474 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 2393kB
31. 8.755 15.445 ↓ 1.4 32,474 1

Bitmap Heap Scan on product_cost pclast (cost=1,461.87..9,472.06 rows=23,946 width=43) (actual time=6.812..15.445 rows=32,474 loops=1)

  • Recheck Cond: (((site_id_)::text = 'UA2201'::text) AND ((product_cost_component_category_id_)::text = 'C034RECEIVECOST'::text))
  • Heap Blocks: exact=866
32. 6.690 6.690 ↓ 1.4 32,474 1

Bitmap Index Scan on product_cost_idx04 (cost=0.00..1,455.88 rows=23,946 width=0) (actual time=6.690..6.690 rows=32,474 loops=1)

  • Index Cond: (((site_id_)::text = 'UA2201'::text) AND ((product_cost_component_category_id_)::text = 'C034RECEIVECOST'::text))
33. 2.712 7.082 ↑ 1.9 9,486 1

Hash (cost=9,043.22..9,043.22 rows=18,289 width=43) (actual time=7.082..7.082 rows=9,486 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 700kB
34. 2.621 4.370 ↑ 1.9 9,486 1

Bitmap Heap Scan on product_cost pcavg (cost=1,117.88..9,043.22 rows=18,289 width=43) (actual time=1.784..4.370 rows=9,486 loops=1)

  • Recheck Cond: (((site_id_)::text = 'UA2201'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
  • Heap Blocks: exact=255
35. 1.749 1.749 ↑ 1.9 9,486 1

Bitmap Index Scan on product_cost_idx04 (cost=0.00..1,113.31 rows=18,289 width=0) (actual time=1.749..1.749 rows=9,486 loops=1)

  • Index Cond: (((site_id_)::text = 'UA2201'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
36. 0.001 0.164 ↓ 0.0 0 1

Hash (cost=150.16..150.16 rows=96 width=38) (actual time=0.164..0.164 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
37. 0.000 0.163 ↓ 0.0 0 1

Bitmap Heap Scan on product_monthly_para_info ppi (cost=60.90..150.16 rows=96 width=38) (actual time=0.163..0.163 rows=0 loops=1)

  • Recheck Cond: (((site_id_)::text = 'UA2201'::text) AND ((product_control_type_id_)::text = 'C509PARTSSUSPENDTARGET'::text))
38. 0.163 0.163 ↓ 0.0 0 1

Bitmap Index Scan on product_monthly_para_info_idx01 (cost=0.00..60.87 rows=96 width=0) (actual time=0.163..0.163 rows=0 loops=1)

  • Index Cond: (((site_id_)::text = 'UA2201'::text) AND ((product_control_type_id_)::text = 'C509PARTSSUSPENDTARGET'::text))
39. 0.002 0.016 ↑ 1.0 1 1

Hash (cost=11.75..11.75 rows=1 width=196) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
40. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on product_suspend_setting pss (cost=0.00..11.75 rows=1 width=196) (actual time=0.014..0.014 rows=1 loops=1)

  • Filter: (((start_date_)::text <= '20190527'::text) AND ((end_date_)::text >= '20190527'::text) AND ((site_id_)::text = 'UA2201'::text))
  • Rows Removed by Filter: 4
Planning time : 5.714 ms
Execution time : 800.950 ms