From: Tom Lane <tgl@sss.pgh.pa.us>
To: Artem Tomyuk <admin@leboutique.com>
cc: depesz@depesz.com, pgsql-admin@postgresql.org
Subject: Re: Finding the size of your biggest relations
In-reply-to: 
 <CANYYVqKF83n76cD3+SGZkOBWVOytMmVun72a_vm_euYw1CdbjA@mail.gmail.com>
References: 
 <CANYYVqJrOSYCaryawYt95f-hQR6FHjMAk7CZ6eycb5i=GSu-Zg@mail.gmail.com>
 <20160923132122.jcpbobqzf3x4emda@depesz.com>
 <CANYYVqKF83n76cD3+SGZkOBWVOytMmVun72a_vm_euYw1CdbjA@mail.gmail.com>
Comments: In-reply-to Artem Tomyuk <admin@leboutique.com>
	message dated "Fri, 23 Sep 2016 16:31:39 +0300"
Date: Fri, 23 Sep 2016 10:30:52 -0400
Message-ID: <13514.1474641052@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Artem Tomyuk <admin@leboutique.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


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin