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@postgresql.org [mailto:pgsql-general-
>> owner@postgresql.org] On Behalf Of D. Dante Lorenso
>> Sent: Monday, February 26, 2007 2:20 PM
>> To: pgsql-general@postgresql.org
>> 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
>>
>
>