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

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)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(at)perceptron(dot)com>
wrote:

> -----Original Message-----
> From: AI Rumman [mailto:rummandba(at)gmail(dot)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