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 51047D1B455 for ; Wed, 28 Apr 2004 03:35:29 -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 03735-04 for ; Wed, 28 Apr 2004 03:35:26 -0300 (ADT) Received: from gpdnet.co.uk (gpdnet.plus.com [212.56.100.243]) by svr1.postgresql.org (Postfix) with ESMTP id 6B51AD1B454 for ; Wed, 28 Apr 2004 03:35:24 -0300 (ADT) Received: from gary (unverified [192.168.1.2]) by gpdnet.co.uk (SurgeMail 1.8g3) with ESMTP id 72 for ; Wed, 28 Apr 2004 07:34:44 +0100 From: "Gary Doades" To: pgsql-performance@postgresql.org Date: Wed, 28 Apr 2004 07:35:41 +0100 MIME-Version: 1.0 Subject: Re: planner/optimizer question Message-ID: <408F5ECD.29849.CA91084@localhost> In-reply-to: <23032.1083126479@sss.pgh.pa.us> References: <20040426171103.45600.qmail@uno.mnl.com> X-mailer: Pegasus Mail for Windows (v4.12a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Server: High Performance Mail Server - http://surgemail.com 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/473 X-Sequence-Number: 6773 I know you will shoot me down, but... Why is there an entry in the index for a row if the row is not valid? Wouldn't it be better for the index entry validity to track the row validity. If a particular data value for a query (join, where etc.) can be satisfied by the index entry itself this would be a big performance gain. Cheers, Gary. On 28 Apr 2004 at 0:27, Tom Lane wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend