explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MlQp

Settings
# exclusive inclusive rows x rows loops node
1. 0.174 25,892.332 ↑ 31.6 180 1

HashAggregate (cost=4,121,998.57..4,122,055.37 rows=5,680 width=17) (actual time=25,892.273..25,892.332 rows=180 loops=1)

  • Group Key: fr.ancestor_id, pv.date, (sum(pv.value)), (sum((pv.value * COALESCE(rcv.cost, '0'::numeric))))
2. 0.077 25,892.158 ↑ 31.6 180 1

Append (cost=2,028,613.56..4,121,941.77 rows=5,680 width=17) (actual time=12,742.120..25,892.158 rows=180 loops=1)

3. 34.407 12,742.142 ↑ 36.1 36 1

HashAggregate (cost=2,028,613.56..2,028,633.06 rows=1,300 width=17) (actual time=12,742.119..12,742.142 rows=36 loops=1)

  • Group Key: fr.ancestor_id, pv.date
4. 62.869 12,707.735 ↑ 3.0 22,140 1

Nested Loop Left Join (cost=353,333.22..2,027,790.42 rows=65,851 width=17) (actual time=2,631.275..12,707.735 rows=22,140 loops=1)

  • Join Filter: (rc.id = rcv.rate_card_id)
  • Rows Removed by Join Filter: 122152
5. 16.680 12,401.326 ↑ 1.3 22,140 1

Nested Loop (cost=353,332.79..2,006,480.28 rows=29,218 width=35) (actual time=2,631.247..12,401.326 rows=22,140 loops=1)

6. 9.958 12,296.086 ↑ 1.3 22,140 1

Nested Loop (cost=353,332.37..1,986,926.81 rows=29,218 width=43) (actual time=2,631.233..12,296.086 rows=22,140 loops=1)

7. 0.030 0.030 ↑ 1.0 1 1

Index Scan using rate_cards_pkey on rate_cards rc (cost=0.13..12.27 rows=1 width=4) (actual time=0.020..0.030 rows=1 loops=1)

  • Filter: ((version)::text = '2019'::text)
  • Rows Removed by Filter: 7
8. 115.893 12,286.098 ↑ 1.3 22,140 1

Nested Loop (cost=353,332.23..1,986,622.35 rows=29,218 width=39) (actual time=2,631.208..12,286.098 rows=22,140 loops=1)

9. 4,539.483 10,509.345 ↓ 1.2 332,172 1

Hash Join (cost=353,331.54..648,694.89 rows=272,684 width=16) (actual time=2,609.432..10,509.345 rows=332,172 loops=1)

  • Hash Cond: (pm.plan_id = p.id)
10. 3,361.717 3,361.717 ↑ 1.0 10,512,117 1

Seq Scan on plan_metadata pm (cost=0.00..169,100.28 rows=10,568,328 width=8) (actual time=0.008..3,361.717 rows=10,512,117 loops=1)

11. 127.594 2,608.145 ↓ 1.2 332,172 1

