display_table_sizes.sql

SELECT 
    schemaname || '.' || tablename AS table_name,
    pg_size_pretty(pg_total_relation_size(format('%I.%I', schemaname, tablename))) AS total_size,
    pg_size_pretty(pg_relation_size(format('%I.%I', schemaname, tablename))) AS table_size,
    pg_size_pretty(pg_indexes_size(format('%I.%I', schemaname, tablename))) AS index_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(format('%I.%I', schemaname, tablename)) DESC;