For example, this would transform the following:

SELECT a.col1, (SELECT b.col2 FROM b WHERE b.x = a.x)
FROM a;

... to the following:

SELECT a.col1, b.col2
FROM a LEFT JOIN b ON b.x = a.x;

Notes:

• Check that this makes sense for other databases as well (likely)
• There's a slight semantic difference between the two: the former errors when the scalar subquery returns more than one row, but the latter returns multiple rows (JOIN). We can perform this transformation e.g. only in cases where we're sure that the subquery returns one row (e.g. there's LIMIT 1 inside, or there's a predicate over a uniquely-constrained column).

See https://www.cybertec-postgresql.com/en/subqueries-and-performance-in-postgresql for more details

/cc @laurenz (post author)