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 CDC92563F6 for ; Wed, 9 Mar 2005 04:22:41 +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 23599-07 for ; Wed, 9 Mar 2005 04:22:32 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 86D5656375 for ; Wed, 9 Mar 2005 04:22:30 +0000 (GMT) Received: (qmail 2678 invoked by uid 500); 9 Mar 2005 04:38:21 -0000 Date: Tue, 8 Mar 2005 22:38:21 -0600 From: Bruno Wolff III To: Rick Schumeyer Cc: pgsql-performance@postgresql.org Subject: Re: index scan on =, but not < ? Message-ID: <20050309043821.GA1569@wolff.to> Mail-Followup-To: Rick Schumeyer , pgsql-performance@postgresql.org References: <001f01c5240d$a920c8c0$0200a8c0@dell8200> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <001f01c5240d$a920c8c0$0200a8c0@dell8200> 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/154 X-Sequence-Number: 10859 On Tue, Mar 08, 2005 at 13:35:53 -0500, Rick Schumeyer wrote: > I have two index questions. The first is about an issue that has been > recently discussed, > > and I just wanted to be sure of my understanding. Functions like count(), > max(), etc. will > > use sequential scans instead of index scans because the index doesn't know > which rows > > are actually visible.is this correct? 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 the only columns of interest are in the index. Another issue is that max could in theory use an index, but there isn't a mechanism for Postgres to know how to do this in general for aggregates where it is possible. There have been discussions in the past about how this could be done, but no one has done it yet.