On 2/11/2022 09:02, Andy Fan wrote:
In the past we pull-up the ANY-sublink with 2 steps, the first step is to
pull up the sublink as a subquery, and the next step is to pull up the
subquery if it is allowed. The benefits of this method are obvious,
pulling up the subquery has more requirements, even if we can just finish
the first step, we still get huge benefits. However the bad stuff happens
if varlevelsup = 1 involves, things fail at step 1.
convert_ANY_sublink_to_join ...
if (contain_vars_of_level((Node *) subselect, 1))
return NULL;
In this patch we distinguish the above case and try to pull-up it within
one step if it is helpful, It looks to me that what we need to do is just
transform it to EXIST-SUBLINK.
Maybe code [1] would be useful for your purposes/tests.
We implemented flattening of correlated subqueries for simple N-J case,
but found out that in some cases the flattening isn't obvious the best
solution - we haven't info about cardinality/cost estimations and can do
worse.
I guess, for more complex flattening procedure (with aggregate function
in a targetlist of correlated subquery) situation can be even worse.
Maybe your idea has such corner cases too ?
[1]
https://www.postgresql.org/message-id/flat/CALNJ-vTa5VgvV1NPRHnypdnbx-fhDu7vWp73EkMUbZRpNHTYQQ%40mail.gmail.com
--
regards,
Andrey Lepikhov
Postgres Professional
