X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 4770F9DCC19 for ; Thu, 16 Mar 2006 11:56:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35963-03 for ; Thu, 16 Mar 2006 11:57:03 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 2A0E29DCC64 for ; Thu, 16 Mar 2006 11:56:55 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k2GFv1fA017044; Thu, 16 Mar 2006 10:57:01 -0500 (EST) To: Simon Riggs cc: Kevin Grittner , pgsql-performance@postgresql.org, Andreas Kretschmer Subject: Re: BETWEEN optimizer problems with single-value In-reply-to: <1142510032.3859.320.camel@localhost.localdomain> References: <44180105.EE98.0025.0@wicourts.gov> <20060315181735.GA22240@KanotixBox> <44180D33.EE98.0025.0@wicourts.gov> <28798.1142450270@sss.pgh.pa.us> <1142467626.3859.207.camel@localhost.localdomain> <1142468678.3859.221.camel@localhost.localdomain> <11428.1142474724@sss.pgh.pa.us> <1142510032.3859.320.camel@localhost.localdomain> Comments: In-reply-to Simon Riggs message dated "Thu, 16 Mar 2006 11:53:52 +0000" Date: Thu, 16 Mar 2006 10:57:01 -0500 Message-ID: <17043.1142524621@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.113 required=5 tests=[AWL=0.113] X-Spam-Score: 0.113 X-Spam-Level: X-Archive-Number: 200603/241 X-Sequence-Number: 17678 Simon Riggs writes: > Trying to get the information in the wrong place would be very > expensive, I agree. But preparing that information when we have access > to it and passing it through the plan would be much cheaper. Where would that be? > The operator and the opclass are only connected via an index access > method, but for a particular index each column has only one opclass. If you're proposing making clauselist_selectivity depend on what indexes exist, I think that's very much the wrong approach. In the first place, it still has to give usable answers for unindexed columns, and in the second place there might be multiple indexes with different opclasses for the same column, so the ambiguity problem still exists. I have been wondering if we shouldn't add some more indexes on pg_amop or something to make it easier to do this sort of lookup --- we definitely seem to be finding multiple reasons to want to look up which opclasses contain a given operator. regards, tom lane