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 B52DB9DC844 for ; Wed, 15 Mar 2006 22:05:22 -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 30470-06 for ; Wed, 15 Mar 2006 22:05:26 -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 719BC9DC825 for ; Wed, 15 Mar 2006 22:05:19 -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 k2G25Oac011429; Wed, 15 Mar 2006 21:05:24 -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: <1142468678.3859.221.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> Comments: In-reply-to Simon Riggs message dated "Thu, 16 Mar 2006 00:24:38 +0000" Date: Wed, 15 Mar 2006 21:05:24 -0500 Message-ID: <11428.1142474724@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/229 X-Sequence-Number: 17666 Simon Riggs writes: >> ISTM that when the BETWEEN constants match we end up in this part of >> clauselist_selectivity()... Yeah, I think you are right. > so that the planner underestimates the cost of using "Cal_CalDate" so > that it ends up the same as "Cal_CtofcNo", and then we pick > "Cal_CalDate" because it was created first. No, it doesn't end up the same --- but the difference is small enough to be in the roundoff-error regime. The real issue here is that we're effectively assuming that one row will be fetched from the index in both cases, and this is clearly not the case for the Cal_CalDate index. So we need a more accurate estimate for the boundary case. > Using 1.0e-10 isn't very useful... the selectivity for a range should > never be less than the selectivity for an equality, so we should simply > put in a test against one of the pseudo constants and use that as the > minimal value. That's easier said than done, because you'd first have to find the appropriate equality operator to use (ie, one having semantics that agree with the inequality operators). Another point is that the above statement is simply wrong, consider calDate BETWEEN '2006-03-15' AND '2006-03-14' for which an estimate of zero really is correct. Possibly we could drop this code's reliance on seeing SCALARLTSEL/SCALARGTSEL as the estimators, and instead try to locate a common btree opclass for the operators --- which would then let us identify the right equality operator to use, and also let us distinguish > from >= etc. If we're trying to get the boundary cases right I suspect we have to account for that. I could see such an approach being tremendously slow though :-(, because we'd go looking for btree opclasses even for operators that have nothing to do with < or >. regards, tom lane