Re: How often do I need to reindex tables? - Mailing list pgsql-general

In response to "Joshua D. Drake" <jd@commandprompt.com>:

> Bill Moran wrote:
> > In response to "Ezequias Rodrigues da Rocha" <ezequias.rocha@gmail.com>:
> >> 2007/2/27, Jim C. Nasby <jim@nasby.net>:
> >>> On Tue, Feb 27, 2007 at 11:26:02AM -0800, Dhaval Shah wrote:
> >>>> I am planning to use 8.2 and the average inserts/deletes and updates
> >>>> across all tables is moderate. That is, it is a moderate sized
> >>>> database with moderate usage of tables.
> >>>>
> >>>> Given that, how often do I need to reindex the tables? Do I need to do
> >>>> it everyday?
> >>> No, you should very rarely if ever need to do it.
> >
> > I don't agree.  I think that regular indexing is mandatory under some
> > workloads.
>
> Bill, you are right but I believe Jim was speaking from a general
> perspective. Generally speaking you should not have to reindex, or if
> you do very rarely.
>
> I too have a couple of databases we manage that require a reindex more
> often than what would be considered normal, but a reindex is far from
> the norm itself.

Well, I hope I didn't come across as confrontation or anything, as that
wasn't my intent.  The only point I was trying to make is that the need to
reindex probably shouldn't be written off lightly until one has monitored
the indexes for a spell to see if they need it or not.


>  Example:
> > bacula=# select relname, relpages from pg_class where relkind='i' and relname not like 'pg_%' order by relname;
> >             relname            | relpages
> > -------------------------------+----------
> >  basefiles_pkey                |        1
> >  cdimages_pkey                 |        1
> >  client_name_idx               |        2
> >  client_pkey                   |        2
> >  counters_pkey                 |        1
> >  device_pkey                   |        1
> >  file_fp_idx                   |    41212
> > [...]
> >
> > bacula=# reindex database bacula;
> > [...]
> >
> >             relname            | relpages
> > -------------------------------+----------
> >  basefiles_pkey                |        1
> >  cdimages_pkey                 |        1
> >  client_name_idx               |        2
> >  client_pkey                   |        2
> >  counters_pkey                 |        1
> >  device_pkey                   |        1
> >  file_fp_idx                   |    21367
> > [...]
> >
> > There are some additional indexes that I've snipped from the output that also
> > saw some benefit from reindexing, but let's just focus on file_fp_idx.
> >
> > Please note that the database you're looking at is reindexed _weekly_ by a
> > cron job, which means the index bloat you're seeing in the above example is
> > the result of normal activity since last Saturday.
> >
> > I've brought this up before, and I want to point it out again.  I really
> > think there are certain workloads that require reindexing.  Luckily for
> > this particular workload, it's easy to schedule a job to do so, since I
> > know when the backups aren't running :)
> >
>
>
> --
>
>       === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
> Providing the most comprehensive  PostgreSQL solutions since 1997
>              http://www.commandprompt.com/
>
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
> PostgreSQL Replication: http://www.commandprompt.com/products/
>
>
>
>
>
>
>


--
Bill Moran
Collaborative Fusion Inc.

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************