Skip site navigation (1) Skip section navigation (2)

Re: Most efficient report of number of records in all tables?

From: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
To: Dann Corbit <DCorbit(at)connx(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Most efficient report of number of records in all tables?
Date: 2007-02-26 23:15:10
Message-ID: 45E369FE.5050701@lorenso.com (view raw or flat)
Thread:
Lists: pgsql-general
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(at)postgresql(dot)org [mailto:pgsql-general-
>> owner(at)postgresql(dot)org] On Behalf Of D. Dante Lorenso
>> Sent: Monday, February 26, 2007 2:20 PM
>> To: pgsql-general(at)postgresql(dot)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
>>     
>
>   



In response to

Responses

pgsql-general by date

Next:From: Richard HuxtonDate: 2007-02-26 23:56:02
Subject: Re: preventing ALTER TABLE RENAME from changing view definitions?
Previous:From: George PavlovDate: 2007-02-26 23:13:18
Subject: preventing ALTER TABLE RENAME from changing view definitions?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group