LATERAL is awesome. It makes a lot of queries that required sub-select joins much simpler to write and later read.<p>It's also great for set returning functions. Even cooler, you don't need to explicitly specify the LATERAL keyword. The query planner will add it for you automatically:<p><pre><code> -- NOTE: WITH clause is just to fake a table with data:
WITH foo AS (
SELECT 'a' AS name
, 2 AS quantity
UNION ALL
SELECT 'b' AS name
, 4 AS quantity)
SELECT t.*
, x
FROM foo t
-- No need to say "LATERAL" here as it's added automatically
, generate_series(1,quantity) x;
name | quantity | x
------+----------+---
a | 2 | 1
a | 2 | 2
b | 4 | 1
b | 4 | 2
b | 4 | 3
b | 4 | 4
(6 rows)</code></pre>
I'm a huge believer in postgres' json store, but anything above the basic queries can get a bit messy. Lateral joins really clean up aggregate data queries on json columns. E.g.<p><pre><code> SELECT id, COUNT(keys)
FROM users,
LATERAL json_object_keys(login_history) keys
GROUP BY id;</code></pre>
As someone who only works now and then with Postgres, this is what made it click for me:<p>"Loosely, it means that a LATERAL join is like a SQL foreach loop, in which PostgreSQL will iterate over each row in a result set and evaluate a subquery using that row as a parameter."
The `sum(1)` and `order by...limit` approach really isn't the best way to build the funnel. And if you take another approach then this could have easily been built with normal left joins.<p>Also, you should probably show some explain plans before making this claim: "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."<p>Here's a comparison of the explain plan from your query without the `sum(1)` and `order by...limit` business and a query using only left joins (no use of lateral): [link redacted]. Note, I ran this against an empty copy of your exact table (no data, no statistics). However, the explain plans are the same.<p>My understanding is that lateral was really meant for set returning functions like generate_series as others have already mentioned.<p>Edit: I should mention I know you were just trying to demonstrate how lateral works and that it is always good to see people writing about new Postgres features!
> 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.<p>I don't buy the performance benefit over derived tables with properly indexed fields for this example. However, I'd definitely use this more so with functions.
Here's another explanation from the Postgres docs with additional examples:
<a href="http://www.postgresql.org/docs/9.3/static/queries-table-expressions.html#QUERIES-LATERAL" rel="nofollow">http://www.postgresql.org/docs/9.3/static/queries-table-expr...</a>
seems similar to CROSS APPLY and OUTER APPLY from mssql <a href="http://technet.microsoft.com/en-us/library/ms175156%28v=sql.105%29.aspx" rel="nofollow">http://technet.microsoft.com/en-us/library/ms175156%28v=sql....</a>
Fantastic. This is <i>exactly</i> what I wanted earlier in the week. I ended up just writing some python to do the looping for me as it was only a few thousand rows and I was optimizing for developer time (i.e. mine).
How does this relate to WITH RECURSIVE (CTEs)?<p><a href="http://www.postgresql.org/docs/8.4/static/queries-with.html" rel="nofollow">http://www.postgresql.org/docs/8.4/static/queries-with.html</a>
cross join lateral also works with json arrays. Select your json array then CROSS JOIN LATERAL with a jsonb_to_recordset() to take your json array into individual columns.
From the many specific comments on here, it sounds like most people don't use an ORM. Is that the case?<p>Not trolling or trying to start a flame, I'm genuinely curious as to how people here get stuff done.
Nice read.<p>Somethings i would have liked to have read/seen though are, the hardware specs, database dimensions (nr of entries in tables etc) and query times.<p>Just my penny. Hope you can spend it still somewhere :)
So it seems this does the same thing as putting the subquery in the list of columns to return, except more efficient.<p>i.e.<p><pre><code> SELECT a, (SELECT b FROM ...) b FROM ....</code></pre>