ON predicate of Postgres LATERAL JOIN

Question

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 subquery B and join a result set of the B to that "current" row of A - if the B query 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?

The given query is just a part of legacy backend we strive to optimize. I agree that it senseless from functional point of view and will be rewritten. But the very fact of presence of non-trivial ON clause near the LATERAL join in real commercial code impressed me to the degree that I decided to post a question. Thanks to @Zegarek I know now it's just a matter of taming the Postgres planning/optimizer. — Nikita Glukhov
– Nikita Glukhov, Commented Dec 10, 2023 at 22:43
ON and USING allow you to add an extra predicate for the related rows to match. I prefer to keep all predicates centralized in the inner join, and I leave the external one as ON TRUE for simplicity. — The Impaler
– The Impaler, Commented Dec 11, 2023 at 1:24

Zegarek · Accepted Answer · 2023-12-11 10:07:16Z

3

How does ON predicate of Postgres LATERAL JOIN work?

Just the same as in a regular, non-LATERAL JOIN. It's tied to it independently from what's happening in the subquery.

But why is there a need in ON predicate?

The fact that the subquery includes what could effectively be used the same way a join condition is, doesn't change the syntactic requirements of the JOIN outside.

Why using (route_id)? We already have where rts.route_id = r.route_id inside the subquery!

Whether you apply the condition inside the subquery in the where, outside in the join, or even further in the outer where, or in any combination of the three spots, it changes nothing about the result. One difference that's helpful in this case is that going for join allows PostgreSQL to better reason about the query and optimise it: changing using (route_id) to on true removes that benefit.

It doesn't look like this should matter and ideally, it should not. Unfortunately, the declarative meaning of any query still has to be somehow understood by the Planner/Optimizer. An explicit join condition clearly communicates dependency and lets it inspect the relation between the joined tables to apply adequate optimisation techniques. A lateral join (...)subquery on true only means that the subquery is meant to be evaluated for each row and on true obfuscates how it depends on that row - that's left untouched as internal logic of the lateral subquery, and lateral merely 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 the where, but it doesn't (at least as of PostgreSQL 16.1). It doesn't do that either if you move the where out 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 join being lateral, and further, that there's actually no join required. You're not requesting anything from the subquery, so it's only providing matches for route, 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:

SELECT
r.acceptance_status, count(*) as count
FROM route r
LEFT JOIN LATERAL (
    SELECT distinct rts.route_id
    FROM route_to_shipment rts
) rts using (route_id)
group by 1

But since route_to_shipment.route_id that doesn't match route.route_id is ignored thanks to the left join and left join will fetch unmatched route.route_id regardless of their presence in the subquery, it can be completely removed:

SELECT r.acceptance_status, count(*)
FROM route r group by 1;

You'll get the exact same plan for all three forms of the query, unsurprisingly short and quick in all cases:

QUERY PLAN
Sort  (cost=41.60..42.10 rows=200 width=40) (actual time=0.590..0.591 rows=5 loops=1)
  Output: r.acceptance_status, (count(*))
  Sort Key: r.acceptance_status, (count(*))
  Sort Method: quicksort  Memory: 25kB
  ->  HashAggregate  (cost=31.95..33.95 rows=200 width=40) (actual time=0.583..0.585 rows=5 loops=1)
        Output: r.acceptance_status, count(*)
        Group Key: r.acceptance_status
        Batches: 1  Memory Usage: 40kB
        ->  Seq Scan on public.route r  (cost=0.00..24.97 rows=1397 width=32) (actual time=0.007..0.158 rows=2000 loops=1)
              Output: r.route_id, r.acceptance_status
Planning Time: 0.056 ms
Execution Time: 0.608 ms

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:

