Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0343B63329A for ; Wed, 10 Mar 2010 18:41:48 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 18462-07 for ; Wed, 10 Mar 2010 22:41:38 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f223.google.com (mail-fx0-f223.google.com [209.85.220.223]) by mail.postgresql.org (Postfix) with ESMTP id 1656B632C0F for ; Wed, 10 Mar 2010 18:41:37 -0400 (AST) Received: by fxm23 with SMTP id 23so7711874fxm.3 for ; Wed, 10 Mar 2010 14:41:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=n8n9t/1eMawdsmyUykUWlQOAi5fX82cwizHxzPWfyqI=; b=faAv/bvgg5Pyzpey8bRtx0/C8Ax8dvcULEpQ0QF7X2b9eCfQjGiqlsBk0rwGrEr0i2 QA729lcxjV2bVWc+7O7+cS8n1w5jPy6Ddls9bRCDNAA6JR2YuNgPPuzpYFF7HtNKxL/+ W4pQ5C/6NTp20VmNzLNm4O86HzbTfHGMaQv8o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=xdDcNntmoTAJ9npIhP5sgVAskcPvrKo5SwWGt/K49rIHTGDr37ki2PT9Jb98rIFlt2 66E4g4r0Oegkr5NYvZ9TwFHYYfBy5u6x6WJlgSFoBS8V0/usmnmJTrIhTV5pvx+AFxsP e6Y7hKHQzUICTbFMlEkgrTvqlkXhG3s9+hTko= MIME-Version: 1.0 Received: by 10.239.187.146 with SMTP id l18mr239408hbh.29.1268260895936; Wed, 10 Mar 2010 14:41:35 -0800 (PST) In-Reply-To: <21592.1268260640@sss.pgh.pa.us> References: <4B976229.7030407@gmail.com> <4B977045.6040604@gmail.com> <4B977CD3.5030402@gmail.com> <603c8f071003101355r155a3078p7962360d0167895d@mail.gmail.com> <21592.1268260640@sss.pgh.pa.us> Date: Wed, 10 Mar 2010 17:41:35 -0500 Message-ID: <603c8f071003101441g4b116429t5044db4c52c7a32f@mail.gmail.com> Subject: Re: Strange workaround for slow query From: Robert Haas To: Tom Lane Cc: Yeb Havinga , sverhagen@wps-nl.com, pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/119 X-Sequence-Number: 37944 On Wed, Mar 10, 2010 at 5:37 PM, Tom Lane wrote: > Robert Haas writes: >> It does seem like once the materialize step is done we could notice >> that the tuplestore is empty and, given that uses no outer variables >> or parameters and therefore will never be re-executed, we could skip >> the rest of the index scan. > > Yeah, the same thing occurred to me while looking at this example. > > Right now, nodeNestloop is not really aware of whether the inner scan > depends on any parameters from the outer scan, so it's a bit hard to > determine whether the join can be abandoned. =A0However, I have 9.1 > plans to change that --- I'd like to get rid of the current > pass-the-outer-tuple-to-ReScan hack entirely, in favor of having > nodeNestloop explicitly set PARAM_EXEC parameters for the inner scan. > Once that's in, it would be pretty easy to apply this optimization. > (I've added a note to my private TODO file about it.) Oh, cool. I was thinking about working on that exact project (getting rid of the outer tuple stuff) per some of our earlier conversations, but I don't understand the code well enough so it is likely to be exceedingly slow going if I have to do it. > Another possible objection is that if the inner scan has any volatile > functions in its quals, it might yield a different result upon rescan > even without parameters. =A0However, since we are already willing to stic= k > a Materialize node on it at the whim of the planner, I don't see how > such a short-circuit in the executor would make things any worse. +1. ...Robert