explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JRWr : Optimization for: plan #beqB

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 18,674.114 282,026.736 ↓ 0.0 0 1

Update on offer_blocks (cost=2,301.25..3,459,957.56 rows=2,297 width=234) (actual time=282,026.736..282,026.736 rows=0 loops=1)

2. 17,375.607 263,352.622 ↓ 868.7 1,995,330 1

Nested Loop Left Join (cost=2,301.25..3,459,957.56 rows=2,297 width=234) (actual time=29.262..263,352.622 rows=1,995,330 loops=1)

  • Join Filter: (ap.id = sp.asset_profile_id)
  • Rows Removed by Join Filter: 3937920
3. 1,542.330 60,745.345 ↓ 4,300.3 1,995,330 1

Nested Loop (cost=2,301.25..1,232,368.35 rows=464 width=755) (actual time=26.928..60,745.345 rows=1,995,330 loops=1)

  • Join Filter: (fri.shaped_coal_price_set_id = scps.id)
4. 3,282.607 57,207.685 ↓ 4,300.3 1,995,330 1

Nested Loop (cost=2,301.10..1,230,056.22 rows=464 width=757) (actual time=26.920..57,207.685 rows=1,995,330 loops=1)

5. 2,815.834 23,995.128 ↓ 4,180.2 5,985,990 1

Hash Join (cost=2,300.67..1,220,603.88 rows=1,432 width=666) (actual time=26.887..23,995.128 rows=5,985,990 loops=1)

  • Hash Cond: ((sgp.hour_beginning = scp.hour_beginning) AND (fri.shaped_coal_price_set_id = scp.shaped_coal_price_set_id))
6. 2,114.337 21,172.883 ↓ 64.3 5,983,266 1

Hash Join (cost=1,679.47..1,218,572.78 rows=93,039 width=658) (actual time=20.352..21,172.883 rows=5,983,266 loops=1)

  • Hash Cond: (ob.cleared_hour_id = ch.id)
7. 1,952.732 19,052.532 ↓ 64.3 5,983,266 1

Hash Join (cost=828.19..1,217,477.21 rows=93,039 width=652) (actual time=14.202..19,052.532 rows=5,983,266 loops=1)

  • Hash Cond: (ap.fuel_id = f.id)
8. 2,244.691 17,099.757 ↓ 64.3 5,983,266 1

Hash Join (cost=815.04..1,217,212.44 rows=93,039 width=134) (actual time=14.128..17,099.757 rows=5,983,266 loops=1)

  • Hash Cond: (apb.asset_profile_id = ap.id)
9. 2,160.249 14,855.015 ↓ 64.3 5,983,266 1

Hash Join (cost=810.43..1,216,954.68 rows=93,039 width=103) (actual time=14.065..14,855.015 rows=5,983,266 loops=1)

  • Hash Cond: (ob.asset_profile_block_id = apb.id)
10. 3,015.468 12,694.664 ↓ 64.3 5,983,266 1

Hash Join (cost=802.32..1,216,697.78 rows=93,039 width=78) (actual time=13.939..12,694.664 rows=5,983,266 loops=1)

  • Hash Cond: ((ob.forecast_run_iteration_id = fri.id) AND (ob.hour_beginning = sgp.hour_beginning))
11. 9,665.427 9,665.427 ↑ 1.0 5,981,904 1

Seq Scan on offer_blocks ob (cost=0.00..1,124,261.83 rows=6,046,883 width=34) (actual time=0.031..9,665.427 rows=5,981,904 loops=1)

12. 4.606 13.769 ↑ 1.0 17,568 1

