Обсуждение: Error in getting top 10 biggest table

-----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-----