Re: REINDEX requirement?

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

On Tue, Nov 9, 2010 at 1:51 PM, Igor Neyman <ineyman@perceptron.com> wrote: