From: | Richard Guo <guofenglinux(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: A new strategy for pull-up correlated ANY_SUBLINK |
Date: | 2022-11-15 01:02:13 |
Message-ID: | CAMbWs4_VE-CicUwa7M5Gtm7Eu=NtZLS6ENKUGdf7300YHyxmAA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Sun, Nov 13, 2022 at 6:45 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
Thanks
Richard
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2022-11-15 01:25:31 | Re: Assertion failure in SnapBuildInitialSnapshot() |
Previous Message | Michael Paquier | 2022-11-15 00:57:26 | Re: Avoid overhead open-close indexes (catalog updates) |