Hey Sven!
Thanks for getting back to me. I suspect that the poor performance is from the increase in the number of hypertables… But ~600 doesn’t seem like an excessive number?
The query:
SELECT
c.minute_bucket AS time,
a.sensor,
a.network_key,
a.interface_type,
a.service,
c.latency
FROM (
SELECT
sensor,
network_key,
interface_type,
service,
customer
FROM
ping d
WHERE
timestamp > (unix_now () - 3600)
AND customer = '<some-uuid>'
AND is_internal = true
GROUP BY
sensor,
customer,
network_key,
interface_type,
service) a
LEFT JOIN LATERAL (
SELECT
minute_bucket,
avg(b.latency) OVER (ORDER BY b.minute_bucket ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS latency,
b.customer,
b.sensor,
b.network_key,
b.service,
b.interface_type
FROM (
SELECT
time_bucket_gapfill (60, timestamp) AS minute_bucket,
interpolate (avg(latency)) AS latency,
x.customer,
x.sensor,
x.network_key,
x.service,
x.interface_type
FROM
ping x
WHERE
timestamp > (unix_now () - 3600)
AND timestamp <= unix_now ()
AND x.customer = a.customer
AND x.sensor = a.sensor
AND x.network_key = a.network_key
AND x.service = a.service
AND x.interface_type = a.interface_type
GROUP BY
minute_bucket,
x.customer,
x.sensor,
x.network_key,
x.service,
x.interface_type
ORDER BY
minute_bucket) b) c ON c.customer = a.customer
WHERE
c.latency IS NOT NULL;
The plan:
| Nested Loop (cost=1413828.17..123381928.86 rows=32052 width=90) (actual time=27.573..84.477 rows=23149 loops=1) |
| -> HashAggregate (cost=1413823.51..1413916.82 rows=9331 width=115) (actual time=27.482..27.735 rows=421 loops=1) |
| Group Key: d.sensor, d.customer, d.network_key, d.interface_type, d.service |
| -> Custom Scan (ChunkAppend) on ping d (cost=0.53..91901.00 rows=105753801 width=115) (actual time=5.052..24.219 rows=6557 loops=1) |
| Chunks excluded during startup: 689 |
| -> Bitmap Heap Scan on _hyper_15_65843_chunk d_1 (cost=753.26..34897.40 rows=8642 width=115) (actual time=5.051..23.776 rows=6557 loops=1) |
| Recheck Cond: ((customer = '<some-uuid>'::text) AND ("timestamp" > ((date_part('epoch'::text, now()))::integer - 3600))) |
| Filter: is_internal |
| Rows Removed by Filter: 27058 |
| Heap Blocks: exact=5599 |
| -> Bitmap Index Scan on _hyper_15_65843_chunk_ping__customer_timestamp_idx (cost=0.00..751.10 rows=31933 width=0) (actual time=4.284..4.284 rows=33642 loops=1) |
| Index Cond: ((customer = '<some-uuid>'::text) AND ("timestamp" > ((date_part('epoch'::text, now()))::integer - 3600))) |
| -> Subquery Scan on c (cost=4.66..13071.23 rows=3 width=49) (actual time=0.032..0.129 rows=55 loops=421) |
| Filter: ((c.latency IS NOT NULL) AND (d.customer = c.customer)) |
| Rows Removed by Filter: 6 |
| -> WindowAgg (cost=4.66..13062.60 rows=690 width=177) (actual time=0.025..0.122 rows=61 loops=421) |
| -> Subquery Scan on b (cost=4.66..13052.25 rows=690 width=49) (actual time=0.023..0.074 rows=61 loops=421) |
| -> Custom Scan (GapFill) (cost=4.66..13045.35 rows=690 width=127) (actual time=0.022..0.067 rows=61 loops=421) |
| -> GroupAggregate (cost=4.66..13040.18 rows=690 width=0) (actual time=0.021..0.056 rows=11 loops=421) |
| Group Key: (time_bucket_gapfill(60, x."timestamp", NULL::integer, NULL::integer)), x.customer, x.sensor, x.network_key, x.service, x.interface_type |
| -> Custom Scan (ChunkAppend) on ping x (cost=4.66..3752.03 rows=530160 width=129) (actual time=0.017..0.037 rows=16 loops=421) |
| Order: time_bucket_gapfill(60, x."timestamp", NULL::integer, NULL::integer) |
| Chunks excluded during startup: 689 |
| -> Index Scan Backward using _hyper_15_65843_chunk_ping__customer_sensor_network_key_service on _hyper_15_65843_chunk x_1 (cost=0.70..2.94 rows=1 width=129) (actual time=0.017..0.036 rows=16 loops=421) |
| Index Cond: ((customer = d.customer) AND (sensor = d.sensor) AND (network_key = d.network_key) AND (service = d.service) AND (interface_type = d.interface_type) AND ("timestamp" > ((date_part('epoch'::text, now()))::integer - 3600)) AND ("timestamp" <= (date_part('epoch'::text, now()))::integer)) |
| Planning Time: 2373.466 ms |
| Execution Time: 111.131 ms |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+