public inbox for [email protected]  

help / color / mirror / Atom feed

From: Richard Guo <[email protected]>
To: Tom Lane <[email protected]>
Cc: Andy Fan <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: A new strategy for pull-up correlated ANY_SUBLINK
Date: Tue, 15 Nov 2022 09:02:13 +0800
Message-ID: <CAMbWs4_VE-CicUwa7M5Gtm7Eu=NtZLS6ENKUGdf7300YHyxmAA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAKU4AWoZksNZ4VR-fLTdwmiR91WU8qViDBNQKNwY=7iyo+uV0w@mail.gmail.com>
	<[email protected]>

On Sun, Nov 13, 2022 at 6:45 AM Tom Lane <[email protected]> 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%4...

[2]
https://www.postgresql.org/message-id/[email protected]...

Thanks
Richard

view thread (30+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: A new strategy for pull-up correlated ANY_SUBLINK
  In-Reply-To: <CAMbWs4_VE-CicUwa7M5Gtm7Eu=NtZLS6ENKUGdf7300YHyxmAA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox