Re: Finding bloated indexes?

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.