Every now and then I am in a need of a quick way to get an overview of the row count in a PostgreSQL database. So far I have been googling it up on demand. Each time I was impressed by the various approaches to get this information, and much depends on your case.
Usually my need is to get a rough number, it does not have to be accurate. The following snippet has proven to be useful for this case:
SELECT relname, reltuples FROM pg_class r JOIN pg_namespace n ON (relnamespace = n.oid) WHERE relkind = 'r' AND n.nspname = 'public' ORDER BY reltuples DESC;
If you happen to use multiple schema names, just tweak the part around n.nspname according to your needs, e.g.:
SELECT relname, reltuples FROM pg_class r JOIN pg_namespace n ON (relnamespace = n.oid) WHERE relkind = 'r' AND (n.nspname = 'public' OR n.nspname = 'yourothername') ORDER BY reltuples DESC;
Good background information can be found in the following places:
"Postgresql General Bits"
Commentscomments powered by Disqus