explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PK7qs

Settings
# exclusive inclusive rows x rows loops node
1. 1.496 132,796.523 ↓ 95.0 95 1

Nested Loop (cost=1,872.19..408,131.50 rows=1 width=124) (actual time=1,319.942..132,796.523 rows=95 loops=1)

  • Join Filter: (emissions.event_time = generation.event_time)
2. 8.665 132,792.842 ↓ 95.0 95 1

Nested Loop (cost=1,871.78..408,124.06 rows=1 width=88) (actual time=1,319.899..132,792.842 rows=95 loops=1)

  • Join Filter: (eg_realpower_15min.local_15min = emissions.event_time)
  • Rows Removed by Join Filter: 9025
3. 12.557 12.557 ↓ 95.0 95 1

Seq Scan on emissions (cost=0.00..4,885.50 rows=1 width=44) (actual time=11.457..12.557 rows=95 loops=1)

  • Filter: ((event_time >= '2019-07-01 00:00:00'::timestamp without time zone) AND (event_time < '2019-07-02 00:00:00'::timestamp without time zone) AND (((date_part('minute'::text, event_time))::integer % 15) = 0))
  • Rows Removed by Filter: 84285
4. 12,333.470 132,771.620 ↑ 6.5 96 95

Subquery Scan on eg_realpower_15min (cost=1,871.78..403,230.76 rows=624 width=44) (actual time=1,321.913..1,397.596 rows=96 loops=95)

  • Filter: ((eg_realpower_15min.local_15min >= '2019-07-01 00:00:00-05'::timestamp with time zone) AND (eg_realpower_15min.local_15min < '2019-07-02 00:00:00-05'::timestamp with time zone))
  • Rows Removed by Filter: 205958
5. 23,375.510 120,438.150 ↓ 1.7 206,054 95

Append (cost=1,871.78..401,359.82 rows=124,729 width=2,476) (actual time=11.958..1,267.770 rows=206,054 loops=95)

6. 31,251.200 93,179.705 ↓ 1.8 183,827 95

Subquery Scan on *SELECT* 1 (cost=1,871.78..333,958.11 rows=100,027 width=2,424) (actual time=11.956..980.839 rows=183,827 loops=95)

7. 60,860.610 61,928.505 ↓ 1.8 183,827 95

Bitmap Heap Scan on egauge_15min_wide_real_2012to2018 (cost=1,871.78..332,707.77 rows=100,027 width=2,424) (actual time=11.952..651.879 rows=183,827 loops=95)

  • Recheck Cond: (dataid = 9983)
  • Heap Blocks: exact=331835
8. 1,067.895 1,067.895 ↓ 1.8 183,827 95

Bitmap Index Scan on electricity_egauge_15min_did_idx (cost=0.00..1,846.77 rows=100,027 width=0) (actual time=11.241..11.241 rows=183,827 loops=95)

  • Index Cond: (dataid = 9983)
9. 3,688.470 3,882.935 ↑ 1.1 22,227 95

Bitmap Heap Scan on egauge_15min_wide_real_2019 (cost=1,128.00..66,531.05 rows=24,702 width=310) (actual time=2.831..40.873 rows=22,227 loops=95)

  • Recheck Cond: (dataid = 9983)
  • Heap Blocks: exact=372780
10. 194.465 194.465 ↑ 1.1 22,227 95

Bitmap Index Scan on egauge_15min_wide_real_2019_idx (cost=0.00..1,121.82 rows=24,702 width=0) (actual time=2.047..2.047 rows=22,227 loops=95)

  • Index Cond: (dataid = 9983)
11. 2.185 2.185 ↑ 1.0 1 95

Index Scan using generation_un on generation (cost=0.42..7.39 rows=1 width=77) (actual time=0.023..0.023 rows=1 loops=95)

  • Index Cond: ((event_time = eg_realpower_15min.local_15min) AND (event_time >= '2019-07-01 00:00:00'::timestamp without time zone) AND (event_time < '2019-07-02 00:00:00'::timestamp without time zone))
  • Filter: (((date_part('minute'::text, event_time))::integer % 15) = 0)
Planning time : 1.053 ms