Hash (cost=538.80..538.80 rows=17,568 width=52) (actual time=13.769..13.769 rows=17,568 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1766kB
13. 3.320 9.163 ↑ 1.0 17,568 1

Hash Join (cost=49.92..538.80 rows=17,568 width=52) (actual time=0.083..9.163 rows=17,568 loops=1)

  • Hash Cond: (sgps.id = fri.shaped_gas_price_set_id)
14. 2.669 5.817 ↑ 1.0 17,568 1

Hash Join (cost=37.00..440.96 rows=17,568 width=38) (actual time=0.046..5.817 rows=17,568 loops=1)

  • Hash Cond: (sgp.shaped_gas_price_set_id = sgps.id)
15. 3.128 3.128 ↑ 1.0 17,568 1

Seq Scan on shaped_gas_prices sgp (cost=0.00..357.68 rows=17,568 width=24) (actual time=0.015..3.128 rows=17,568 loops=1)

16. 0.007 0.020 ↑ 600.0 2 1

Hash (cost=22.00..22.00 rows=1,200 width=14) (actual time=0.020..0.020 rows=2 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
17. 0.013 0.013 ↑ 600.0 2 1

Seq Scan on shaped_gas_price_sets sgps (cost=0.00..22.00 rows=1,200 width=14) (actual time=0.013..0.013 rows=2 loops=1)

18. 0.008 0.026 ↑ 65.0 2 1

Hash (cost=11.30..11.30 rows=130 width=30) (actual time=0.026..0.026 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.018 0.018 ↑ 6.5 20 1

Seq Scan on forecast_run_iterations fri (cost=0.00..11.30 rows=130 width=30) (actual time=0.016..0.018 rows=20 loops=1)

20. 0.051 0.102 ↑ 1.0 227 1

Hash (cost=5.27..5.27 rows=227 width=37) (actual time=0.102..0.102 rows=227 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
21. 0.051 0.051 ↑ 1.0 227 1

Seq Scan on asset_profile_blocks apb (cost=0.00..5.27 rows=227 width=37) (actual time=0.011..0.051 rows=227 loops=1)

22. 0.026 0.051 ↑ 1.0 116 1

Hash (cost=3.16..3.16 rows=116 width=35) (actual time=0.051..0.051 rows=116 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
23. 0.025 0.025 ↑ 1.0 116 1

Seq Scan on asset_profiles ap (cost=0.00..3.16 rows=116 width=35) (actual time=0.009..0.025 rows=116 loops=1)

24. 0.008 0.043 ↑ 20.0 7 1

Hash (cost=11.40..11.40 rows=140 width=530) (actual time=0.043..0.043 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.035 0.035 ↑ 20.0 7 1

Seq Scan on fuels f (cost=0.00..11.40 rows=140 width=530) (actual time=0.035..0.035 rows=7 loops=1)

26. 2.790 6.014 ↑ 1.0 17,568 1

Hash (cost=631.68..631.68 rows=17,568 width=18) (actual time=6.014..6.014 rows=17,568 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1217kB
27. 3.224 3.224 ↑ 1.0 17,568 1

Seq Scan on cleared_hours ch (cost=0.00..631.68 rows=17,568 width=18) (actual time=0.022..3.224 rows=17,568 loops=1)

28. 2.930 6.411 ↑ 1.0 17,568 1

Hash (cost=357.68..357.68 rows=17,568 width=24) (actual time=6.411..6.411 rows=17,568 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1354kB
29. 3.481 3.481 ↑ 1.0 17,568 1

Seq Scan on shaped_coal_prices scp (cost=0.00..357.68 rows=17,568 width=24) (actual time=0.028..3.481 rows=17,568 loops=1)

30. 29,929.950 29,929.950 ↓ 0.0 0 5,985,990

Index Scan using offer_blocks_pkey on offer_blocks (cost=0.43..6.60 rows=1 width=99) (actual time=0.005..0.005 rows=0 loops=5,985,990)

  • Index Cond: (id = ob.id)
  • Filter: (forecast_run_iteration_id = 1)
  • Rows Removed by Filter: 1
31. 1,995.330 1,995.330 ↑ 1.0 1 1,995,330

Index Scan using shaped_coal_price_sets_pkey on shaped_coal_price_sets scps (cost=0.15..4.97 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=1,995,330)

  • Index Cond: (id = scp.shaped_coal_price_set_id)
32. 0.000 0.000 ↑ 495.0 2 1,995,330

Materialize (cost=0.00..24.85 rows=990 width=22) (actual time=0.000..0.000 rows=2 loops=1,995,330)

33. 0.021 0.021 ↑ 495.0 2 1

Seq Scan on storage_profiles sp (cost=0.00..19.90 rows=990 width=22) (actual time=0.020..0.021 rows=2 loops=1)

34.          

SubPlan (for Nested Loop Left Join)

35. 474.660 115,395.120 ↑ 1.0 1 52,740

Aggregate (cost=895.31..895.32 rows=1 width=4) (actual time=2.188..2.188 rows=1 loops=52,740)

36. 114,920.460 114,920.460 ↑ 3.4 13 52,740

Seq Scan on cleared_hours ch2 (cost=0.00..895.20 rows=44 width=4) (actual time=1.263..2.179 rows=13 loops=52,740)

  • Filter: ((hour_beginning >= ob.hour_beginning) AND (forecast_run_iteration_id = ob.forecast_run_iteration_id) AND (hour_beginning <= (ob.hour_beginning + ('01:00:00'::interval hour * (sp.supply_cushion_look_ahead_hours)::double precision))))
  • Rows Removed by Filter: 17555
37. 41,901.930 55,869.240 ↑ 1.0 1 1,995,330

Aggregate (cost=43.01..43.02 rows=1 width=32) (actual time=0.028..0.028 rows=1 loops=1,995,330)

38. 13,967.310 13,967.310 ↓ 3.0 3 1,995,330

Seq Scan on cost_escalator_forecast_values cefv (cost=0.00..43.00 rows=1 width=14) (actual time=0.001..0.007 rows=3 loops=1,995,330)

  • Filter: ((cost_escalator_forecast_id = fri.cost_escalator_forecast_id) AND ((year)::double precision <= date_part('year'::text, ob.hour_beginning)) AND ((year)::double precision > date_part('year'::text, (ap.effective_date)::timestamp without time zone)))
  • Rows Removed by Filter: 19
39. 13,967.310 13,967.310 ↑ 1.0 1 1,995,330

Seq Scan on carbon_price_forecast_values cpfv (cost=0.00..28.41 rows=1 width=32) (actual time=0.002..0.007 rows=1 loops=1,995,330)

  • Filter: ((carbon_price_forecast_id = fri.carbon_price_forecast_id) AND ((year)::double precision = date_part('year'::text, ob.hour_beginning)))
  • Rows Removed by Filter: 21
Planning time : 9.725 ms
Execution time : 282,027.584 ms