Gok Demir Gok Demir - 2 months ago 10
SQL Question

Getting tables with no rows without counting

I got a huge PostgreSQL database with lots of tables. I want learn all empty tables without counting each tables for performance reasons (Some of the tables have several millions rows).

jmz jmz
Answer

This query will give you an approximate result, but does not include counting table rows.

SELECT relname FROM pg_class JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid) WHERE relpages = 0 AND pg_namespace.nspname = 'public';

This will work best after a VACUUM ANALYZE.

Comments