Re: [SQL] Deleting indexes before vacuum? - Mailing list pgsql-sql

I see another reason for index/vacuum/create
Currently index files grows infinitely because vacuum analyze
doesn't truncates them. Vadim has implemented in 6.5.3 a quick hack for
index reuse, but indices still grow. This cause a visible performance
degradation if your table is often updated.
The question is: what's the right way to do index/vacuum/create ?
Do I need transaction ?
Regards,    Oleg

On Sat, 20 Nov 1999, Tom Lane wrote:

> Date: Sat, 20 Nov 1999 01:14:25 -0500
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Matthew Hagerty <matthew@venux.net>
> Cc: pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] Deleting indexes before vacuum? 
> 
> Matthew Hagerty <matthew@venux.net> writes:
> > I read a post sometime back where someone said their normal routine for
> > running a vacuum was to delete the indexes first, then recreate them after
> > the vacuum.  Is this necessary?  If the indexes are gone does vacuum
> > analyze still make sense?
> 
> Sure.  Vacuum analyze is mostly about deleting dead tuples, reclaiming
> the space they occupied, and computing statistics about column values.
> These activities are useful whether there are indexes or not.  If there
> are indexes, vacuum also cleans up useless entries in the indexes (ie,
> pointers to dead tuples).
> 
> The reason for the drop index/vacuum/create index raindance is that
> vacuum's method of cleaning up indexes seems to be horrendously
> inefficient.  It actually takes less time to rebuild an index on a
> large table *from scratch* than to let vacuum fix up the index.
> 
> This is, of course, pretty bogus.  I personally have no idea *why*
> vacuum's index-handling code is so slow --- I've been griping about it
> freely for a year or more, but have not had time to look into causes or
> solutions.  Perhaps someone else has a better idea of what's going on
> here (...paging Vadim...)
> 
> Anyway, the drop/vacuum/recreate business is nothing more nor less than
> a quick-hack workaround for a performance deficiency in the current
> implementation of vacuum.  That deficiency should go away eventually,
> but in the meantime, if vacuum takes too long on your large tables,
> you might give it a try.
> 
> > I am concerned about this because I never removed the indexes prior to
> > vacuum, but today I executed this simple query:
> > select note_id from appnotes where note_id=6068;
> > note_id
> > -------
> >   17768
> > (1 row)
> > This was rather alarming, so I deleted all the indexes, ran vacuum,
> > recreated the indexes.  Now the query works:
> 
> Ugh.  Definitely a busted index.  Vacuum is *not* designed to recover
> from corrupted-data situations.  Dropping/rebuilding indexes will
> recover from index corruption problems (with or without a vacuum), so
> long as the underlying table is OK.  That seems to be what you saw here.
> 
> I have no words of wisdom about what might have caused the index
> corruption --- if you can find a sequence that reproduces it, please
> file a bug report!
> 
> > I am running pg-6.4 (I know it needs an update) on this particular server
> > and 6.5.x on others.
> 
> 6.5 is more stable than 6.4 AFAIK.  It's still got bugs of course...
> 
>             regards, tom lane
> 
> ************
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83