QUERY PLAN
Incremental Sort  (cost=475713.66..475735.06 rows=200 width=40) (actual time=3076.386..3076.388 rows=5 loops=1)
  Output: r.acceptance_status, (count(*))
  Sort Key: r.acceptance_status, (count(*))
  Presorted Key: r.acceptance_status
  Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB
  ->  GroupAggregate  (cost=475713.59..475726.06 rows=200 width=40) (actual time=3076.153..3076.348 rows=5 loops=1)
        Output: r.acceptance_status, count(*)
        Group Key: r.acceptance_status
        ->  Sort  (cost=475713.59..475717.08 rows=1397 width=32) (actual time=3076.035..3076.135 rows=2000 loops=1)
              Output: r.acceptance_status
              Sort Key: r.acceptance_status
              Sort Method: quicksort  Memory: 86kB
              ->  Nested Loop Left Join  (cost=0.00..475640.60 rows=1397 width=32) (actual time=34.858..3074.645 rows=2000 loops=1)
                    Output: r.acceptance_status
                    ->  Seq Scan on public.route r  (cost=0.00..24.97 rows=1397 width=36) (actual time=0.013..0.387 rows=2000 loops=1)
                          Output: r.route_id, r.acceptance_status
                    ->  GroupAggregate  (cost=0.00..340.44 rows=1 width=36) (actual time=1.536..1.536 rows=1 loops=2000)
                          Output: rts.route_id, NULL::integer[]
                          ->  Seq Scan on public.route_to_shipment rts  (cost=0.00..340.43 rows=101 width=4) (actual time=0.166..1.532 rows=10 loops=2000)
                                Output: rts.shipment_id, rts.route_id
                                Filter: (rts.route_id = r.route_id)
                                Rows Removed by Filter: 19990
Planning Time: 0.134 ms
JIT:
  Functions: 11
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 0.896 ms, Inlining 0.000 ms, Optimization 1.520 ms, Emission 31.319 ms, Total 33.735 ms
Execution Time: 3279.075 ms

Complete demo at db<>fiddle:

edited Dec 11, 2023 at 10:07

answered Dec 10, 2023 at 16:33

Zegarek

29.9k5 gold badges27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Salman Arshad Over a year ago

"make a lot of sense"... can you give an example where on clause makes a lot of sense?

Zegarek Over a year ago

@SalmanA All I did in this entire answer is pretty much showing why OP's query is the example you request. The version with explicit using (route_id) condition enables the entire subquery to get optimized away, while the on true blocks that. I don't mean to be rude but right now your comment sounds like you didn't make it past the opening sentence. If the answer is too long, unclear, requires rephrasing, I'll welcome remarks how to improve it.

Salman Arshad Over a year ago

Sorry, if the optimizer is generating different plans for the above query then the optimizer is at fault (the query doesn't make sense either... the on clause matches the where clause). May be the optimizer in a future version will do a better job. So again, any case where having an ON clause makes sense when you have a where clause?

Zegarek Over a year ago

I agree and I point that out in the second paragraph: Ideally, the planner should peek inside and see the where, but it doesn't. Maybe this post leaves an impression that I recommend some bizarre lateral join condition syntax to be mandatory, as a general rule - I don't. The idea was to explain why "OP's friend's query" works better and their improvement idea will break that, apart from pointing out what's wrong with both in the first place.

Nikita Glukhov Over a year ago

@Zegarek I marked your answer as the accepted one, because only you pointed to a non-obvious role of ON as an implicit hint to PG planner/optimizer. In everyday battles with long-running queries this kind of knowledge could be very helpful. It may look ugly, but in real commercial code most of us prefer technically justified solutions over the "pretty" ones if the latter ties our hands.

|

JohnH · Accepted Answer · 2023-12-10 09:24:20Z

-1

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 ON clause is a syntactic requirement for other than CROSS JOIN or NATURAL JOIN (the latter of which is an ill-conceived idea that should be expunged from SQL). For LEFT JOIN LATERAL, use ON TRUE instead of USING to avoid unnecessary dependencies on the subquery's select list.

answered Dec 10, 2023 at 9:24

JohnH

3,2932 gold badges6 silver badges20 bronze badges

Collectives™ on Stack Overflow

ON predicate of Postgres LATERAL JOIN

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related