explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yCHh

Settings
# exclusive inclusive rows x rows loops node
1. 211.619 7,097.991 ↑ 15.2 5,375 1

GroupAggregate (cost=85,486.75..122,331.65 rows=81,435 width=820) (actual time=5,530.507..7,097.991 rows=5,375 loops=1)

  • Group Key: s.id
2. 193.343 6,886.372 ↓ 9.6 1,370,395 1

Merge Join (cost=85,486.75..120,041.19 rows=142,525 width=772) (actual time=5,530.212..6,886.372 rows=1,370,395 loops=1)

  • Merge Cond: (sub.series_id = s.id)
3. 186.464 6,207.257 ↓ 9.6 1,370,395 1

Subquery Scan on sub (cost=85,486.46..108,318.57 rows=142,525 width=20) (actual time=5,523.458..6,207.257 rows=1,370,395 loops=1)

  • Filter: ((sub.bucket - sub.data_time) < '00:05:00'::interval)
  • Rows Removed by Filter: 16355
4. 141.985 6,020.793 ↓ 3.2 1,386,750 1

Custom Scan (GapFill) (cost=85,486.46..101,904.93 rows=427,576 width=0) (actual time=5,523.455..6,020.793 rows=1,386,750 loops=1)

5. 258.873 5,878.808 ↑ 1.6 271,925 1

GroupAggregate (cost=85,486.46..101,904.93 rows=427,576 width=0) (actual time=5,523.451..5,878.808 rows=271,925 loops=1)

  • Group Key: data.series_id, (time_bucket_gapfill('00:00:14'::interval, data."time"))
6. 591.908 5,619.935 ↓ 1.0 543,850 1

Sort (cost=85,486.46..86,846.06 rows=543,841 width=28) (actual time=5,523.419..5,619.935 rows=543,850 loops=1)

  • Sort Key: data.series_id, (time_bucket_gapfill('00:00:14'::interval, data."time"))
  • Sort Method: external merge Disk: 22384kB
7. 202.244 5,028.027 ↓ 1.0 543,850 1

Result (cost=0.43..26,056.02 rows=543,841 width=28) (actual time=6.200..5,028.027 rows=543,850 loops=1)

8. 105.695 4,825.783 ↓ 1.0 543,850 1

Append (cost=0.43..19,258.01 rows=543,841 width=20) (actual time=6.195..4,825.783 rows=543,850 loops=1)

9. 4,720.088 4,720.088 ↓ 1.0 543,850 1

Index Scan using _hyper_203_180_chunk_container_tasks_state_time on _hyper_203_180_chunk data (cost=0.43..16,538.80 rows=543,841 width=20) (actual time=6.193..4,720.088 rows=543,850 loops=1)

  • Index Cond: (("time" < '2019-12-29 09:00:00.711+00'::timestamp with time zone) AND ("time" > '2019-12-29 07:55:00.711+00'::timestamp with time zone))
10. 485.772 485.772 ↑ 1.0 81,243 1

Index Scan using series_pkey on series s (cost=0.29..9,737.47 rows=81,435 width=756) (actual time=2.194..485.772 rows=81,243 loops=1)

Planning time : 164.373 ms
Execution time : 7,104.112 ms