In response to "Joshua D. Drake" <jd@xxxxxxxxxxxxxxxxx>: > Bill Moran wrote: > > In response to "Ezequias Rodrigues da Rocha" <ezequias.rocha@xxxxxxxxx>: > >> 2007/2/27, Jim C. Nasby <jim@xxxxxxxxx>: > >>> 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@xxxxxxxxxxxxxxxxxxxxxxx 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. ****************************************************************
