From: | "Dann Corbit" <DCorbit(at)connx(dot)com> |
---|---|
To: | <dante(at)lorenso(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Most efficient report of number of records in all tables? |
Date: | 2007-02-26 23:01:04 |
Message-ID: | D425483C2C5C9F49B5B7A41F89441547010004FA@postal.corporate.connx.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of D. Dante Lorenso
> Sent: Monday, February 26, 2007 2:20 PM
> To: pgsql-general(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | George Pavlov | 2007-02-26 23:13:18 | preventing ALTER TABLE RENAME from changing view definitions? |
Previous Message | D. Dante Lorenso | 2007-02-26 22:20:07 | Most efficient report of number of records in all tables? |