The size of base tables and their indexes

Copy SQL

WITH sizes AS (SELECT 
nspname AS table_schema,
relname AS table_name, 
Round(pg_table_size(c.oid)::numeric/1024/1024,3) AS table_size_MB, 
Round(pg_indexes_size(c.oid)::numeric/1024/1024,3) AS index_size_MB,
Round(pg_table_size(c.oid)::numeric/1024/1024,3)+Round(pg_indexes_size(c.oid)::numeric/1024/1024,3) AS total_size_MB
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND relkind='r')
SELECT table_schema, 
table_name, 
table_size_MB, 
index_size_MB, 
total_size_MB,
Round(index_size_MB*100/CASE WHEN total_size_MB=0 THEN NULL ELSE total_size_MB END,1) AS index_size_percentage
FROM sizes
ORDER BY index_size_percentage DESC, table_schema, table_name;