Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 4F1F0B5DE98 for ; Thu, 1 Sep 2011 20:19:17 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 05199-03-2 for ; Thu, 1 Sep 2011 23:19:11 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.catalyst.net.nz (bertrand.catalyst.net.nz [202.78.240.40]) by mail.postgresql.org (Postfix) with ESMTP id 4F0F2B5DE87 for ; Thu, 1 Sep 2011 20:19:10 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mail.catalyst.net.nz (Postfix) with ESMTP id 1811F320D0; Fri, 2 Sep 2011 11:19:05 +1200 (NZST) X-Virus-Scanned: Debian amavisd-new at catalyst.net.nz Received: from mail.catalyst.net.nz ([127.0.0.1]) by localhost (bertrand.catalyst.net.nz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id hG9WEyox+ERO; Fri, 2 Sep 2011 11:19:04 +1200 (NZST) Received: from [IPv6:2404:130:0:1000:222:15ff:fe5c:2471] (unknown [IPv6:2404:130:0:1000:222:15ff:fe5c:2471]) (Authenticated sender: mark.kirkwood) by mail.catalyst.net.nz (Postfix) with ESMTPSA id 809E131B9C; Fri, 2 Sep 2011 11:19:04 +1200 (NZST) Message-ID: <4E6012E2.2000004@catalyst.net.nz> Date: Fri, 02 Sep 2011 11:18:58 +1200 From: Mark Kirkwood User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.20) Gecko/20110805 Lightning/1.0b2 Thunderbird/3.1.12 MIME-Version: 1.0 To: Tom Lane CC: PGSQL Performance Subject: Re: 8.4 optimization regression? References: <4E5331FF.3000900@catalyst.net.nz> <15708.1314155740@sss.pgh.pa.us> <4E5DCFA4.5090806@catalyst.net.nz> <5156.1314829311@sss.pgh.pa.us> <26940.1314918836@sss.pgh.pa.us> In-Reply-To: <26940.1314918836@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.404 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-0.504 X-Spam-Level: X-Archive-Number: 201109/4 X-Sequence-Number: 44780 On 02/09/11 11:13, Tom Lane wrote: > I wrote: >> Mark Kirkwood writes: >>> [ assorted examples showing that commit >>> 7f3eba30c9d622d1981b1368f2d79ba0999cdff2 has got problems ] >> ... >> So, not only are you correct that we should revert the changes to >> eqjoinsel_inner, but what's happening in eqjoinsel_semi is wrong too. > I've retested these examples with the patches I committed yesterday. > Six of the eight examples are estimated pretty nearly dead on, while the > other two are estimated about 50% too high (still a lot better than > before). AFAICT there's no easy way to improve those estimates further; > eqjoinsel_semi just plain hasn't got enough information to know how many > matches there will be. > > Just noticed your two commits this morning and ran them through the examples too - results look really good! Not only are the plain join queries looking way better but that last semi join that was way off is now being estimated pretty close. Should be interesting to see how much this improves more complex queries! Cheers Mark