explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cTj6

Settings
# exclusive inclusive rows x rows loops node
1. 36.543 933.903 ↓ 1.0 61,568 1

Unique (cost=75,991.33..77,940.55 rows=59,976 width=201) (actual time=882.014..933.903 rows=61,568 loops=1)

2. 145.020 897.360 ↓ 1.0 61,569 1

Sort (cost=75,991.33..76,141.27 rows=59,976 width=201) (actual time=882.013..897.360 rows=61,569 loops=1)

  • Sort Key: pr.product_code_, pr.local_description_, pr.english_description_, prsp.product_code_, pcsmiddle.category_code_, pclast.cost_, pcavg.cost_, ypr.standard_retail_price_, abc.abc_type_, pr.sales_lot_quantity_, ypr.md_purchase_lot_, (CASE WHEN (((ppi.string_value_)::text = '1'::text) OR (pss.product_suspend_id_ IS NOT NULL)) THEN 'Y'::text ELSE 'N'::text END)
  • Sort Method: external merge Disk: 6528kB
3. 19.321 752.340 ↓ 1.0 61,569 1

Merge Left Join (cost=48,696.33..66,984.10 rows=59,976 width=201) (actual time=403.883..752.340 rows=61,569 loops=1)

  • Merge Cond: ((pr.product_id_)::text = (pss.product_id_)::text)
4. 11.982 732.987 ↓ 1.0 61,569 1

Merge Left Join (cost=48,684.57..66,672.44 rows=59,976 width=140) (actual time=403.846..732.987 rows=61,569 loops=1)

  • Merge Cond: ((pr.product_id_)::text = (ppi.product_id_)::text)
5. 31.279 720.801 ↓ 1.0 61,569 1

Merge Left Join (cost=48,531.25..66,367.82 rows=59,976 width=138) (actual time=403.639..720.801 rows=61,569 loops=1)

  • Merge Cond: ((pr.product_id_)::text = (prabc.product_id_)::text)
6. 17.919 557.968 ↓ 1.0 61,569 1

Merge Left Join (cost=39,455.08..56,365.13 rows=59,976 width=136) (actual time=282.443..557.968 rows=61,569 loops=1)

  • Merge Cond: ((pr.product_id_)::text = (pcavg.product_id_)::text)
7. 25.098 527.848 ↓ 1.0 61,569 1

Merge Left Join (cost=29,117.13..45,617.32 rows=59,976 width=130) (actual time=272.094..527.848 rows=61,569 loops=1)

  • Merge Cond: ((pr.product_id_)::text = (pclast.product_id_)::text)
8. 16.039 456.212 ↓ 1.0 61,568 1

Merge Left Join (cost=17,903.30..33,913.23 rows=59,976 width=124) (actual time=231.936..456.212 rows=61,568 loops=1)

  • Merge Cond: ((pr.product_id_)::text = (prl.to_product_id_)::text)
9. 44.316 377.544 ↓ 1.0 61,568 1

Merge Join (cost=11,557.83..27,359.77 rows=59,976 width=112) (actual time=170.608..377.544 rows=61,568 loops=1)

  • Merge Cond: ((pr.product_id_)::text = (ypr.product_id_)::text)
10. 47.870 281.189 ↓ 1.0 61,568 1

Merge Join (cost=11,557.42..20,707.01 rows=61,368 width=139) (actual time=170.582..281.189 rows=61,568 loops=1)

  • Merge Cond: ((pr.product_id_)::text = (pcimiddle.product_id_)::text)
11. 52.000 52.000 ↑ 1.0 65,108 1

Index Scan using pk_product on product pr (cost=0.41..8,066.97 rows=65,108 width=99) (actual time=0.006..52.000 rows=65,108 loops=1)

12. 117.138 181.319 ↓ 1.0 61,568 1

Sort (cost=11,557.00..11,710.42 rows=61,368 width=40) (actual time=170.560..181.319 rows=61,568 loops=1)

  • Sort Key: pcimiddle.product_id_
  • Sort Method: external sort Disk: 3248kB
13. 16.627 64.181 ↓ 1.0 61,568 1

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

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

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

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

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

17. 52.039 52.039 ↓ 1.0 63,674 1

