Tuesday, January 29, 2013

PostgreSQL - Determining Table and Index Size

This is somewhat of a follow up to my previous post. If you are interested in knowing the size of your postgres table and associated indexes, sequences, etc., you can run the following SQL to find out. Enjoy.

SELECT pg_size_pretty(pg_relation_size(pg_class.oid)), pg_class.relname, pg_namespace.nspname,
    
CASE pg_class.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 't' THEN 'TOAST'
    
ELSE pg_class.relkind::text END
    FROM
pg_class
            
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace)
    
ORDER BY pg_relation_size(pg_class.oid) DESC;

No comments:

Post a Comment