explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VMSA

Settings
# exclusive inclusive rows x rows loops node
1. 191.829 1,363.165 ↑ 15.2 5,375 1

GroupAggregate (cost=0.73..101,856.51 rows=81,435 width=820) (actual time=0.931..1,363.165 rows=5,375 loops=1)

  • Group Key: s.id
2. 177.829 1,171.336 ↓ 9.6 1,370,395 1

Merge Join (cost=0.73..99,568.23 rows=142,234 width=772) (actual time=0.121..1,171.336 rows=1,370,395 loops=1)

  • Merge Cond: (sub.series_id = s.id)
3. 170.299 964.315 ↓ 9.6 1,370,395 1

Subquery Scan on sub (cost=0.44..87,844.19 rows=142,234 width=20) (actual time=0.063..964.315 rows=1,370,395 loops=1)

  • Filter: ((sub.bucket - sub.data_time) < '00:05:00'::interval)
  • Rows Removed by Filter: 16,355
4. 126.990 794.016 ↓ 3.2 1,386,750 1

Custom Scan (GapFill) (cost=0.44..81,443.66 rows=426,702 width=0) (actual time=0.061..794.016 rows=1,386,750 loops=1)

5. 260.590 667.026 ↑ 1.6 271,925 1

GroupAggregate (cost=0.44..81,443.66 rows=426,702 width=0) (actual time=0.058..667.026 rows=271,925 loops=1)

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

Result (cost=0.44..66,447.37 rows=539,549 width=28) (actual time=0.036..406.436 rows=543,850 loops=1)

7. 39.747 341.219 ↓ 1.0 543,850 1

Merge Append (cost=0.44..59,703.01 rows=539,549 width=20) (actual time=0.034..341.219 rows=543,850 loops=1)

  • Sort Key: data.series_id, (time_bucket_gapfill('00:00:14'::interval, data."time", '2019-12-29 08:00:00.711+00'::timestamp with time zone, '2019-12-29 09:00:00.711+00'::timestamp with time zone))
8. 301.472 301.472 ↓ 1.0 543,850 1

Index Only Scan using _hyper_165_201_chunk_series_id_time_value_idx on _hyper_165_201_chunk data (cost=0.43..54,307.51 rows=539,549 width=20) (actual time=0.034..301.472 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))
  • Heap Fetches: 543,850
9. 29.192 29.192 ↑ 1.0 81,243 1

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