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 E52DD3A4275 for ; Thu, 13 Jan 2005 08:58:34 +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 96317-06 for ; Thu, 13 Jan 2005 08:58:22 +0000 (GMT) Received: from mail.coretech.co.nz (coretech.co.nz [202.36.204.41]) by svr1.postgresql.org (Postfix) with ESMTP id DC4EB3A2585 for ; Thu, 13 Jan 2005 08:58:23 +0000 (GMT) Received: (qmail 21041 invoked from network); 13 Jan 2005 08:58:21 -0000 Received: from 218-101-12-250.paradise.net.nz (HELO ?192.168.1.11?) (218.101.12.250) by 0 with SMTP; 13 Jan 2005 08:58:21 -0000 Message-ID: <41E63917.2070906@coretech.co.nz> Date: Thu, 13 Jan 2005 22:02:15 +1300 From: Mark Kirkwood User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.7.2) Gecko/20041016 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: Litao Wu , pgsql-performance@postgresql.org Subject: Re: Postgres Optimizer is not smart enough? References: <20050112222506.32084.qmail@web13124.mail.yahoo.com> <41E5AF3F.2080100@coretech.co.nz> <4337.1105583973@sss.pgh.pa.us> In-Reply-To: <4337.1105583973@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/163 X-Sequence-Number: 9853 Tom Lane wrote: > Mark Kirkwood writes: > 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. > I see some small differences in the numbers - I am thinking that these are due to the calculations etc in cost_index(). e.g: create_index_paths : index oid 12616389 (test_id2) cost_index : cost=2.839112 (startup_cost=0.000000 run_cost=2.839112) : tuples=1.000000 cpu_per_tuple=0.017500 : selectivity=0.000002 : run_index_tot_cost=2.003500 run_io_cost=0.818112) create_index_paths : index oid 12616388 (test_id1) cost_index : cost=2.933462 (startup_cost=0.002500 run_cost=2.930962) : tuples=1.000000 cpu_per_tuple=0.010000 : selectivity=0.000002 : run_index_tot_cost=2.008500 run_io_cost=0.912462 Where: run_index_tot_cost=indexTotalCost - indexStartupCost; run_io_cost=max_IO_cost + csquared * (min_IO_cost - max_IO_cost) selectivity=indexSelectivity 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? cheers Mark