I have stock 1 minute data DB organized in hypertable, chunks = 1 day.
i used 2 minutes CAGG .
query = “”" SELECT * FROM m2
WHERE time >= ‘2024-03-28T00:00:00’
ORDER BY ticker"“”
As you can see query is very simple, result is 31M rows and takes 71 s.
In my opinion it’s ridiculously slow, but may be it’s normal speed??
I hope i’ve done smth wrong, any help appreciated
I run docker instanse on PC with Ryzen 5600 with 16 Gb RAM and 1700 Mb/s NVME
EXPLAIN (ANALYZE) gives this:
https://explain.depesz.com/s/7s14#html
Hypertable has index f"CREATE INDEX {index_name} ON {tbl_name} (ticker, time DESC);"
m2 CAGG sql:
CREATE MATERIALIZED VIEW if not EXISTS m2
WITH (timescaledb.continuous, timescaledb.materialized_only = True)
AS SELECT
time_bucket(‘2 minutes’::interval, time) as time,
ticker,
first(open, time) AS open,
max(high) AS high,
min(low) AS low,
last(close, time) AS close,
last(volume, time) as volume
FROM stocks_m1
GROUP BY 1, 2
ORDER BY 2, 1 ASC;