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 786243A5EE6 for ; Thu, 13 Jan 2005 02:39:47 +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 17300-07 for ; Thu, 13 Jan 2005 02:39:33 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id D0F0F3A5EBF for ; Thu, 13 Jan 2005 02:39:36 +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 j0D2dX0E004338; Wed, 12 Jan 2005 21:39:33 -0500 (EST) To: Mark Kirkwood Cc: Litao Wu , pgsql-performance@postgresql.org Subject: Re: Postgres Optimizer is not smart enough? In-reply-to: <41E5AF3F.2080100@coretech.co.nz> References: <20050112222506.32084.qmail@web13124.mail.yahoo.com> <41E5AF3F.2080100@coretech.co.nz> Comments: In-reply-to Mark Kirkwood message dated "Thu, 13 Jan 2005 12:14:07 +1300" Date: Wed, 12 Jan 2005 21:39:33 -0500 Message-ID: <4337.1105583973@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/160 X-Sequence-Number: 9850 Mark Kirkwood writes: > I happen to have some debugging code enabled for the optimizer, and the > issue appears to be that the costs of paths using these indexes are > quite similar, so are quite sensitive to (some) parameter values. They'll be exactly the same, actually, as long as the thing predicts exactly one row retrieved. So it's quasi-random which plan you get. btcostestimate needs to be improved to understand that in multicolumn index searches with inequality conditions, we may have to scan through tuples that don't meet all the qualifications. It's not accounting for that cost at the moment, which is why the estimates are the same. regards, tom lane