How does ON predicate of Postgres LATERAL JOIN work?
Let me clarify question a bit. I’ve read the official documentation and a bunch of articles about this kind of JOIN.
As far as I understood it is a foreach loop with a correlated subquery inside
- it iterates over all records of a table
A, allowing to reference columns of a "current" row in a correlated subqueryBand join a result set of theBto that "current" row ofA– if theBquery returns 1 row there is only one pair, and if the B query return N rows there are N pairs with duplicated "current" row of the A. The same behavior like in usual JOINs.
But why is there a need in ON predicate?
For me, in usual JOINs we use ON because we have a cartesian product of 2 tables to be filtered out, and it is not the case of LATERAL JOIN, which produces resulting pairs directly.
In other words, in my developer experience I’ve only seen CROSS JOIN LATERAL and LEFT JOIN LATERAL () ON TRUE (the latter looks quite clumsy, though) but one day a colleague showed me
SELECT
r.acceptance_status, count(*) as count
FROM route r
LEFT JOIN LATERAL (
SELECT rts.route_id, array_agg(rts.shipment_id) shipment_ids
FROM route_to_shipment rts
where rts.route_id = r.route_id
GROUP BY rts.route_id
) rts using (route_id)
and this exploded my mind. Why using (route_id)? We already have where rts.route_id = r.route_id inside the subquery!
Maybe I understand the mechanics of LATERAL joins wrong?
2
Answers
A variant of this question has been answered at https://dba.stackexchange.com/questions/301884/do-postgresql-lateral-joins-require-or-allow-an-on-clause.
In short, the
ONclause is a syntactic requirement for other thanCROSS JOINorNATURAL JOIN(the latter of which is an ill-conceived idea that should be expunged from SQL). ForLEFT JOIN LATERAL, useON TRUEinstead ofUSINGto avoid unnecessary dependencies on the subquery’s select list.It does make a lot of sense, even though it doesn’t look like it should. Unfortunately, it isn’t as much about the declarative meaning of either form of the statement as it is about what the Planner/Optimizer makes of it: an explicit
joincondition clearly communicates dependency and lets it inspect the relation between the joined tables to apply adequate optimisation techniques.A
lateral join (...)subquery on trueonly means that the subquery is meant to be evaluated for each row andon trueobfuscates how it depends on that row – that’s left untouched as internal logic of the lateral subquery, andlateralmerely allows it to use the external reference, without communicating much more than that to the planner, so it’s left unoptimised. Ideally, the planner should peek inside and see thewhere, but it doesn’t (at least as of PostgreSQL 16.1). It doesn’t do that either if you move thewhereout to the outer query, old-implicit-join-style, although that does help it speed things up in other ways.When you run your friend’s query, it just makes it more obvious there’s nothing useful in the
joinbeinglateral, and further, that there’s actually nojoinrequired. You’re not requesting anything from the subquery, so it’s only providing matches forroute, which could be useful to count combinations of matched rows but because you also aggregate there, it can at most provide a single match. In the end, it turns out it contributes nothing to the query, which effectively can be shortened first to this:But since
route_to_shipment.route_idthat doesn’t matchroute.route_idis ignored thanks to theleft joinandleft joinwill fetch unmatchedroute.route_idregardless of their presence in the subquery, it can be completely removed:You’ll get the exact same plan for all three forms of the query, unsurprisingly short and quick in all cases:
If you instead
left lateral join...on true, you’re pretending you want the subquery to be evaluated for every row, no matter what, and you obfuscate the dependency, so the planner does literally that:Complete demo at db<>fiddle: