From: Tom Lane <tgl@sss.pgh.pa.us>
To: Artem Tomyuk <admin@leboutique.com>
Cc: depesz@depesz.com
Cc: pgsql-admin@postgresql.org
Subject: Re: Finding the size of your biggest relations
Date: Fri, 23 Sep 2016 10:30:52 -0400
Message-ID: <13514.1474641052@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, admin@leboutique.com, depesz@depesz.com
Subject: Re: Finding the size of your biggest relations
In-Reply-To: <13514.1474641052@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox