
PostgreSQL’s New Join Type: LATERAL (2014) - fanf2
https://heap.io/blog/engineering/postgresqls-powerful-new-join-type-lateral
======
d_watt
I'd normally do what's outlined in this article with CTEs. IE, make a
selection, use that as a base in a following selection, repeat, until you have
all your data, then join it all together in the needed view at the end.

Is there a performance advantage to using a lateral join over CTEs for this
type of analysis?

~~~
jimktrains2
CTEs can be an optimization barrier. Only recently did that change from "are"
to "can be" and even now only in specific situations is it not.

~~~
d_watt
Interesting. I would think conceptually lateral joins would suffer from
similar issues, as it would seem like you need to run a query, get a result,
and then use it in the next query, and these are all just fancy ways of
writing out subqueries/temp tables.

Lateral joins are able to use "downstream" lateral join queries to optimize
"upstream" ones?

~~~
striking
I think part of it is that LATERAL JOINs were (are, for folks still on <12,
which is probably a lot of them) able to be run when needed rather than having
to be completely materialized upstream. Now that CTEs don't have to be
materialized, this is less interesting.

Beyond that, LATERAL JOINs are really nice way to write out a subquery without
a ton of nesting, as shown at [https://ddrscott.github.io/blog/2017/what-the-
sql-lateral/](https://ddrscott.github.io/blog/2017/what-the-sql-lateral/).
(LATERAL can be applied to things in FROM! Especially useful given some set-
returning functions can only be used in FROM.)

I think even though you can do most of these things using subqueries or CTEs,
LATERAL makes it ergonomic to do them.

I'm hoping someone a little more knowledgable than I replies to this comment
with some more details, as I know my knowledge on this particular subject is
spotty.

------
jimktrains2
Pg9.3 is pretty old at this point.

That said laterals can be very useful, and it's still worth reading the
article I'd you're not familiar with them.

