explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ag4e : Optimization for: Optimization for: plan #beqB; plan #JRWr - add index to cleared hours

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 17,602.420 157,397.740 ↓ 0.0 0 1

Update on offer_blocks (cost=2,301.25..1,726,930.05 rows=2,341 width=234) (actual time=157,397.740..157,397.740 rows=0 loops=1)

2. 16,585.898 139,795.320 ↓ 852.3 1,995,330 1

Nested Loop Left Join (cost=2,301.25..1,726,930.05 rows=2,341 width=234) (actual time=25.558..139,795.320 rows=1,995,330 loops=1)

  • Join Filter: (ap.id = sp.asset_profile_id)
  • Rows Removed by Join Filter: 3,937,920
3. 1,203.810 56,730.652 ↓ 4,218.5 1,995,330 1

Nested Loop (cost=2,301.25..1,234,620.19 rows=473 width=755) (actual time=25.399..56,730.652 rows=1,995,330 loops=1)

  • Join Filter: (fri.shaped_coal_price_set_id = scps.id)
4. 1,631.088 53,531.512 ↓ 4,218.5 1,995,330 1

Nested Loop (cost=2,301.10..1,232,263.21 rows=473 width=757) (actual time=25.388..53,531.512 rows=1,995,330 loops=1)

5. 2,607.535 21,970.474 ↓ 4,122.6 5,985,990 1

Hash Join (cost=2,300.67..1,222,679.32 rows=1,452 width=666) (actual time=25.355..21,970.474 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. 1,998.481 19,356.958 ↓ 63.4 5,983,266 1

Hash Join (cost=1,679.47..1,220,628.30 rows=94,353 width=658) (actual time=19.154..19,356.958 rows=5,983,266 loops=1)

  • Hash Cond: (ob.cleared_hour_id = ch.id)
7. 1,821.143 17,353.145 ↓ 63.4 5,983,266 1

Hash Join (cost=828.19..1,219,529.29 rows=94,353 width=652) (actual time=13.686..17,353.145 rows=5,983,266 loops=1)

  • Hash Cond: (ap.fuel_id = f.id)
8. 2,120.612 15,531.971 ↓ 63.4 5,983,266 1

Hash Join (cost=815.04..1,219,260.97 rows=94,353 width=134) (actual time=13.625..15,531.971 rows=5,983,266 loops=1)

  • Hash Cond: (apb.asset_profile_id = ap.id)
9. 2,028.194 13,411.307 ↓ 63.4 5,983,266 1

Hash Join (cost=810.43..1,218,999.64 rows=94,353 width=103) (actual time=13.567..13,411.307 rows=5,983,266 loops=1)

  • Hash Cond: (ob.asset_profile_block_id = apb.id)
10. 2,880.122 11,382.968 ↓ 63.4 5,983,266 1

Hash Join (cost=802.32..1,218,739.22 rows=94,353 width=78) (actual time=13.413..11,382.968 rows=5,983,266 loops=1)

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

Seq Scan on offer_blocks ob (cost=0.00..1,125,073.15 rows=6,128,015 width=34) (actual time=0.030..8,489.579 rows=5,981,904 loops=1)

12. 4.210 13.267 ↑ 1.0 17,568 1

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

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

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

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

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

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

16. 0.007 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.014 0.014 ↑ 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.014 rows=2 loops=1)

18. 0.007 0.028 ↑ 65.0 2 1

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

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

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

20. 0.076 0.145 ↑ 1.0 227 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
21. 0.069 0.069 ↑ 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.069 rows=227 loops=1)

22. 0.027 0.052 ↑ 1.0 116 1

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

  • Buckets: 1,024 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.007 0.031 ↑ 20.0 7 1

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

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

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

26. 2.406 5.332 ↑ 1.0 17,568 1

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

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

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

28. 2.945 5.981 ↑ 1.0 17,568 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,354kB
29. 3.036 3.036 ↑ 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.024..3.036 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.019 0.019 ↑ 495.0 2 1

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

34.          

SubPlan (for Nested Loop Left Join)

35. 369.180 632.880 ↑ 1.0 1 52,740

Aggregate (cost=135.83..135.84 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=52,740)

36. 158.220 263.700 ↑ 3.4 13 52,740

Bitmap Heap Scan on cleared_hours ch2 (cost=4.86..135.72 rows=44 width=4) (actual time=0.004..0.005 rows=13 loops=52,740)

  • Recheck Cond: ((forecast_run_iteration_id = ob.forecast_run_iteration_id) AND (hour_beginning >= ob.hour_beginning) AND (hour_beginning <= (ob.hour_beginning + ('01:00:00'::interval hour * (sp.supply_cushion_look_ahead_hours)::double precision))))
  • Heap Blocks: exact=60,018
37. 105.480 105.480 ↑ 3.4 13 52,740

Bitmap Index Scan on cleared_hours_test_index (cost=0.00..4.84 rows=44 width=0) (actual time=0.002..0.002 rows=13 loops=52,740)

  • Index Cond: ((forecast_run_iteration_id = ob.forecast_run_iteration_id) AND (hour_beginning >= ob.hour_beginning) AND (hour_beginning <= (ob.hour_beginning + ('01:00:00'::interval hour * (sp.supply_cushion_look_ahead_hours)::double precision))))
38. 41,901.930 53,873.910 ↑ 1.0 1 1,995,330

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

39. 11,971.980 11,971.980 ↓ 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.006 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
40. 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 : 10.821 ms
Execution time : 157,398.540 ms