Hash (cost=348,565.01..348,565.01 rows=274,202 width=16) (actual time=2,608.145..2,608.145 rows=332,172 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 2988kB
12. 396.846 2,480.551 ↓ 1.2 332,172 1

Hash Join (cost=154,685.39..348,565.01 rows=274,202 width=16) (actual time=1,847.461..2,480.551 rows=332,172 loops=1)

  • Hash Cond: (p.fleet_id = fr.descendant_id)
13. 243.546 294.011 ↑ 1.1 594,563 1

Bitmap Heap Scan on plans p (cost=15,158.47..156,856.32 rows=645,292 width=12) (actual time=57.141..294.011 rows=594,563 loops=1)

  • Recheck Cond: ((scenario_id = 10) AND (deleted_at IS NULL))
  • Filter: (project_id IS NULL)
  • Rows Removed by Filter: 15268
  • Heap Blocks: exact=36870
14. 50.465 50.465 ↑ 1.1 626,585 1

Bitmap Index Scan on idx_active_plans_on_scenario_and_fleet (cost=0.00..14,997.14 rows=661,428 width=0) (actual time=50.465..50.465 rows=626,585 loops=1)

  • Index Cond: (scenario_id = 10)
15. 259.212 1,789.694 ↓ 1.0 724,116 1

Hash (cost=127,740.20..127,740.20 rows=718,378 width=8) (actual time=1,789.694..1,789.694 rows=724,116 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 2852kB
16. 1,435.652 1,530.482 ↓ 1.0 724,116 1

Bitmap Heap Scan on fleet_relationships fr (cost=20,574.88..127,740.20 rows=718,378 width=8) (actual time=105.102..1,530.482 rows=724,116 loops=1)

  • Recheck Cond: (ancestor_id = ANY ('{2127,13186,8565,822,8154,1141687,1353173,1332782,1234617,13129,5815,509,473,372,352,351,120}'::integer[]))
  • Rows Removed by Index Recheck: 4417850
  • Heap Blocks: exact=50873 lossy=26649
17. 94.830 94.830 ↓ 1.1 763,284 1

Bitmap Index Scan on fr_ancestor_idx (cost=0.00..20,395.29 rows=718,378 width=0) (actual time=94.830..94.830 rows=763,284 loops=1)

  • Index Cond: (ancestor_id = ANY ('{2127,13186,8565,822,8154,1141687,1353173,1332782,1234617,13129,5815,509,473,372,352,351,120}'::integer[]))
18. 1,660.860 1,660.860 ↓ 0.0 0 332,172

Index Scan using idx_non_zero_pv_on_time_series_id_offering_date on plan_values pv (cost=0.70..4.90 rows=1 width=31) (actual time=0.005..0.005 rows=0 loops=332,172)

  • Index Cond: ((time_series_id = pm.active_time_series_id) AND ((offering_type)::text = 'PublicEc2Offering'::text) AND (date >= '2019-10-01'::date) AND (date <= '2019-12-01'::date))
19. 88.560 88.560 ↑ 1.0 1 22,140

Index Only Scan using index_full_fleets_on_id on fleets f (cost=0.43..0.66 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=22,140)

  • Index Cond: (id = fr.descendant_id)
  • Heap Fetches: 25014
20. 243.540 243.540 ↑ 1.1 7 22,140

Index Scan using index_rate_card_values_on_offering_type_and_offering_id on rate_card_values rcv (cost=0.42..0.63 rows=8 width=32) (actual time=0.005..0.011 rows=7 loops=22,140)

  • Index Cond: (((offering_type)::text = (pv.offering_type)::text) AND ((offering_type)::text = 'PublicEc2Offering'::text) AND (offering_id = pv.offering_id))
21. 69.149 13,149.939 ↑ 30.4 144 1

HashAggregate (cost=2,093,186.21..2,093,251.91 rows=4,380 width=17) (actual time=13,149.862..13,149.939 rows=144 loops=1)

  • Group Key: fr_1.ancestor_id, pv_1.date
22. 84.030 13,080.790 ↑ 2.7 82,136 1

Merge Left Join (cost=2,085,054.90..2,090,412.96 rows=221,860 width=17) (actual time=12,914.045..13,080.790 rows=82,136 loops=1)

  • Merge Cond: ((pv_1.offering_id = rcv_1.offering_id) AND (rc_1.id = rcv_1.rate_card_id))
  • Join Filter: ((rcv_1.offering_type)::text = (pv_1.offering_type)::text)
23. 163.441 12,848.950 ↑ 1.2 82,136 1

Sort (cost=2,064,355.30..2,064,601.39 rows=98,438 width=35) (actual time=12,816.818..12,848.950 rows=82,136 loops=1)

  • Sort Key: pv_1.offering_id, rc_1.id
  • Sort Method: external merge Disk: 4112kB
24. 111.825 12,685.509 ↑ 1.2 82,136 1

Nested Loop (cost=353,332.79..2,053,496.38 rows=98,438 width=35) (actual time=2,725.822..12,685.509 rows=82,136 loops=1)

25. 33.626 12,409.412 ↑ 1.2 82,136 1

Nested Loop (cost=353,332.37..1,987,619.01 rows=98,438 width=43) (actual time=2,725.807..12,409.412 rows=82,136 loops=1)

26. 0.023 0.023 ↑ 1.0 1 1

Index Scan using rate_cards_pkey on rate_cards rc_1 (cost=0.13..12.27 rows=1 width=4) (actual time=0.018..0.023 rows=1 loops=1)

  • Filter: ((version)::text = '2020'::text)
  • Rows Removed by Filter: 7
27. 406.859 12,375.763 ↑ 1.2 82,136 1

Nested Loop (cost=353,332.23..1,986,622.35 rows=98,438 width=39) (actual time=2,725.783..12,375.763 rows=82,136 loops=1)

28. 4,569.573 10,640.216 ↓ 1.2 332,172 1

Hash Join (cost=353,331.54..648,694.89 rows=272,684 width=16) (actual time=2,703.863..10,640.216 rows=332,172 loops=1)

  • Hash Cond: (pm_1.plan_id = p_1.id)
29. 3,367.996 3,367.996 ↑ 1.0 10,512,117 1

Seq Scan on plan_metadata pm_1 (cost=0.00..169,100.28 rows=10,568,328 width=8) (actual time=0.009..3,367.996 rows=10,512,117 loops=1)

30. 124.526 2,702.647 ↓ 1.2 332,172 1

Hash (cost=348,565.01..348,565.01 rows=274,202 width=16) (actual time=2,702.647..2,702.647 rows=332,172 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 2988kB
31. 463.359 2,578.121 ↓ 1.2 332,172 1

Hash Join (cost=154,685.39..348,565.01 rows=274,202 width=16) (actual time=1,878.567..2,578.121 rows=332,172 loops=1)

  • Hash Cond: (p_1.fleet_id = fr_1.descendant_id)
32. 243.595 294.679 ↑ 1.1 594,563 1

Bitmap Heap Scan on plans p_1 (cost=15,158.47..156,856.32 rows=645,292 width=12) (actual time=57.812..294.679 rows=594,563 loops=1)

  • Recheck Cond: ((scenario_id = 10) AND (deleted_at IS NULL))
  • Filter: (project_id IS NULL)
  • Rows Removed by Filter: 15268
  • Heap Blocks: exact=36870
33. 51.084 51.084 ↑ 1.1 626,585 1

Bitmap Index Scan on idx_active_plans_on_scenario_and_fleet (cost=0.00..14,997.14 rows=661,428 width=0) (actual time=51.084..51.084 rows=626,585 loops=1)

  • Index Cond: (scenario_id = 10)
34. 261.296 1,820.083 ↓ 1.0 724,116 1

Hash (cost=127,740.20..127,740.20 rows=718,378 width=8) (actual time=1,820.083..1,820.083 rows=724,116 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 2852kB
35. 1,459.332 1,558.787 ↓ 1.0 724,116 1

Bitmap Heap Scan on fleet_relationships fr_1 (cost=20,574.88..127,740.20 rows=718,378 width=8) (actual time=109.759..1,558.787 rows=724,116 loops=1)

  • Recheck Cond: (ancestor_id = ANY ('{2127,13186,8565,822,8154,1141687,1353173,1332782,1234617,13129,5815,509,473,372,352,351,120}'::integer[]))
  • Rows Removed by Index Recheck: 4417850
  • Heap Blocks: exact=50873 lossy=26649
36. 99.455 99.455 ↓ 1.1 763,284 1

Bitmap Index Scan on fr_ancestor_idx (cost=0.00..20,395.29 rows=718,378 width=0) (actual time=99.455..99.455 rows=763,284 loops=1)

  • Index Cond: (ancestor_id = ANY ('{2127,13186,8565,822,8154,1141687,1353173,1332782,1234617,13129,5815,509,473,372,352,351,120}'::integer[]))
37. 1,328.688 1,328.688 ↓ 0.0 0 332,172

Index Scan using idx_non_zero_pv_on_time_series_id_offering_date on plan_values pv_1 (cost=0.70..4.90 rows=1 width=31) (actual time=0.004..0.004 rows=0 loops=332,172)

  • Index Cond: ((time_series_id = pm_1.active_time_series_id) AND ((offering_type)::text = 'PublicEc2Offering'::text) AND (date >= '2020-01-01'::date) AND (date <= '2020-12-01'::date))
38. 164.272 164.272 ↑ 1.0 1 82,136

Index Only Scan using index_full_fleets_on_id on fleets f_1 (cost=0.43..0.66 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=82,136)

  • Index Cond: (id = fr_1.descendant_id)
  • Heap Fetches: 93089
39. 34.937 147.810 ↓ 1.2 126,949 1

Materialize (cost=20,699.47..21,219.98 rows=104,102 width=32) (actual time=97.138..147.810 rows=126,949 loops=1)

40. 63.876 112.873 ↑ 2.3 45,682 1

Sort (cost=20,699.47..20,959.73 rows=104,102 width=32) (actual time=97.129..112.873 rows=45,682 loops=1)

  • Sort Key: rcv_1.offering_id, rcv_1.rate_card_id
  • Sort Method: external merge Disk: 2192kB
41. 48.997 48.997 ↑ 2.0 51,719 1

Seq Scan on rate_card_values rcv_1 (cost=0.00..9,531.80 rows=104,102 width=32) (actual time=8.160..48.997 rows=51,719 loops=1)

  • Filter: ((offering_type)::text = 'PublicEc2Offering'::text)
  • Rows Removed by Filter: 172833
Planning time : 2,230.036 ms
Execution time : 25,894.747 ms