X-Original-To: pgsql-performance@postgresql.org Received: from bob.samurai.com (bob.samurai.com [205.207.28.75]) by postgresql.org (Postfix) with ESMTP id 95CF5475FD9 for ; Mon, 17 Mar 2003 01:18:59 -0500 (EST) Received: from samurai.com (localhost [127.0.0.1]) by bob.samurai.com (Postfix) with SMTP id 4DBFA1D60; Mon, 17 Mar 2003 01:18:59 -0500 (EST) Received: from du150.n224.resnet.queensu.ca ([130.15.224.150]) (SquirrelMail authenticated user neilc) by mailbox.samurai.com with HTTP; Mon, 17 Mar 2003 01:18:59 -0500 (EST) Message-ID: <33137.130.15.224.150.1047881939.squirrel@mailbox.samurai.com> Date: Mon, 17 Mar 2003 01:18:59 -0500 (EST) Subject: Re: postgresql meltdown on PlanetMath.org From: "Neil Conway" To: In-Reply-To: <20030317061011.GH23355@perrin.int.nxad.com> References: <20030316060125.GD19570@vt.edu> <3E741B93.1090203@joeconway.com> <20030316075206.GE19570@vt.edu> <20030317061011.GH23355@perrin.int.nxad.com> X-Priority: 3 Importance: Normal Cc: , X-Mailer: SquirrelMail (version 1.2.11) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Archive-Number: 200303/113 X-Sequence-Number: 1411 Sean Chittenden said: >> A "select count(*) FROM tblwordidx" initially took about 1 second to >> return a count of 2 million but after a few months it took several >> minutes of really hard HDD grinding. > > That's because there are dead entries in the index that weren't being > reused or cleaned up. As I said, this has been fixed. That's doubtful: "select count(*) FROM foo" won't use an index. There are a bunch of other factors (e.g. dead heap tuples, changes in the pages cached in the buffer, disk fragmentation, etc.) that could effect performance in that situation, however. Cheers, Neil