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