Re: [GENERAL] Disk space usage analyzer?
> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Steve Crawford > Sent: Monday, March 28, 2011 12:22 PM > To: Yang Zhang > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Disk space usage analyzer? > > On 03/25/2011 07:58 PM, Yang Zhang wrote: > > Is there any tool for breaking down how much disk space is used by > > (could be freed by removing) various tables, indexes, selected rows, > > etc.? Thanks! > > > > You can use the pg_class table and the pg_relation_size (and optionally > the pg_size_pretty) functions to get that info. This query gives table > sizes and percent of overall usage. > > BUT! It is only looking at tables, not indexes. If you want to know how > much space will be freed by dropping a table, you will have to modify > this query to total up the index space used for all the indexes > associated with each table. pg_total_relation_size() will give you the size of the table and the indexes on it. Brad. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disk space usage analyzer?
On 03/25/2011 07:58 PM, Yang Zhang wrote: Is there any tool for breaking down how much disk space is used by (could be freed by removing) various tables, indexes, selected rows, etc.? Thanks! You can use the pg_class table and the pg_relation_size (and optionally the pg_size_pretty) functions to get that info. This query gives table sizes and percent of overall usage. BUT! It is only looking at tables, not indexes. If you want to know how much space will be freed by dropping a table, you will have to modify this query to total up the index space used for all the indexes associated with each table. select relname as table, lpad(pg_size_pretty(pg_relation_size(oid)), 9) as size, (100*pg_relation_size(oid)/(select sum(pg_relation_size(oid)) from pg_class where relkind='r'))::numeric(4,1) as percent from pg_class where relkind = 'r' order by relpages desc ; table | size| percent --+---+- foobar | 2310 MB |19.7 foobaz | 2021 MB |17.2 bazbar | 1642 MB |14.0 ... Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Disk space usage analyzer?
Is there any tool for breaking down how much disk space is used by (could be freed by removing) various tables, indexes, selected rows, etc.? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
