On 11/7/12 3:58 PM, Jeff Janes wrote:
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
I question the wisdom of that where clause (from the wiki)
If the pg_catalog relations are big, then they are big and why
shouldn't they get reported as such? And if they are not big, that is
why the limit is there on the wiki page.
The idea was that in a new database with a relatively small number of
tables, your own tables will be lost among the catalog data unless you
filter them out. Testing against an install with a single real table,
the query there will show something like this right now:
relation | total_size
----------------+------------
public.t | 3568 kB
public.t_k_seq | 8192 bytes
But if the filter on pg_catalog is removed, you get this instead:
relation | total_size
-----------------------------+------------
public.t | 3568 kB
pg_catalog.pg_depend | 808 kB
pg_catalog.pg_proc | 752 kB
pg_catalog.pg_attribute | 568 kB
pg_catalog.pg_rewrite | 464 kB
pg_catalog.pg_description | 392 kB
pg_catalog.pg_statistic | 328 kB
pg_catalog.pg_operator | 208 kB
pg_catalog.pg_collation | 152 kB
pg_catalog.pg_type | 152 kB
pg_catalog.pg_amop | 136 kB
pg_catalog.pg_class | 136 kB
pg_catalog.pg_constraint | 112 kB
pg_catalog.pg_conversion | 104 kB
pg_catalog.pg_index | 88 kB
pg_catalog.pg_amproc | 80 kB
pg_catalog.pg_opclass | 80 kB
pg_catalog.pg_ts_config_map | 80 kB
pg_catalog.pg_cast | 80 kB
pg_catalog.pg_authid | 72 kB
That is overload for a lot of people, and confusing to new users.
That's why I opted for the shorter version.
There's no perfect answer to all use cases here. This sort of thing is
why there's three sets of queries for pg_stat_user_tables,
pg_stat_sys_tables, and pg_stat_all_tables. The wiki disk space queries
aim to be like the user tables version from that trio.
Adding a note pointing out that you might want to remove pg_catalog and
see the size of those relations would be appropriate. I wouldn't make
that the default case though, due to the issue highlighted above. I'd
rather optimize the initially suggested query so that new users get
simple output, even if it means that might hide problems on larger
installs, where the catalog data became big.
The other way I sometimes balance these two requirements--want to show
all the big data, but not clutter small installs with the catalog--is to
make the filter size-based instead:
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
AND relpages > 100
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
On my trivial test install that gives me just the one user table:
relation | total_size
----------+------------
public.t | 3568 kB
While still showing larger catalog tables if they grow to be noticeable.
--
Greg Smith 2ndQuadrant US greg@xxxxxxxxxxxxxxx Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
