From: Oleg Bartunov <oleg@sai.msu.su>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Matthew Hagerty <matthew@venux.net>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Deleting indexes before vacuum?
Date: Sat, 20 Nov 1999 10:57:17 +0300 (MSK)
Message-ID: <Pine.GSO.3.96.SK.991120105211.3910w-100000@ra> (raw)
In-Reply-To: <6453.943078465@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: oleg@sai.msu.su, tgl@sss.pgh.pa.us, matthew@venux.net
Subject: Re: [SQL] Deleting indexes before vacuum?
In-Reply-To: <Pine.GSO.3.96.SK.991120105211.3910w-100000@ra>
References: <6453.943078465@sss.pgh.pa.us> <Pine.GSO.3.96.SK.991120105211.3910w-100000@ra>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox