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
Attachment:
OpenPGP_signature
Description: OpenPGP digital signature
