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-owner@postgresql.org [mailto:pgsql-general-owner@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 <ineyman@perceptron.com> wrote:
> -----Original Message-----
> From: AI Rumman [mailto:rummandba@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.
