Re: Finding the size of your biggest relations

Artem Tomyuk <admin(at)leboutique(dot)com> writes:
> Sorry,
> this query

> SELECT nspname || '.' || relname AS "relation",
> pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> WHERE nspname NOT IN ('pg_catalog', 'information_schema')
> AND C.relkind <> 'i'
> AND nspname !~ '^pg_toast'
> ORDER BY pg_total_relation_size(C.oid) DESC
> LIMIT 20;

I think what's happening is that your ORDER BY is sorting nulls first.
So what's coming out at the top is temp relations that were dropped
between the time of the statement snapshot and the time that
pg_total_relation_size looked at them. Try ORDER BY ... DESC NULLS LAST.

regards, tom lane