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: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
