Message-ID: <04e783a75738a548708b40732b263e7d2a3119ab.camel@cybertec.at>
Subject: Re: slow sql query for big items
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Hua W Peng <huawaltp@gmail.com>, pgsql-general@lists.postgresql.org
Date: Sat, 28 Mar 2026 08:33:31 +0100
In-Reply-To: <CAMiP+MhLzwNzjvxsLnLZvh+P+6zcakno4HfjSWKoDMC2GeFovA@mail.gmail.com>
References: 
	<CAMiP+MhLzwNzjvxsLnLZvh+P+6zcakno4HfjSWKoDMC2GeFovA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/04e783a75738a548708b40732b263e7d2a3119ab.camel%40cybertec.at>
Precedence: bulk

On Sat, 2026-03-28 at 15:07 +0800, Hua W Peng wrote:
> I have a common table for telemetry data. the stru is:
> [25 columns]
>=20
> Data records are growing by about 10 million every day, reaching 300 mill=
ion per month.
> In this case, even a simple COUNT(*) query becomes extremely slow, taking=
 about
> 7-8 minutes to finish.
> I am running PostgreSQL 14 on Ubuntu 22.04 with a 24GB shared buffer.
> And, though in our test env we have timescaledb enabled:
>=20
> Triggers:
> =C2=A0 =C2=A0 ts_insert_blocker BEFORE INSERT ON dms_data_gzdy FOR EACH R=
OW EXECUTE FUNCTION _timescaledb_functions.insert_blocker()
> Number of child tables: 9 (Use \d+ to list them.)
>=20
> But in production env there is no timescaledb which can't be installed as=
 well.
>=20
> Can you help me?

First, a test environment should be as similar to production as possible,
otherwise it cannot serve its purpose.

There is little you can do about speeding up count(*), it is bound to be sl=
ow.
See https://www.cybertec-postgresql.com/en/postgresql-count-made-fast/

But if counting the rows is really your use case, you are doing something w=
rong.
I suspect that your real problem are other queries.

The way to get good performance with large tables it to have your queries u=
se
an index scan.  The indexes you need will depend on your queries, so withou=
t
knowing the queries, it is impossible to recommend anything.

Partitioning is not primarily a measuer for improving query performance,
but it would still be a smart idea, primarily to be able to delete old data
efficiently.  It doesn't matter if you use TimescaleDB for partitioning or
use PostgreSQL's support directly.

Yours,
Laurenz Albe