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

Most efficient report of number of records in all tables?

From: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Most efficient report of number of records in all tables?
Date: 2007-02-26 22:20:07
Message-ID: 45E35D17.3040205@lorenso.com (view raw or flat)
Thread:
Lists: pgsql-general
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




Responses

pgsql-general by date

Next:From: Dann CorbitDate: 2007-02-26 23:01:04
Subject: Re: Most efficient report of number of records in all tables?
Previous:From: Tony CadutoDate: 2007-02-26 22:12:55
Subject: grant on sequence and pg_restore/pg_dump problem

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