Hello,
When reindexing we keep the previous and new reltuples/relpages ratio in in a reference table in order to track the reindex efficiency. We also have a maintenance jobs that compare this table with the stats from pg_class and automatically reindex the relations where the ratio degraded too much. (There is also a list of relations that need to be ignored by the job.) This is a simplistic approach , but it matches our needs. CREATE TABLE reindex_list ( nspname character varying, index_name character varying, nspname_oid oid NOT NULL, index_oid oid NOT NULL, old_ratio double precision, new_ratio double precision, old_pagecount integer, new_pagecount integer, execution_count integer, reindex_time bigint, CONSTRAINT reindex_list_pk PRIMARY KEY (nspname_oid, index_oid) ) regards, Marc Mamin From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Robert Treat Sent: Mittwoch, 10. November 2010 22:40 To: Igor Neyman Cc: AI Rumman; pgsql-general General Subject: Re: [GENERAL] REINDEX requirement? On Tue, Nov 9, 2010 at 1:51 PM, Igor Neyman <iney...@perceptron.com> wrote: > -----Original Message----- > From: AI Rumman [mailto:rumman...@gmail.com] > Sent: Tuesday, November 09, 2010 3:26 AM > To: pgsql-general General > Subject: REINDEX requirement? > > How do I know that index require REINDEX? > > Look at the results of pgstatindex(...) function for specific index. It's part of pgstattupple contrib module - read it up in the docs. If you are looking for a "poor mans" tool, we have a script that will output numbers on table/index bloat. It's not entirely accurate (patches welcome), but usually good enough to highlight the problems. See http://labs.omniti.com/labs/pgtreats/log/trunk/tools/pg_bloat_report.pl Robert Treat play: http://www.xzilla.net work: http://www.omniti.com/is/hiring
