Tuesday, July 24, 2012

List the Table / Object size in Postgresql Database.




Here is the query to find the largest Table in current database


SELECT u.schemaname || '.' ||c.relname AS relation, c.relpages, t.relpages AS toastpages, (c.relpages+coalesce(t.relpages,0))*8 as KB
FROM PG_CLASS c
LEFT OUTER JOIN PG_CLASS t ON c.reltoastrelid=t.oid
RIGHT OUTER JOIN PG_STAT_USER_TABLES u ON u.relid = c.oid
ORDER BY  4 DESC LIMIT 10;


Above query will result the TOP 10 Object, If you would like to view all the table just remove LIMIT keyword from the query.