X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F225D563F2 for ; Wed, 9 Mar 2005 05:04:39 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 37721-08 for ; Wed, 9 Mar 2005 05:04:30 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id C95725551D for ; Wed, 9 Mar 2005 05:04:29 +0000 (GMT) Received: (qmail 3826 invoked by uid 500); 9 Mar 2005 05:20:20 -0000 Date: Tue, 8 Mar 2005 23:20:20 -0600 From: Bruno Wolff III To: "Jim C. Nasby" Cc: Rick Schumeyer , pgsql-performance@postgresql.org Subject: Re: index scan on =, but not < ? Message-ID: <20050309052020.GB1569@wolff.to> Mail-Followup-To: "Jim C. Nasby" , Rick Schumeyer , pgsql-performance@postgresql.org References: <001f01c5240d$a920c8c0$0200a8c0@dell8200> <20050309043821.GA1569@wolff.to> <20050309045519.GA509@decibel.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050309045519.GA509@decibel.org> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/159 X-Sequence-Number: 10864 On Tue, Mar 08, 2005 at 22:55:19 -0600, "Jim C. Nasby" wrote: > On Tue, Mar 08, 2005 at 10:38:21PM -0600, Bruno Wolff III wrote: > > Not exactly. If the number of rows to be examined is on the order of 5% > > of the table, an index scan will probably be slower than a sequential > > scan. The visibility issue makes index scans slower in the case that > > Shouldn't that be 50%? No. When you are doing an index scan of a significant part of the table, you will fetch some heap pages more than once. You will also be fetching blocks out of order, so you will lose out on read ahead optimization by the OS. This assumes that you don't get a lot of cache hits on the help pages. If a significant portion of the table is cached, then the trade off point will be at a higher percentage of the table.