I have a client_requests table with approximately 66k records. It has the following structure:
metrics=# \d+ client_requests;
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------+--------------------------------+-----------+----------+---------------------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('client_requests_id_seq'::regclass) | plain | |
channel_id | bigint | | not null | | plain | |
process_id | uuid | | not null | | plain | |
route | character varying(255) | | | | extended | |
route_name | character varying(255) | | | | extended | |
duration | double precision | | not null | | plain | |
timestamp | timestamp(0) without time zone | | not null | | plain | |
events | jsonb | | | | extended | |
created_at | timestamp(0) without time zone | | | | plain | |
updated_at | timestamp(0) without time zone | | | | plain | |
Indexes:
"client_requests_timestamp_idx" btree ("timestamp" DESC)
Foreign-key constraints:
"client_requests_channel_id_foreign" FOREIGN KEY (channel_id) REFERENCES channels(id)
"client_requests_process_id_foreign" FOREIGN KEY (process_id) REFERENCES processes(id)
Triggers:
ts_insert_blocker BEFORE INSERT ON client_requests FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Child tables: _timescaledb_internal._hyper_1_1_chunk,
_timescaledb_internal._hyper_1_2_chunk,
_timescaledb_internal._hyper_1_3_chunk,
_timescaledb_internal._hyper_1_4_chunk,
_timescaledb_internal._hyper_1_5_chunk
metrics=# select count(*) from client_requests;
count
-------
66874
(1 row)
However, the following query takes 168ms to run, which is far longer than I would expect (I would expect single digit ms):
select
time_bucket_gapfill('12 hours', timestamp, '2021-01-13 16:44:55', '2021-02-12 16:44:55') AS ts
,coalesce(ROUND(percentile_cont(0.5) within group (order by client_requests.duration)::numeric, 0), 0.00) as typical_response_time
,coalesce(ROUND(percentile_cont(0.95) within group (order by client_requests.duration)::numeric, 0), 0.00) as problem_response_time
from "client_requests"
where "timestamp" > NOW() - INTERVAL '30 days'
group by "ts"
order by "ts" asc;
(66 rows)
Time: 168.704 ms
An EXPLAIN ANALYZE query return the following:
Custom Scan (GapFill) (cost=21746.41..23921.40 rows=38593 width=72) (actual time=152.965..199.968 rows=66 loops=1)
-> GroupAggregate (cost=21746.41..23438.99 rows=38593 width=0) (actual time=152.957..199.864 rows=61 loops=1)
Group Key: (time_bucket_gapfill('12:00:00'::interval, client_requests."timestamp"))
-> Sort (cost=21746.41..21904.23 rows=63127 width=16) (actual time=152.703..163.472 rows=63101 loops=1)
Sort Key: (time_bucket_gapfill('12:00:00'::interval, client_requests."timestamp"))
Sort Method: quicksort Memory: 4494kB
-> Custom Scan (ChunkAppend) on client_requests (cost=0.00..16713.30 rows=63127 width=16) (actual time=3.251..120.119 rows=63101 loops=1)
Chunks excluded during startup: 0
-> Seq Scan on _hyper_1_1_chunk (cost=0.00..3239.92 rows=8866 width=16) (actual time=3.246..17.159 rows=8868 loops=1)
Filter: ("timestamp" > (now() - '30 days'::interval))
Rows Removed by Filter: 3813
-> Seq Scan on _hyper_1_2_chunk (cost=0.00..4520.70 rows=17583 width=16) (actual time=0.020..26.201 rows=17583 loops=1)
Filter: ("timestamp" > (now() - '30 days'::interval))
-> Seq Scan on _hyper_1_3_chunk (cost=0.00..3606.74 rows=16214 width=16) (actual time=0.028..22.497 rows=16214 loops=1)
Filter: ("timestamp" > (now() - '30 days'::interval))
-> Seq Scan on _hyper_1_4_chunk (cost=0.00..3584.34 rows=14191 width=16) (actual time=0.024..21.248 rows=14191 loops=1)
Filter: ("timestamp" > (now() - '30 days'::interval))
-> Seq Scan on _hyper_1_5_chunk (cost=0.00..1603.78 rows=6273 width=16) (actual time=0.021..9.236 rows=6245 loops=1)
Filter: ("timestamp" > (now() - '30 days'::interval))
Planning Time: 2.128 ms
Execution Time: 201.534 ms
(21 rows)
Time: 205.592 ms
With so few records in the table, its unclear to me why this query is performing so poorly.
