Re: [PERFORM] Finding bloated indexes?
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] 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. smime.p7s Description: S/MIME cryptographic signature
