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.
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
