I was reading this post the other day:
http://blog.heapanalytics.com/postgresqls-powerful-new-join-type-lateral/
I suspected some of the claims in the post may not have been accurate. This one in particular:
"Without lateral joins, we would need to resort to PL/pgSQL to do this analysis. Or, if our data set were small, we could get away with complex, inefficient queries."
The sum(1) and order by time limit 1 approach seemed less than ideal to me
and I thought this analysis could be done with normal left joins instead of
lateral left joins. So I came up with a proof of concept:
https://github.com/ajw0100/snippets/tree/master/SQL/lateral
Is my conclusion in the README correct? Does anything beyond
select...from...where in a laterally joined subquery force a nested loop? In
that case, is lateral really only useful with set returning functions as the
docs suggest? Does anyone know of any use cases for lateral that do not
involve a set returning function?