From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
Date: Mon, 11 Jul 2022 10:33:41 +0200
Message-ID: <7ffca868-0728-6263-eac5-33cf56a46a54@jakobs.com> (raw)
In-Reply-To: <CACxU--UJY4sD2SjW8V3W0oAPuuTzE3fAvBzTaej-BmUUNJxN_Q@mail.gmail.com>
References: <CACxU--UJY4sD2SjW8V3W0oAPuuTzE3fAvBzTaej-BmUUNJxN_Q@mail.gmail.com>
Hi Raj,
Since PostgreSQL uses MVCC there is no current number of rows as an
absolute truth. Every COUNT(*) has to check the visibility of each row
for the current transaction. This makes it slow for large tables.
If you don't need the exact figure but just an estimation, you can get
this from the internal statistics tables. After an ANALYZE they are
close to the "real value".
This show the number of rows for all non-empty tables in all visible
schemas.
SELECT nspname AS schemaname,relname,reltuples::numeric
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
nspname IN (SELECT unnest(current_schemas(false))) AND
relkind='r' AND reltuples > 0
ORDER BY reltuples DESC;
Regards,
Holger
Am 11.07.22 um 09:16 schrieb Raj kumar:
> Hi,
>
> How can I improve "select count(*)" for larger tables? I'm doing a db
> migration and need to validate the data count.
> "select count(*) " queries are taking more than 30 minutes for some
> tables which is more than the downtime we have.
> Will work_mem increase help? or how can i speed up this row count?
>
> Thanks,
> Raj
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: Improve "select count(*)" query - takes more than 30 mins for some large tables
In-Reply-To: <7ffca868-0728-6263-eac5-33cf56a46a54@jakobs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox