explain.depesz.com

PostgreSQL's explain analyze made readable

Result: beqB

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 88,461.998 377,752.915 ↓ 0.0 0 1

Update on offer_blocks (cost=2,301.25..3,552,044.40 rows=2,391 width=233) (actual time=377,752.915..377,752.915 rows=0 loops=1)

2. 18,329.864 289,290.917 ↓ 834.5 1,995,330 1

Nested Loop Left Join (cost=2,301.25..3,552,044.40 rows=2,391 width=233) (actual time=30.203..289,290.917 rows=1,995,330 loops=1)

  • Join Filter: (ap.id = sp.asset_profile_id)
  • Rows Removed by Join Filter: 3,937,920
3. 1,602.638 98,114.493 ↓ 4,131.1 1,995,330 1

Nested Loop (cost=2,301.25..1,233,296.43 rows=483 width=754) (actual time=27.932..98,114.493 rows=1,995,330 loops=1)

  • Join Filter: (fri.shaped_coal_price_set_id = scps.id)
4. 3,027.056 94,516.525 ↓ 4,131.1 1,995,330 1

Nested Loop (cost=2,301.10..1,230,889.62 rows=483 width=756) (actual time=27.921..94,516.525 rows=1,995,330 loops=1)

5. 2,857.373 25,643.579 ↓ 4,159.8 5,985,990 1

Hash Join (cost=2,300.67..1,221,391.25 rows=1,439 width=666) (actual time=27.887..25,643.579 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,188.620 22,779.660 ↓ 64.0 5,983,266 1

Hash Join (cost=1,679.47..1,219,352.97 rows=93,513 width=658) (actual time=21.216..22,779.660 rows=5,983,266 loops=1)

  • Hash Cond: (ob.cleared_hour_id = ch.id)
7. 1,932.763 20,585.043 ↓ 64.0 5,983,266 1

Hash Join (cost=828.19..1,218,256.16 rows=93,513 width=652) (actual time=15.097..20,585.043 rows=5,983,266 loops=1)

  • Hash Cond: (ap.fuel_id = f.id)
8. 2,227.490 18,652.242 ↓ 64.0 5,983,266 1

Hash Join (cost=815.04..1,217,990.11 rows=93,513 width=134) (actual time=15.033..18,652.242 rows=5,983,266 loops=1)

  • Hash Cond: (apb.asset_profile_id = ap.id)
9. 2,274.098 16,424.699 ↓ 64.0 5,983,266 1

Hash Join (cost=810.43..1,217,731.06 rows=93,513 width=103) (actual time=14.959..16,424.699 rows=5,983,266 loops=1)

  • Hash Cond: (ob.asset_profile_block_id = apb.id)
10. 3,219.427 14,150.495 ↓ 64.0 5,983,266 1

Hash Join (cost=802.32..1,217,472.90 rows=93,513 width=78) (actual time=14.836..14,150.495 rows=5,983,266 loops=1)

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

Seq Scan on offer_blocks ob (cost=0.00..1,124,569.98 rows=6,077,698 width=34) (actual time=0.034..10,916.385 rows=5,981,904 loops=1)

12. 4.939 14.683 ↑ 1.0 17,568 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,766kB
13. 3.470 9.744 ↑ 1.0 17,568 1

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

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

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

  • Hash Cond: (sgp.shaped_gas_price_set_id = sgps.id)
15. 3.423 3.423 ↑ 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.017..3.423 rows=17,568 loops=1)

16. 0.006 0.021 ↑ 600.0 2 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 17kB
17. 0.015 0.015 ↑ 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.015..0.015 rows=2 loops=1)

18. 0.007 0.027 ↑ 65.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.020 0.020 ↑ 6.5 20 1

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

20. 0.053 0.106 ↑ 1.0 227 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
21. 0.053 0.053 ↑ 1.0 227 1

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

22. 0.027 0.053 ↑ 1.0 116 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
23. 0.026 0.026 ↑ 1.0 116 1

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

24. 0.002 0.038 ↑ 20.0 7 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
25. 0.036 0.036 ↑ 20.0 7 1

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

26. 2.845 5.997 ↑ 1.0 17,568 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,217kB
27. 3.152 3.152 ↑ 1.0 17,568 1

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

28. 3.346 6.546 ↑ 1.0 17,568 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,354kB
29. 3.200 3.200 ↑ 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.036..3.200 rows=17,568 loops=1)

30. 65,845.890 65,845.890 ↓ 0.0 0 5,985,990

Index Scan using offer_blocks_pkey on offer_blocks (cost=0.43..6.60 rows=1 width=98) (actual time=0.011..0.011 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.048 0.048 ↑ 495.0 2 1

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

34.          

SubPlan (for Nested Loop Left Join)

35. 421.920 105,005.340 ↑ 1.0 1 52,740

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

36. 104,583.420 104,583.420 ↑ 3.4 13 52,740

Seq Scan on cleared_hours ch2 (cost=0.00..895.20 rows=44 width=4) (actual time=1.137..1.983 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: 17,555
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. 11,971.980 11,971.980 ↑ 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.006 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 : 14.898 ms
Execution time : 377,754.049 ms