Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL’s New LATERAL Join Type

blog.heapanalytics.com

Post reply on HN
Print view View original

31–40 of 42 posts

Re: PostgreSQL’s New LATERAL Join Type

#31

Post by jeffdavis »

tracker1 wrote: post #28
ars wrote: post #20

So it seems this does the same thing as putting the subquery in the list of columns to return, except more efficient. i.e. SELECT a, (SELECT b FROM ...) b FROM ....

Kind of.. it's more expensive than a direct join, but you can do more with it. AFAIK it's syntactic sugar around a correlated sub-query... It may be slightly more efficient now too. It should probably be used sparingly and over limited result sets.

I think it goes beyond syntax sugar, but perhaps there's a transformation between them that I'm not aware of.

Re: PostgreSQL’s New LATERAL Join Type

#32

Post by _e9sw »

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.

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."

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.

My understanding is that lateral was really meant for set returning functions like generate_series as others have already mentioned.

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!

Re: PostgreSQL’s New LATERAL Join Type

#33

Post by ajw0100 »

_e9sw wrote: post #32

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. 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…

Looking at this a little further, the outer nested loop could cause issues in the left join vs. left join lateral version, depending on how many use_demo events there are in the week following the user's first view_homepage event. I added another query that uses CTEs which allows for intermediate aggregation which should make the size of the nested loops similar between both versions. However, I wouldn't be surprised if the CTEs take more memory than the lateral joins because CTEs are basically temp tables that only last for the duration of the query. Lateral may indeed be the best option but ideally I would populate this table with real data, gather statistics, and then run explain analyze on each query.

Re: PostgreSQL’s New LATERAL Join Type

#34

Post by ajw0100 »

nunwuo wrote: post #15
bchallenor wrote: post #10

Earlier quoted context omitted.

No, you can write queries that are not really possible to express without it. Basically, it allows you to execute a table-valued function for each row in an earlier query. For example, in SQL Server I find a common use of CROSS APPLY (which appears to be the same thing) is where the "table-valued function" is a SELECT with a WHERE clause referencing the earlier query, an ORDER BY, and a TOP (=LIMIT) 1. (In fact, this…

That's not true. Anything you can do with LATERAL you can also do with correlated scalar subqueries in the SELECT list. LATERAL simply makes writing these kinds of queries easier and more intuitive.

What if the limits on the lateral subqueries were 2 instead of 1, and they were doing select * instead on select sum() in the outer query? How would you recreate that with correlated SCALAR subqueries? There's no such thing as non-scalar correlated subqueries is there?

Re: PostgreSQL’s New LATERAL Join Type

#36

Post by nunwuo »

ajw0100 wrote: post #34
nunwuo wrote: post #15

Earlier quoted context omitted.

That's not true. Anything you can do with LATERAL you can also do with correlated scalar subqueries in the SELECT list. LATERAL simply makes writing these kinds of queries easier and more intuitive.

What if the limits on the lateral subqueries were 2 instead of 1, and they were doing select * instead on select sum() in the outer query? How would you recreate that with correlated SCALAR subqueries? There's no such thing as non-scalar correlated subqueries is there?

Untested, but this is the general approach:

  SELECT unnest(ar).* FROM
    (SELECT ARRAY(SELECT tbl FROM tbl
                  WHERE .. ORDER BY .. LIMIT 2) AS ar
     FROM .. OFFSET 0) ss;
If you want a specific set of columns instead of *, you'd need to create a custom composite type to create an array of, since it's not possible to "unpack" anonymous records.

Re: PostgreSQL’s New LATERAL Join Type

#38

Post by jordanlev »

kidmenot wrote: post #35

From the many specific comments on here, it sounds like most people don't use an ORM. Is that the case? Not trolling or trying to start a flame, I'm genuinely curious as to how people here get stuff done.

Even folks who use an ORM still need to go outside the ORM for some operations -- especially reporting (like the example in the article). I find ORM's very helpful for basic CRUD operations, especially when one conceptual entity of data maps to one or two database tables directly. But there are just some things that can be expressed in SQL more efficiently than you could in OOP programming (and vice-versa).

Re: PostgreSQL’s New LATERAL Join Type

#39

Post by empthought »

kidmenot wrote: post #35

From the many specific comments on here, it sounds like most people don't use an ORM. Is that the case? Not trolling or trying to start a flame, I'm genuinely curious as to how people here get stuff done.

...we let people like you shuffle simple result sets in and out of web pages, freeing us up to do more interesting things?

(ha ha, only serious)

Re: PostgreSQL’s New LATERAL Join Type

#40

Post by kidmenot »

empthought wrote: post #39
kidmenot wrote: post #35

From the many specific comments on here, it sounds like most people don't use an ORM. Is that the case? Not trolling or trying to start a flame, I'm genuinely curious as to how people here get stuff done.

...we let people like you shuffle simple result sets in and out of web pages, freeing us up to do more interesting things? (ha ha, only serious)

I don't get it. I don't think your comment has anything to do with mine.
Post reply on HN