select schemaname as schema_owner,
relname as table_name,
pg_size_pretty(pg_total_relation_size(relid)) as total_size,
pg_size_pretty(pg_relation_size(relid)) as used_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))
as free_space
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc,
pg_relation_size(relid) desc
limit 10;
(or)
SELECT
nspname as schema_name,relname as table_name,pg_size_pretty(pg_relation_size(c.oid)) as "table_size"
from pg_class c left join pg_namespace n on ( n.oid=c.relnamespace)
where nspname not in ('pg_catalog','information_schema')
order by pg_relation_size(c.oid) desc limit 10;