Thread: Finding bloated indexes?

Is there a pg_stat_* table or the like that will show how bloated an index is?
I am trying to squeeze some disk space and want to track down where the worst
offenders are before performing a global REINDEX on all tables, as the database
is rougly 400GB on disk and this takes a very long time to run.

I have been able to do this with tables, using a helpful view posted to this
list a few months back, but I'm not sure if I can get the same results on indexes.

Thanks

-Dan

On Apr 13, 2007, at 4:01 PM, Dan Harris wrote:

> Is there a pg_stat_* table or the like that will show how bloated
> an index is? I am trying to squeeze some disk space and want to
> track down where the worst offenders are before performing a global
> REINDEX on all tables, as the database is rougly 400GB on disk and
> this takes a very long time to run.

I find this as a helpful guide:

select relname,relkind,relpages from pg_class where relname like 'user
%';

for example (obviously change the LIKE clause to something useful to
you).

then with your knowledge of how big your rows are and how many
relpages the table itself takes, you can see if your index is too
big.  It helps to watch these numbers over time.

Also, running "analyze verbose" on the table gives you a hint at how
sparse the pages are, which might imply something for table bloat.
I'm not sure.

More expensive is "vacuum verbose" which gives lots of info on how
many "unused pointers" there are in your indexes.  This may be of
use.  If this is a high number compared to the number of row
versions, then you probably have bloat there.

On Fri, 2007-04-13 at 14:01 -0600, Dan Harris wrote:
> Is there a pg_stat_* table or the like that will show how bloated an index is?
> I am trying to squeeze some disk space and want to track down where the worst
> offenders are before performing a global REINDEX on all tables, as the database
> is rougly 400GB on disk and this takes a very long time to run.
>
> I have been able to do this with tables, using a helpful view posted to this
> list a few months back, but I'm not sure if I can get the same results on indexes.

Use pgstatindex in contrib/pgstattuple

--
  Simon Riggs
  EnterpriseDB   http://www.enterprisedb.com