I have an Ignition installation feeding into a Postgres DB with TimescaleDB. The setup is as follows :
- Postgres 15
- Hypertable with 100 parallel chunks, 12h interval
- Autocompression set to 24h
In theory, no data should need to be decompressed, unless I query > 24h from now, yet, when I do this :
EXPLAIN ANALYSE
SELECT
"tagid","intvalue","floatvalue","stringvalue","datevalue","t_stamp","dataintegrity"
FROM
sqlth_1_data
WHERE
"t_stamp">cast(extract(epoch from now()) - extract(epoch from INTERVAL '4 hours') as BIGINT)*1000
ORDER BY
"t_stamp" DESC
I get a slow query that decompresses chunks, same if I try with 10 minutes. Is my query wrong ? Or is the setup wrong ?