I’ve been fighting some database performance issues recently and started to use the following query to look for tables that are being used that are showing that they have 0 rows. These are likely to be unanalyzed tables being used in queries. We have queries to look through the entire database for potential unanalyzed tables, but it takes much less resources just to look at what’s currently in flight and try to address what we are currently hammering on. There are a lot of other table in there that I don’t need to join on for the visible data set, but I’ve got them there in case I need to start ungrouping things and pulling in more specific data.
SELECT n.nspname AS "schema_name",
c.relname AS "object_name",
c.reltuples,
c.relpages
FROM pg_locks l,
pg_class c,
pg_database d,
pg_stat_activity s,
pg_namespace n
WHERE l.relation = c.oid
AND l.database = d.oid
AND l.pid = s.procpid
AND c.relnamespace = n.oid
AND c.relkind = 'r'
AND n.nspname !~ '^pg_'::text
AND c.reltuples = 0
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2;