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

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).

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.
2.  The number can be invalid immediately after the query and might be
different for different users anyway.

What are you doing with those numbers?

> -----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