Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA53489 for ; Sat, 20 Nov 1999 03:04:24 -0500 (EST) (envelope-from oleg@sai.msu.su) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.9.1/8.9.1) with SMTP id KAA25896; Sat, 20 Nov 1999 10:57:17 +0300 (MSK) Date: Sat, 20 Nov 1999 10:57:17 +0300 (MSK) From: Oleg Bartunov X-Sender: megera@ra To: Tom Lane cc: Matthew Hagerty , pgsql-sql@postgreSQL.org Subject: Re: [SQL] Deleting indexes before vacuum? In-Reply-To: <6453.943078465@sss.pgh.pa.us> Message-ID: Organization: Sternberg Astronomical Institute (Moscow University) MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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 > To: Matthew Hagerty > Cc: pgsql-sql@postgreSQL.org > Subject: Re: [SQL] Deleting indexes before vacuum? > > Matthew Hagerty 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