explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D1pI

Settings
# exclusive inclusive rows x rows loops node
1. 0.738 12,693.670 ↓ 18.0 360 1

Sort (cost=165,114.73..165,114.78 rows=20 width=115) (actual time=12,693.550..12,693.670 rows=360 loops=1)

  • Sort Key: (CASE WHEN (ii.area_eid = ANY ('{103,104,105,106}'::integer[])) THEN 43 WHEN (ii.area_eid = ANY ('{6,7}'::integer[])) THEN 8 WHEN (ii.area_eid = ANY ('{247,249,250,10}'::integer[])) THEN 248 ELSE ii.area_eid END), ii.data_source_id, (CASE WHEN (ii.fuel_id = ANY ('{21,22}'::integer[])) THEN 24 ELSE ii.fuel_id END), (date_trunc('hour'::text, ii.start_time))
  • Sort Method: quicksort Memory: 75kB
2. 0.792 12,692.932 ↓ 18.0 360 1

Nested Loop (cost=165,020.09..165,114.30 rows=20 width=115) (actual time=12,689.777..12,692.932 rows=360 loops=1)

3. 0.296 12,691.780 ↓ 18.0 360 1

Hash Join (cost=165,019.80..165,097.94 rows=20 width=90) (actual time=12,689.753..12,691.780 rows=360 loops=1)

  • Hash Cond: (ii.data_source_id = c.id)
4. 0.305 12,691.398 ↓ 6.4 360 1

Hash Join (cost=165,017.20..165,094.63 rows=56 width=90) (actual time=12,689.658..12,691.398 rows=360 loops=1)

  • Hash Cond: ((CASE WHEN (ii.fuel_id = ANY ('{21,22}'::integer[])) THEN 24 ELSE ii.fuel_id END) = d.id)
5. 1.018 12,691.060 ↓ 1.1 360 1

GroupAggregate (cost=165,015.46..165,085.21 rows=340 width=66) (actual time=12,689.616..12,691.060 rows=360 loops=1)

  • Group Key: (date_trunc('hour'::text, ii.start_time)), ii.area_eid, ii.data_source_id, ii.fuel_id
6. 1.189 12,690.042 ↑ 2.7 1,246 1

Sort (cost=165,015.46..165,023.97 rows=3,403 width=30) (actual time=12,689.600..12,690.042 rows=1,246 loops=1)

  • Sort Key: (date_trunc('hour'::text, ii.start_time)), ii.area_eid, ii.data_source_id, ii.fuel_id
  • Sort Method: quicksort Memory: 146kB
7. 1.088 12,688.853 ↑ 2.7 1,246 1

Subquery Scan on ii (cost=164,730.71..164,815.83 rows=3,403 width=30) (actual time=12,686.438..12,688.853 rows=1,246 loops=1)

8. 0.902 12,687.765 ↑ 2.7 1,246 1

Unique (cost=164,730.71..164,773.29 rows=3,403 width=30) (actual time=12,686.433..12,687.765 rows=1,246 loops=1)

9. 1.431 12,686.863 ↑ 2.7 1,246 1

Sort (cost=164,730.71..164,739.22 rows=3,407 width=30) (actual time=12,686.431..12,686.863 rows=1,246 loops=1)

  • Sort Key: actual_production.area_eid, actual_production.data_source_id, actual_production.fuel_id, actual_production.start_time, actual_production.dump_date DESC
  • Sort Method: quicksort Memory: 146kB
10. 21.628 12,685.432 ↑ 2.7 1,246 1

Gather (cost=93,478.29..164,530.81 rows=3,407 width=30) (actual time=12,653.966..12,685.432 rows=1,246 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
11. 20.985 12,663.804 ↑ 3.4 249 5

Parallel Bitmap Heap Scan on actual_production (cost=92,478.29..163,190.11 rows=852 width=30) (actual time=12,632.507..12,663.804 rows=249 loops=5)

  • Recheck Cond: (((start_time >= '2019-02-05 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-02-06 00:00:00'::timestamp without time zone)) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 69) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[]))) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 229) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[]))) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 245) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[]))) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 73) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[]))) OR ((start_time >= '2019-01-10 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-11 00:00:00'::timestamp without time zone)) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 108) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[]))) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 109) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[]))) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 9) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[]))) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone)) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone)) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 227) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[]))) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 96) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[]))) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = ANY ('{222,254}'::integer[])) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[]))) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone)) OR ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone)))
  • Filter: ((fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[])) AND (((area_eid = ANY ('{103,104,105,106}'::integer[])) AND (start_time >= '2019-02-05 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-02-06 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-02-05'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = 69) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = 229) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = 245) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = 73) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = ANY ('{230,236}'::integer[])) AND (start_time >= '2019-01-10 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-11 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = 108) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = 109) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = 9) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = ANY ('{6,7,8}'::integer[])) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = ANY ('{247,248,249,250,10}'::integer[])) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = 227) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = 96) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = ANY ('{222,254}'::integer[])) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = ANY ('{30,31,33,34,35,38,232}'::integer[])) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval))) OR ((area_eid = ANY ('{252,100,270,235,256,98,233,268}'::integer[])) AND (start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (dump_date <= (timezone('Europe/Amsterdam'::text, ('2019-01-28'::date)::timestamp with time zone) + '1 day'::interval)))))
  • Rows Removed by Filter: 8392
  • Heap Blocks: exact=2499
12. 0.030 12,642.819 ↓ 0.0 0 1

BitmapOr (cost=92,478.29..92,478.29 rows=86,299 width=0) (actual time=12,642.819..12,642.819 rows=0 loops=1)

13. 23.735 23.735 ↓ 14.6 191,994 1

Bitmap Index Scan on actual_production_start_time_index (cost=0.00..189.69 rows=13,125 width=0) (actual time=23.735..23.735 rows=191,994 loops=1)

  • Index Cond: ((start_time >= '2019-02-05 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-02-06 00:00:00'::timestamp without time zone))
14. 1,067.673 1,067.673 ↓ 23.3 6,752 1

Bitmap Index Scan on actual_production_pkey (cost=0.00..8,299.94 rows=290 width=0) (actual time=1,067.673..1,067.673 rows=6,752 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 69) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[])))
15. 1,006.995 1,006.995 ↓ 11.2 900 1

Bitmap Index Scan on actual_production_pkey (cost=0.00..8,299.94 rows=80 width=0) (actual time=1,006.995..1,006.995 rows=900 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 229) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[])))
16. 1,552.843 1,552.843 ↓ 26.4 9,033 1

Bitmap Index Scan on actual_production_pkey (cost=0.00..8,299.94 rows=342 width=0) (actual time=1,552.843..1,552.843 rows=9,033 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 245) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[])))
17. 1,489.666 1,489.666 ↓ 6.0 1,286 1

Bitmap Index Scan on actual_production_pkey (cost=0.00..8,299.94 rows=214 width=0) (actual time=1,489.666..1,489.666 rows=1,286 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 73) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[])))
18. 3.396 3.396 ↓ 1.1 21,656 1

Bitmap Index Scan on actual_production_start_time_index (cost=0.00..283.90 rows=19,646 width=0) (actual time=3.396..3.396 rows=21,656 loops=1)

  • Index Cond: ((start_time >= '2019-01-10 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-11 00:00:00'::timestamp without time zone))
19. 1,257.633 1,257.633 ↓ 0.0 0 1

Bitmap Index Scan on actual_production_pkey (cost=0.00..8,299.94 rows=7 width=0) (actual time=1,257.633..1,257.633 rows=0 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 108) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[])))
20. 1,037.969 1,037.969 ↓ 0.0 0 1

Bitmap Index Scan on actual_production_pkey (cost=0.00..8,299.94 rows=7 width=0) (actual time=1,037.969..1,037.969 rows=0 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 109) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[])))
21. 997.996 997.996 ↓ 0.0 0 1

Bitmap Index Scan on actual_production_pkey (cost=0.00..8,299.94 rows=7 width=0) (actual time=997.996..997.996 rows=0 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 9) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[])))
22. 49.110 49.110 ↓ 30.0 393,599 1

Bitmap Index Scan on actual_production_start_time_index (cost=0.00..189.69 rows=13,125 width=0) (actual time=49.110..49.110 rows=393,599 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone))
23. 48.849 48.849 ↓ 30.0 393,599 1

Bitmap Index Scan on actual_production_start_time_index (cost=0.00..189.69 rows=13,125 width=0) (actual time=48.849..48.849 rows=393,599 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone))
24. 995.086 995.086 ↓ 0.0 0 1

Bitmap Index Scan on actual_production_pkey (cost=0.00..8,299.94 rows=7 width=0) (actual time=995.086..995.086 rows=0 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 227) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[])))
25. 985.588 985.588 ↓ 0.0 0 1

Bitmap Index Scan on actual_production_pkey (cost=0.00..8,299.94 rows=7 width=0) (actual time=985.588..985.588 rows=0 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = 96) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[])))
26. 2,029.366 2,029.366 ↓ 26.4 1,713 1

Bitmap Index Scan on actual_production_pkey (cost=0.00..16,532.88 rows=65 width=0) (actual time=2,029.366..2,029.366 rows=1,713 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone) AND (area_eid = ANY ('{222,254}'::integer[])) AND (fuel_id = ANY ('{19,21,22,24}'::integer[])) AND (data_source_id = ANY ('{5,7,22,25,45,56}'::integer[])))
27. 48.653 48.653 ↓ 30.0 393,599 1

Bitmap Index Scan on actual_production_start_time_index (cost=0.00..189.69 rows=13,125 width=0) (actual time=48.653..48.653 rows=393,599 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone))
28. 48.231 48.231 ↓ 30.0 393,599 1

Bitmap Index Scan on actual_production_start_time_index (cost=0.00..189.69 rows=13,125 width=0) (actual time=48.231..48.231 rows=393,599 loops=1)

  • Index Cond: ((start_time >= '2019-01-28 00:00:00+01'::timestamp with time zone) AND (start_time < '2019-01-29 00:00:00'::timestamp without time zone))
29. 0.016 0.033 ↑ 1.0 33 1

Hash (cost=1.33..1.33 rows=33 width=36) (actual time=0.033..0.033 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
30. 0.017 0.017 ↑ 1.0 33 1

Seq Scan on fuel d (cost=0.00..1.33 rows=33 width=36) (actual time=0.004..0.017 rows=33 loops=1)

31. 0.043 0.086 ↓ 1.0 73 1

Hash (cost=1.71..1.71 rows=71 width=4) (actual time=0.086..0.086 rows=73 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
32. 0.043 0.043 ↓ 1.0 73 1

Seq Scan on data_source c (cost=0.00..1.71 rows=71 width=4) (actual time=0.010..0.043 rows=73 loops=1)

33. 0.360 0.360 ↑ 1.0 1 360

Index Scan using common_eic_eid_index on eic b (cost=0.29..0.81 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=360)

  • Index Cond: ((eid)::integer = (CASE WHEN (ii.area_eid = ANY ('{103,104,105,106}'::integer[])) THEN 43 WHEN (ii.area_eid = ANY ('{6,7}'::integer[])) THEN 8 WHEN (ii.area_eid = ANY ('{247,249,250,10}'::integer[])) THEN 248 ELSE ii.area_eid END))