Re: How often do I need to reindex tables?

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

--
Bill Moran
Collaborative Fusion Inc.