Join consolidation / Removing duplicate joins

I've been looking into improving the performance of queries that have co-related sub-queries, such as:

SELECT id,(SELECT value FROM t2 WHERE t2.id = t1.id) FROM t1;

Where currently we produce a plan that executes the subquery as a sub plan, like:

Though, if the subquery could be proved only to ever return 1 record, then this could be re-written to become:

(notice performance increase)

Of course, you might ask, why not just write the 2nd query in the first place? Well, good idea, but it's not always that simple, take the following update statement:

We end up with a quite a few extra sub queries where probably 1 join would have done the trick. We could have probably written this using the UPDATE/FROM syntax, but if you like to write standard SQL then that might not fly.

Anyway... I've been thinking of writing some code that converts these sub plans into left joins where it can be proved that the subquery would only at most produce 1 row, but as for the case in the UPDATE statement above, I didn't really want the code to create a new join for each subplan that it pulls into the outer query, it would be nice if the code could detect if there's a suitable join there already, and make use of it.

I started to think what an efficient way to do this might be, and thought maybe that it would be good if on RelOptInfo or so, if we could add a bool flag named something like uniquejoin, where this would be set to True, if we could detect that a unique index existed on the relation that was a subset of the join condition, (similar to the requirement of LEFT JOIN removals), we could have a function that tried to pullup all these subplans into the outer query, when it could detect that at most 1 subplan row could exist for each outer plan row, the code could then convert these to a LEFT OUTER JOIN on the outer query.

A bit later in planning, once all the subqueries are planned or pulled up, a "join consolidation" function could be run that merges all of these duplicate joins into 1 join for each relation, it could do this by just looking at relations that have this uniquejoin flag set to true, and then dig deeper to ensure that the join conditions also match. If we also went to the trouble of setting this flag for outer rels and not just the ones we add from this pullup operation, then we could also merge duplicate INNER JOINS too.

Another example of where removing these duplicated joins could be useful is when 2 views get joined that share a non-empty intersection of their relations, where the join conditions are the same.

I kind of thought that perhaps that all of this extra processing to look for unique indexes might just be more processing than it's worth, as success cases of join consolidation might be small, but then I started looking at the executor code around merge and hash join. It seems that there might also be some performance gains in here too.  I see with SEMI joins we move to the next outer row once we've found 1 matching inner row. I believe that with these uniquejoin relations that we could also skip to the next outer row the same as with semi joins. I did some quick hacks in the merge join code to test if there was much performance to gain to be had here and found that on a join conditions involving 2 varchar fields of about 16 chars, that I could get the query to run in 97% of the time (saving 1 comparison on the join condition for each row). So perhaps that's promising enough to make it worth the extra planning cycles of setting this uniquejoin flag.

Does anyone have any thoughts on this?

Regards

David Rowley