Re: [SQL] Deleting indexes before vacuum?

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(at)sss(dot)pgh(dot)pa(dot)us>
> To: Matthew Hagerty <matthew(at)venux(dot)net>
> Cc: pgsql-sql(at)postgreSQL(dot)org
> Subject: Re: [SQL] Deleting indexes before vacuum?
>
> Matthew Hagerty <matthew(at)venux(dot)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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83