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 336DD3A2585 for ; Thu, 13 Jan 2005 15:24:25 +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 57452-04 for ; Thu, 13 Jan 2005 15:24:10 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9E8E23A517C for ; Thu, 13 Jan 2005 15:24:14 +0000 (GMT) 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 j0DFOCGb009157; Thu, 13 Jan 2005 10:24:12 -0500 (EST) To: Mark Kirkwood Cc: Litao Wu , pgsql-performance@postgresql.org Subject: Re: Postgres Optimizer is not smart enough? In-reply-to: <41E63917.2070906@coretech.co.nz> References: <20050112222506.32084.qmail@web13124.mail.yahoo.com> <41E5AF3F.2080100@coretech.co.nz> <4337.1105583973@sss.pgh.pa.us> <41E63917.2070906@coretech.co.nz> Comments: In-reply-to Mark Kirkwood message dated "Thu, 13 Jan 2005 22:02:15 +1300" Date: Thu, 13 Jan 2005 10:24:11 -0500 Message-ID: <9156.1105629851@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/170 X-Sequence-Number: 9860 Mark Kirkwood writes: > Hmmm ... so it's only the selectivity that is the same (sourced from > index->amcostestimate which I am guessing points to btcostestimate), is > that correct? No, the point is that btcostestimate will compute not only the same selectivities but the identical index access cost values, because it thinks that only one index entry will be fetched in both cases. It needs to account for the fact that the inequality condition will cause a scan over a larger range of the index than is actually returned. See _bt_preprocess_keys() and _bt_checkkeys(). The small differences you are showing have to do with different assumptions about where the now() function will get evaluated (once per row or once at scan start). That's not the effect that I'm worried about. regards, tom lane