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

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

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
Cc: Dann Corbit <DCorbit(at)connx(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Most efficient report of number of records in all tables?
Date: 2007-02-27 00:27:05
Message-ID: 20070227002705.GU19104@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-general
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.

In response to

pgsql-general by date

Next:From: George NychisDate: 2007-02-27 00:42:57
Subject: dropping a master table and all of its partitions?
Previous:From: Richard HuxtonDate: 2007-02-26 23:56:02
Subject: Re: preventing ALTER TABLE RENAME from changing view definitions?

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