Index Scan using index_yimm_product_info_01 on yimm_product_info ypr (cost=0.41..5,740.81 rows=63,631 width=47) (actual time=0.021..52.039 rows=63,674 loops=1)

18. 5.866 62.629 ↓ 1.7 6,879 1

Sort (cost=6,345.47..6,355.68 rows=4,085 width=49) (actual time=61.323..62.629 rows=6,879 loops=1)

  • Sort Key: prl.to_product_id_
  • Sort Method: quicksort Memory: 1160kB
19. 8.498 56.763 ↓ 1.7 6,879 1

Hash Left Join (cost=4,843.93..6,100.44 rows=4,085 width=49) (actual time=45.046..56.763 rows=6,879 loops=1)

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

Seq Scan on product_relation prl (cost=0.00..445.03 rows=4,085 width=74) (actual time=0.224..3.496 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
21. 17.831 44.769 ↑ 1.0 65,108 1

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

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

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

23. 30.944 46.538 ↓ 1.4 32,474 1

Sort (cost=11,213.83..11,273.69 rows=23,946 width=43) (actual time=40.153..46.538 rows=32,474 loops=1)

  • Sort Key: pclast.product_id_
  • Sort Method: quicksort Memory: 3306kB
24. 8.556 15.594 ↓ 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=7.150..15.594 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
25. 7.038 7.038 ↓ 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=7.038..7.038 rows=32,474 loops=1)

  • Index Cond: (((site_id_)::text = 'UA2201'::text) AND ((product_cost_component_category_id_)::text = 'C034RECEIVECOST'::text))
26. 8.007 12.201 ↑ 1.9 9,487 1

Sort (cost=10,337.96..10,383.68 rows=18,289 width=43) (actual time=10.347..12.201 rows=9,487 loops=1)

  • Sort Key: pcavg.product_id_
  • Sort Method: quicksort Memory: 1126kB
27. 2.460 4.194 ↑ 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.777..4.194 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
28. 1.734 1.734 ↑ 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.734..1.734 rows=9,486 loops=1)

  • Index Cond: (((site_id_)::text = 'UA2201'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
29. 88.316 131.554 ↓ 1.1 61,569 1

Sort (cost=9,076.14..9,212.76 rows=54,647 width=39) (actual time=121.190..131.554 rows=61,569 loops=1)

  • Sort Key: prabc.product_id_
  • Sort Method: external sort Disk: 3192kB
30. 22.272 43.238 ↓ 1.1 61,568 1

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

  • Hash Cond: (((prabc.site_id_)::text = (abc.site_id_)::text) AND ((prabc.abc_id_)::text = (abc.abc_id_)::text))
31. 20.810 20.810 ↓ 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.019..20.810 rows=61,568 loops=1)

  • Index Cond: (site_id_ = 'UA2201'::text)
  • Heap Fetches: 0
32. 0.047 0.156 ↑ 1.0 192 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
33. 0.083 0.109 ↑ 1.0 192 1

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

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

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

  • Index Cond: ((site_id_)::text = 'UA2201'::text)
35. 0.011 0.204 ↓ 0.0 0 1

Sort (cost=153.32..153.56 rows=96 width=38) (actual time=0.204..0.204 rows=0 loops=1)

  • Sort Key: ppi.product_id_
  • Sort Method: quicksort Memory: 25kB
36. 0.005 0.193 ↓ 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.193..0.193 rows=0 loops=1)

  • Recheck Cond: (((site_id_)::text = 'UA2201'::text) AND ((product_control_type_id_)::text = 'C509PARTSSUSPENDTARGET'::text))
37. 0.188 0.188 ↓ 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.188..0.188 rows=0 loops=1)

  • Index Cond: (((site_id_)::text = 'UA2201'::text) AND ((product_control_type_id_)::text = 'C509PARTSSUSPENDTARGET'::text))
38. 0.005 0.032 ↑ 1.0 1 1

Sort (cost=11.76..11.77 rows=1 width=196) (actual time=0.032..0.032 rows=1 loops=1)

  • Sort Key: pss.product_id_
  • Sort Method: quicksort Memory: 25kB
39. 0.027 0.027 ↑ 1.0 1 1

Seq Scan on product_suspend_setting pss (cost=0.00..11.75 rows=1 width=196) (actual time=0.027..0.027 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 : 4.514 ms
Execution time : 939.696 ms