Re: A new strategy for pull-up correlated ANY_SUBLINK

Hi Andrey:

> > 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.
>

Looks like we are resolving the same problem, IIUC, great that more
people are interested in it!

We implemented flattening of correlated subqueries for simple N-J case,

I went through the code, and it looks like you tried to do the pull-up by
yourself, which would have many troubles to think about. but I just
transformed
it into EXIST sublink after I distinguish it as the case I can improve.
> The only change is transforming the format of SUBLINK, so outer-join /
> pull-up as semi-join is unrelated, so the correctness should not be an
> issue.

That is just a difference, no matter which one is better.

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 ?
>

In my case, since aggregate function can't be handled by
covert_EXISTS_sublink_to_join, so it is not the target I want to optimize in
this patch. More testing/review on my method would be pretty appreciated.
but I'm not insisting on my method at all. Link [2] might be useful as
well.

[2]
https://www.postgresql.org/message-id/CAKU4AWpi9oztiomUQt4JCxXEr6EaQ2thY-7JYDm6c9he0A7oCA%40mail.gmail.com

--
Best Regards
Andy Fan