explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y4er : pps

Settings
# exclusive inclusive rows x rows loops node
1. 254,908.543 261,363.765 ↑ 1.3 359,425 1

Hash Left Join (cost=71,273.96..6,151,846.48 rows=473,325 width=1,149) (actual time=4,687.579..261,363.765 rows=359,425 loops=1)

  • Hash Cond: ((pt_part.pt_part_no)::text = (vw_upload_revisions.rv_part_no)::text)
2. 1,104.657 6,451.667 ↑ 1.3 359,425 1

Merge Right Join (cost=69,594.08..77,998.17 rows=473,325 width=307) (actual time=4,673.710..6,451.667 rows=359,425 loops=1)

  • Merge Cond: (((pc_price_cd.pc_description_cd)::text = (pt_part.pt_description_code)::text) AND ((pc_price_cd.pc_prc_cd)::text = (pt_part.pt_prc_cd)::text))
3. 5.730 5.730 ↑ 1.2 1,320 1

Index Scan using uq_pc_01 on pc_price_cd (cost=0.28..134.62 rows=1,529 width=34) (actual time=0.008..5.730 rows=1,320 loops=1)

4. 2,458.196 5,341.280 ↑ 1.0 359,425 1

Sort (cost=69,592.96..70,491.53 rows=359,425 width=280) (actual time=4,673.668..5,341.280 rows=359,425 loops=1)

  • Sort Key: pt_part.pt_description_code, pt_part.pt_prc_cd
  • Sort Method: quicksort Memory: 196617kB
5. 582.907 2,883.084 ↑ 1.0 359,425 1

Hash Left Join (cost=38.69..36,426.43 rows=359,425 width=280) (actual time=1.571..2,883.084 rows=359,425 loops=1)

  • Hash Cond: ((pt_part.pt_sales_cls_cd_1)::text = (sc_sales_class_cd.sc_sales_cls_cd_1)::text)
6. 592.761 2,300.144 ↑ 1.0 359,425 1

Hash Left Join (cost=37.31..31,985.79 rows=359,425 width=272) (actual time=1.515..2,300.144 rows=359,425 loops=1)

  • Hash Cond: ((pt_part.pt_description_code)::text = (de_description_cd.de_description_cd)::text)
7. 584.443 1,706.058 ↑ 1.0 359,425 1

Hash Left Join (cost=3.80..27,036.67 rows=359,425 width=247) (actual time=0.181..1,706.058 rows=359,425 loops=1)

  • Hash Cond: ((pt_part.pt_cost_cd)::text = (cc_cost_cd.cc_cost_cd)::text)
8. 764.724 1,121.566 ↑ 1.0 359,425 1

Hash Left Join (cost=2.24..22,501.75 rows=359,425 width=211) (actual time=0.119..1,121.566 rows=359,425 loops=1)

  • Hash Cond: ((pt_part.pt_disc_cd)::text = (dc_disc_cd.dc_disc_cd)::text)
9. 356.754 356.754 ↑ 1.0 359,425 1

Seq Scan on pt_part (cost=0.00..17,564.25 rows=359,425 width=201) (actual time=0.019..356.754 rows=359,425 loops=1)

10. 0.049 0.088 ↑ 1.0 55 1

Hash (cost=1.55..1.55 rows=55 width=12) (actual time=0.088..0.088 rows=55 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
11. 0.039 0.039 ↑ 1.0 55 1

Seq Scan on dc_disc_cd (cost=0.00..1.55 rows=55 width=12) (actual time=0.009..0.039 rows=55 loops=1)

12. 0.021 0.049 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=39) (actual time=0.049..0.049 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 0.028 0.028 ↑ 1.0 25 1

Seq Scan on cc_cost_cd (cost=0.00..1.25 rows=25 width=39) (actual time=0.010..0.028 rows=25 loops=1)

14. 0.673 1.325 ↑ 1.0 956 1

Hash (cost=21.56..21.56 rows=956 width=25) (actual time=1.325..1.325 rows=956 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 60kB
15. 0.652 0.652 ↑ 1.0 956 1

Seq Scan on de_description_cd (cost=0.00..21.56 rows=956 width=25) (actual time=0.009..0.652 rows=956 loops=1)

16. 0.018 0.033 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=10) (actual time=0.033..0.033 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.015 0.015 ↑ 1.0 17 1

Seq Scan on sc_sales_class_cd (cost=0.00..1.17 rows=17 width=10) (actual time=0.006..0.015 rows=17 loops=1)

18. 0.116 3.555 ↓ 5.7 149 1

Hash (cost=1,679.55..1,679.55 rows=26 width=84) (actual time=3.555..3.555 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
19. 0.146 3.439 ↓ 5.7 149 1

Subquery Scan on vw_upload_revisions (cost=1,679.03..1,679.55 rows=26 width=84) (actual time=3.198..3.439 rows=149 loops=1)

20. 0.366 3.293 ↓ 5.7 149 1

HashAggregate (cost=1,679.03..1,679.29 rows=26 width=228) (actual time=3.196..3.293 rows=149 loops=1)

  • Group Key: a.sv_part_no, a.sv_eff_dt, a.sv_retail, a.sv_dealernet, a.sv_trade, a.sv_core_value, a.sv_upload_dt, a.sv_price_tracking_date, a.sv_price_category, a.sv_part_status, a.sv_platform, a.sv_disc_cd, a.sv_prc_c
21. 0.158 2.927 ↓ 5.7 149 1

Append (cost=12.81..1,678.12 rows=26 width=228) (actual time=1.325..2.927 rows=149 loops=1)

22. 0.013 0.997 ↓ 0.0 0 1

Hash Join (cost=12.81..15.73 rows=1 width=228) (actual time=0.997..0.997 rows=0 loops=1)

  • Hash Cond: (d.fm_eff_dt = a.sv_eff_dt)
23. 0.977 0.977 ↑ 34.0 1 1

Seq Scan on fm_fiscal_mth d (cost=0.00..2.79 rows=34 width=8) (actual time=0.977..0.977 rows=1 loops=1)

  • Filter: (fm_cutoff_dt <= trunc(sysdate, 'DD'::character varying))
24. 0.001 0.007 ↓ 0.0 0 1

Hash (cost=12.80..12.80 rows=1 width=228) (actual time=0.007..0.007 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
25. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on sv_sim_revision a (cost=0.00..12.80 rows=1 width=228) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: (sv_upload_dt IS NULL)
26. 0.225 1.772 ↓ 6.0 149 1

Hash Join (cost=3.21..1,662.13 rows=25 width=162) (actual time=0.325..1.772 rows=149 loops=1)

  • Hash Cond: (b.rv_eff_dt = e.fm_eff_dt)
27. 0.395 1.289 ↓ 2.0 149 1

Seq Scan on rv_revision b (cost=0.00..1,658.39 rows=74 width=162) (actual time=0.056..1.289 rows=149 loops=1)

  • Filter: ((rv_upload_dt IS NULL) AND (NOT (SubPlan 1)))
28.          

SubPlan (forSeq Scan)

29. 0.436 0.894 ↓ 0.0 0 149

Hash Join (cost=8.18..11.10 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=149)

  • Hash Cond: (f.fm_eff_dt = c.sv_eff_dt)
30. 0.011 0.011 ↑ 34.0 1 1

Seq Scan on fm_fiscal_mth f (cost=0.00..2.79 rows=34 width=8) (actual time=0.011..0.011 rows=1 loops=1)

  • Filter: (fm_cutoff_dt <= trunc(sysdate, 'DD'::character varying))
31. 0.298 0.447 ↓ 0.0 0 149

Hash (cost=8.17..8.17 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=149)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
32. 0.149 0.149 ↓ 0.0 0 149

Index Scan using pk_sv on sv_sim_revision c (cost=0.15..8.17 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=149)

  • Index Cond: ((sv_part_no)::text = (b.rv_part_no)::text)
  • Filter: (sv_upload_dt IS NULL)
33. 0.074 0.258 ↓ 3.0 102 1

Hash (cost=2.79..2.79 rows=34 width=8) (actual time=0.258..0.258 rows=102 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
34. 0.184 0.184 ↓ 3.0 102 1

Seq Scan on fm_fiscal_mth e (cost=0.00..2.79 rows=34 width=8) (actual time=0.009..0.184 rows=102 loops=1)

  • Filter: (fm_cutoff_dt <= trunc(sysdate, 'DD'::character varying))