X-Original-To: pgsql-performance@postgresql.org Received: from perrin.int.nxad.com (unknown [69.1.70.251]) by postgresql.org (Postfix) with ESMTP id 6510D474E53 for ; Mon, 17 Mar 2003 01:29:30 -0500 (EST) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id 425E62106B; Sun, 16 Mar 2003 22:29:29 -0800 (PST) Date: Sun, 16 Mar 2003 22:29:29 -0800 From: Sean Chittenden To: Neil Conway Cc: logan@datacurrent.com, pgsql-performance@postgresql.org Subject: Re: postgresql meltdown on PlanetMath.org Message-ID: <20030317062929.GI23355@perrin.int.nxad.com> References: <20030316060125.GD19570@vt.edu> <3E741B93.1090203@joeconway.com> <20030316075206.GE19570@vt.edu> <20030317061011.GH23355@perrin.int.nxad.com> <33137.130.15.224.150.1047881939.squirrel@mailbox.samurai.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <33137.130.15.224.150.1047881939.squirrel@mailbox.samurai.com> User-Agent: Mutt/1.4i X-PGP-Key: finger seanc@FreeBSD.org X-PGP-Fingerprint: 3849 3760 1AFE 7B17 11A0 83A6 DD99 E31F BC84 B341 X-Web-Homepage: http://sean.chittenden.org/ X-Archive-Number: 200303/114 X-Sequence-Number: 1412 > >> 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. *blush* Yeah, jumped the gun on that when I read that queries were getting slower (churn of an index == slow creaping death for performance). A SELECT COUNT(*), however, wouldn't be affected by the index growth problem. Is the COUNT() on a view that uses an index? I haven't had any real problems with this kind of degredation outside of indexes. :-/ -sc -- Sean Chittenden