X-Original-To: pgsql-bugs@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 22A35926109 for ; Sun, 1 Jun 2003 01:31:40 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 30692-09 for ; Sun, 1 Jun 2003 01:31:22 -0400 (EDT) Received: from wolff.to (wolff.to [66.93.249.74]) by developer.postgresql.org (Postfix) with SMTP id 7E3CF926104 for ; Sun, 1 Jun 2003 01:31:20 -0400 (EDT) Received: (qmail 26151 invoked by uid 500); 1 Jun 2003 05:33:39 -0000 Date: Sun, 1 Jun 2003 00:33:39 -0500 From: Bruno Wolff III To: Dave E Martin XXIII Cc: pgsql-bugs@postgresql.org Subject: Re: Index speeds up one row table (why)? Message-ID: <20030601053339.GB16259@wolff.to> Mail-Followup-To: Dave E Martin XXIII , pgsql-bugs@postgresql.org References: <3ED93338.80806@dave.to> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3ED93338.80806@dave.to> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200306/4 X-Sequence-Number: 6099 On Sat, May 31, 2003 at 16:56:56 -0600, Dave E Martin XXIII wrote: > > (ok, experimented a bit more just now) > Hm, it appears that degredation occurs with the index as well, I guess > at the time I created the index, it just initially did better because it > got to skip all the already dead rows at creation time: but this is > disturbing, I do a vacuum, and the access times are better, but still > horrible: You really don't want to use an index, so this probably doesn't matter for the current application. The problem is that when data is inserted into an index that just increases (or decreases) in value space from deleted entries doesn't get reused. I believe this is fixed in 7.4. This case would apply to indexes based on counters, dates or times where new values are added and old values get deleted.