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
