X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 76797D1B8B6 for ; Wed, 28 Apr 2004 01:28:18 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 65937-01 for ; Wed, 28 Apr 2004 01:28:17 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 43616D1B582 for ; Wed, 28 Apr 2004 01:28:16 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i3S4RxpG023033; Wed, 28 Apr 2004 00:28:00 -0400 (EDT) To: brad-pgperf@duttonbros.com Cc: pgsql-performance@postgresql.org Subject: Re: planner/optimizer question In-reply-to: <20040426171103.45600.qmail@uno.mnl.com> References: <20040426171103.45600.qmail@uno.mnl.com> Comments: In-reply-to brad-pgperf@duttonbros.com message dated "Mon, 26 Apr 2004 10:11:03 -0700" Date: Wed, 28 Apr 2004 00:27:59 -0400 Message-ID: <23032.1083126479@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/472 X-Sequence-Number: 6772 brad-pgperf@duttonbros.com writes: > ... Wouldn't the most efficient plan be to scan the index regardless > of crm_id because the only columns needed are in the index? No. People coming from other databases often have the misconception that queries can be answered by looking only at an index. That is never true in Postgres because row validity info is only stored in the table; so we must always visit the table entry to make sure the row is still valid/visible for the current query. Accordingly, columns added to the index that aren't constrained by the WHERE clause are not very useful ... regards, tom lane