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.
