Use cases for lateral that do not involve a set returning function

From: AJ Welch <awelch0100(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Use cases for lateral that do not involve a set returning function
Date: 2014-12-07 04:40:08
Message-ID: CAO-RzR+yEFGdi8CLgqyrfm77c9D5Ttk2ErpKnBPqkWO9w8WGPA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 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?

Thanks,
AJ

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sérgio Saquetim 2014-12-07 20:11:02 Strange behavior in generate_series(date, date, interval) with DST
Previous Message Bruce Momjian 2014-12-06 20:38:04 Re: I did some testing of GIST/GIN vs BTree indexing…