It's not clear to me what transformations to pull-up correlated subqueries you have in mind. Perhaps the best way to get that happen is to post some proposal on the hackers mailing list, with a description of the benefits and how the planner might decide to apply the transformation. I'd expect that to be the challenging part - costing the alternative transformations.
That was mostly the problem with the GROUP BY reordering - the statistics we have are not sufficient to make reliable decisions, and it got the patch reverted. The incremental sort has somewhat similar challenges, FWIW.
In my experience, subqueries frequently appear in two places: WHERE clause (x IN smth) and target lists.
We have already discussed [1] one such transformation; the most apparent part of this work was committed [2] in the core. But you're right: the key question is cost estimation: without an index on the column involved in the expression, it sometimes makes little sense.
That's why I believe committing this to the core is not feasible for now. However, using a hook in an extension could alleviate the situation for some use cases. The fact that other DBMSes implement such transformations is a promising sign that the issue can be resolved, providing reassurance for the future of query optimization.
It's not clear to me what transformations to pull-up correlated subqueries you have in mind. Perhaps the best way to get that happen is to post some proposal on the hackers mailing list, with a description of the benefits and how the planner might decide to apply the transformation. I'd expect that to be the challenging part - costing the alternative transformations.
That was mostly the problem with the GROUP BY reordering - the statistics we have are not sufficient to make reliable decisions, and it got the patch reverted. The incremental sort has somewhat similar challenges, FWIW.
In my experience, subqueries frequently appear in two places: WHERE clause (x IN smth) and target lists.
We have already discussed [1] one such transformation; the most apparent part of this work was committed [2] in the core. But you're right: the key question is cost estimation: without an index on the column involved in the expression, it sometimes makes little sense.
That's why I believe committing this to the core is not feasible for now. However, using a hook in an extension could alleviate the situation for some use cases. The fact that other DBMSes implement such transformations is a promising sign that the issue can be resolved, providing reassurance for the future of query optimization.
[1] https://www.postgresql.org/message-id/flat/CALNJ-vTa5VgvV1NPRHnypdnbx-fhDu7vWp73EkMUbZRpNHTYQQ%40mail.gmail.com
[2] https://www.postgresql.org/message-id/flat/CAKU4AWoZksNZ4VR-fLTdwmiR91WU8qViDBNQKNwY%3D7iyo%2BuV0w%40mail.gmail.com