Dann Corbit wrote:
If you only need a cardinality estimate, then pg_class.reltuples may be
of help (it will be accurate to when the last vacuum was performed).
Last vacuum ... how does that work with autovacuum?
If you need exact counts then there are a couple of problems:
1. An MVCC database cannot store an exact count, because it can differ
by user. Hence, to collect the exact number, a table scan is necessary.
A table scan ... ouch? I just assumed that COUNT(*) FROM table_name
would be a fast query internally. I see what you mean about MVCC, though.
2. The number can be invalid immediately after the query and might be
different for different users anyway.
The numbers don't really need to be 100% accurate (it's just a ballpark
stat).
What are you doing with those numbers?
It's just an administrative report showing patterns of growth in our
database storage. We are trying to keep statistics for users and our
stats tables are generating about 50,000 records daily. We only know
this is true because we have this reports which shows table record
counts daily.
-- Dante
-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-
owner@xxxxxxxxxxxxxx] On Behalf Of D. Dante Lorenso
Sent: Monday, February 26, 2007 2:20 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: [GENERAL] Most efficient report of number of records in all
tables?
All,
I can find the names of all tables in the database with this query:
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_name ASC;
Then, in code, I can loop through all the table names and run the
following query:
SELECT COUNT(*) AS result
FROM $table;
But, this can be slow when I have a large number of tables of some
tables have several million rows.
Is there a faster way to get this data using table statistics or
something like that? Perhaps something in a single query?
-- Dante
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
