TimescaleDB: SELECT COUNT(*) slow on hypertable
Shows how to fix timescaleDB: SELECT COUNT(*) slow on hypertable. Use it when you hit this exact problem. Skip it when your error message or symptom looks different.
TL;DR
Context: Stack Overflow #67953098 (accepted answer, 9 votes): I have a hyper table for exchange candle data set up using TimescaleDB. - TimescaleDB official image timescale/timescaledb:latest-pg12 set up and running with Docker with the exact version string starting PostgreSQL 12.6 on x86\64-pc-linux-musl, compiled by gcc (Alpine 10.2.1\pre1) 10.2.1 20201203, 64-bit - Python 3 client - The table has 5 continuous aggregate views set up like here and around 15 colums Running the following query is slow (count query generated with SQLAlchemy): ``` SELECT count(*) AS count1 FROM (SELECT myhype
Steps
- you can try the approximaterowcount() function (https://docs.tigerdata.com/api/latest/hyperfunctions/approximaterowcount/) which gives an immediate result.
When to use
You are seeing this: you can try the approximaterowcount() function (https://docs.tigerdata.com/api/latest/hyperfunctions/approximaterowcount/) which gives an immediate result. Use this skill when you run into "TimescaleDB: SELECT COUNT(*) slow on hypertable".
When not to use
If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.
Versions
Versions mentioned in the source: 10.2.1_pre1 10.2.1. If you are on something much newer or older, the details may have shifted.
Why this happens
The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.
Sources
- https://docs.tigerdata.com/api/latest/hyperfunctions/approximaterowcount/