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

D. Dante Lorenso wrote:
> 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?

The same, only that you'd have to monitor autovac activity to know for
sure how far back it is :-)

However, note that the optimizer uses the following trick to guesstimate
the number of tuples on any given table: first, it asks the kernel for
the number of blocks in the files corresponding to the table. Then, it
uses the pg_class.reltuples and relpages values to estimate a "tuple
density" (tuples per page), then multiplies by the number of blocks. As
far as estimates go, this one is pretty good.

The only thing I'm not sure how to get from SQL, is the actual number of
blocks. You could trivially build a C function to get it. In fact,
contrib/pgstatindex in CVS head (not sure if it's in 8.2) contains such
a function which you could borrow, pg_relpages().

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.