MIME-Version: 1.0
References: 
 <CAKU4AWoZksNZ4VR-fLTdwmiR91WU8qViDBNQKNwY=7iyo+uV0w@mail.gmail.com>
 <247851.1668293143@sss.pgh.pa.us>
In-Reply-To: <247851.1668293143@sss.pgh.pa.us>
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 15 Nov 2022 09:02:13 +0800
Message-ID: 
 <CAMbWs4_VE-CicUwa7M5Gtm7Eu=NtZLS6ENKUGdf7300YHyxmAA@mail.gmail.com>
Subject: Re: A new strategy for pull-up correlated ANY_SUBLINK
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andy Fan <zhihui.fan1213@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000babf0d05ed77e8b9"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMbWs4_VE-CicUwa7M5Gtm7Eu%3DNtZLS6ENKUGdf7300YHyxmAA%40mail.gmail.com>
Precedence: bulk

--000000000000babf0d05ed77e8b9
Content-Type: text/plain; charset="UTF-8"

On Sun, Nov 13, 2022 at 6:45 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Looking again at that contain_vars_of_level restriction, I think the
> reason for it was just to avoid making a FROM subquery that has outer
> references, and the reason we needed to avoid that was merely that we
> didn't have LATERAL at the time.  So I experimented with the attached.
> It seems to work, in that we don't get wrong answers from any of the
> small number of places that are affected.  (I wonder though whether
> those test cases still test what they were intended to, particularly
> the postgres_fdw one.  We might have to try to hack them some more
> to not get affected by this optimization.)  Could do with more test
> cases, no doubt.


Hmm, it seems there were discussions about this change before, such as
in [1].


> One thing I'm not at all clear about is whether we need to restrict
> the optimization so that it doesn't occur if the subquery contains
> outer references falling outside available_rels.  I think that that
> case is covered by is_simple_subquery() deciding later to not pull up
> the subquery based on LATERAL restrictions, but maybe that misses
> something.


I think we need to do this, otherwise we'd encounter the problem
described in [2].  In short, the problem is that the constraints imposed
by LATERAL references may make us fail to find any legal join order.  As
an example, consider

explain select * from A where exists
    (select * from B where A.i in (select C.i from C where C.j = B.j));
ERROR:  failed to build any 3-way joins

[1]
https://www.postgresql.org/message-id/flat/CAN_9JTx7N%2BCxEQLnu_uHxx%2BEscSgxLLuNgaZT6Sjvdpt7toy3w%40mail.gmail.com

[2]
https://www.postgresql.org/message-id/CAMbWs49cvkF9akbomz_fCCKS=D5TY=4KGHEQcfHPZCXS1GVhkA@mail.gmail.com

Thanks
Richard

--000000000000babf0d05ed77e8b9
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Sun, Nov 13, 2022 at 6:45 AM Tom Lane =
&lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:</=
div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex">
Looking again at that contain_vars_of_level restriction, I think the<br>
reason for it was just to avoid making a FROM subquery that has outer<br>
references, and the reason we needed to avoid that was merely that we<br>
didn&#39;t have LATERAL at the time.=C2=A0 So I experimented with the attac=
hed.<br>
It seems to work, in that we don&#39;t get wrong answers from any of the<br=
>
small number of places that are affected.=C2=A0 (I wonder though whether<br=
>
those test cases still test what they were intended to, particularly<br>
the postgres_fdw one.=C2=A0 We might have to try to hack them some more<br>
to not get affected by this optimization.)=C2=A0 Could do with more test<br=
>
cases, no doubt.</blockquote><div>=C2=A0</div><div>Hmm, it seems there were=
 discussions about this change before, such as<br>in [1].<br></div><div>=C2=
=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex">
One thing I&#39;m not at all clear about is whether we need to restrict<br>
the optimization so that it doesn&#39;t occur if the subquery contains<br>
outer references falling outside available_rels.=C2=A0 I think that that<br=
>
case is covered by is_simple_subquery() deciding later to not pull up<br>
the subquery based on LATERAL restrictions, but maybe that misses<br>
something.</blockquote><div>=C2=A0</div><div>I think we need to do this, ot=
herwise we&#39;d encounter the problem<br>described in [2].=C2=A0 In short,=
 the problem is that the constraints imposed<br>by LATERAL references may m=
ake us fail to find any legal join order.=C2=A0 As<br>an example, consider<=
br><br>explain select * from A where exists<br>=C2=A0 =C2=A0 (select * from=
 B where A.i in (select C.i from C where C.j =3D B.j));<br>ERROR: =C2=A0fai=
led to build any 3-way joins<br><br>[1] <a href=3D"https://www.postgresql.o=
rg/message-id/flat/CAN_9JTx7N%2BCxEQLnu_uHxx%2BEscSgxLLuNgaZT6Sjvdpt7toy3w%=
40mail.gmail.com">https://www.postgresql.org/message-id/flat/CAN_9JTx7N%2BC=
xEQLnu_uHxx%2BEscSgxLLuNgaZT6Sjvdpt7toy3w%40mail.gmail.com</a><br><br>[2] <=
a href=3D"https://www.postgresql.org/message-id/CAMbWs49cvkF9akbomz_fCCKS=
=3DD5TY=3D4KGHEQcfHPZCXS1GVhkA@mail.gmail.com">https://www.postgresql.org/m=
essage-id/CAMbWs49cvkF9akbomz_fCCKS=3DD5TY=3D4KGHEQcfHPZCXS1GVhkA@mail.gmai=
l.com</a><br><br>Thanks<br>Richard<br></div></div></div>

--000000000000babf0d05ed77e8b9--