From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-admin@postgresql.org
Cc: ankurkaushik@gmail.com
Subject: Re: Error in getting top 10 biggest table
Date: Sat, 25 Jul 2015 12:15:32 -0000
Message-ID: <0456b5c27969a99ac03fbc22908b52e3@biglumber.com> (raw)
In-Reply-To: <CALXoLqz5JOMpAx34w+6QGUPx1th70-xDzpEnjEDuJWb5L09SfA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Ankur Kaushik asked:
> SELECT table_name,pg_relation_size(table_schema || '.' || table_name) as
> size FROM information_schema.tables WHERE table_schema NOT IN
> ('information_schema', 'pg_catalog') ORDER BY size DESC LIMIT 10;
> *ERROR: relation "public.contacts" does not exist*
The problem is that you must pass the exact name to the pg_relation_size
function. The information_schema.tables returns the non-canonical
lowercase version. One solution is to make sure that you wrap the
table_name column in the quote_ident function like so:
SELECT table_name,
pg_relation_size(table_schema || '.' || quote_ident(table_name))
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC LIMIT 10;
Another simpler way is to simply use the system catalogs directly, and
trust that both the information_schema and pg_catalog schemas are not
going to have large enough tables to affect your query. In which case,
you can also pass the OID to the pg_relation_size column. It's also nice
to throw in a pg_size_pretty column to make the output a little more
user-friendly:
SELECT relname, pg_relation_size(oid), pg_size_pretty(pg_relation_size(oid))
FROM pg_class
WHERE relkind = 'r'
ORDER BY pg_relation_size(oid) DESC LIMIT 10;
The relkind = 'r' is needed to limit it to only tables, but indexes are
usually quite large as well, so you sometimes want to show those as well.
Just add another column to show you the type (r=table,i=index):
SELECT relname, relkind, pg_relation_size(oid), pg_size_pretty(pg_relation_size(oid))
FROM pg_class
ORDER BY pg_relation_size(oid) DESC LIMIT 10;
Finally, make note of the pg_total_relation_size() function, which acts like
pg_relation_size but includes the indexes (slightly simplified: see
http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
for more details)
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201507250813
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAlWzfacACgkQvJuQZxSWSshPNQCgqOCbC7B7hNzqLu7N3DHXZ+o6
FEUAoLSMIj7yk3t3cQzO07iCcloLaymt
=x6lc
-----END PGP SIGNATURE-----
--
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: greg@turnstep.com, ankurkaushik@gmail.com
Subject: Re: Error in getting top 10 biggest table
In-Reply-To: <0456b5c27969a99ac03fbc22908b52e3@biglumber.com>
* 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