Обсуждение: REINDEX requirement?

Поиск
Список
Период
Сортировка

REINDEX requirement?

От
AI Rumman
Дата:
How do I know that index require REINDEX?

Re: REINDEX requirement?

От
Josh Kupershmidt
Дата:
On Tue, Nov 9, 2010 at 4:26 AM, AI Rumman <rummandba@gmail.com> wrote:
> How do I know that index require REINDEX?

Well, the REINDEX page:
  <http://www.postgresql.org/docs/current/static/sql-reindex.html>

gives a few examples of why you might need to reindex. I think the
most common reason would probably be due to index bloat. See
  <http://wiki.postgresql.org/wiki/Index_Maintenance>

under "Index Bloat" for links to some handy queries to see whether
your tables and/or indexes suffer bloat.

Josh

Re: REINDEX requirement?

От
"Igor Neyman"
Дата:

> -----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.

Regards,
Igor Neyman

Re: REINDEX requirement?

От
Robert Treat
Дата:
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.


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

Re: REINDEX requirement?

От
"Marc Mamin"
Дата:

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.


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