Roughly counting rows in PostgreSQL

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;