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

21–30 of 42 posts

Re: PostgreSQL’s New LATERAL Join Type

#21

Post by bchallenor »

nunwuo wrote: post #18
bchallenor wrote: post #17

Earlier quoted context omitted.

The syntax for this is pretty horrible, however. And if you want to return more than one column from the subquery, you would have to duplicate the subquery definition for each column, right? Then you'd have to have faith that the optimizer can work out what you meant and reconstruct just a single subquery.

There's no faith required; the planner is guaranteed not to do that. The "normal" way is to create a composite type containing each of the columns you need, and then "unpack" it to separate columns. Horrible? Yeah, but it's possible.

Is it possible with scalar subqueries to perform anything other than a tree of correlation? With CROSS APPLY one can correlate a DAG of subqueries, e.g. a diamond where B and C depend on A, and D depends on B and C.

Re: PostgreSQL’s New LATERAL Join Type

#22

Post by [deleted] »

bchallenor wrote: post #6
sehrope wrote: post #2

LATERAL is awesome. It makes a lot of queries that required sub-select joins much simpler to write and later read. 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: -- 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 ,…

Thanks for the example. It seems to be the same as MS SQL Server's CROSS APPLY / OUTER APPLY? Anyway, good that Postgres has it too, now. There are several Postgres features I'd love in SQL Server, like range types...

[deleted]

Re: PostgreSQL’s New LATERAL Join Type

#23

Post by [deleted] »

bchallenor wrote: post #6
sehrope wrote: post #2

LATERAL is awesome. It makes a lot of queries that required sub-select joins much simpler to write and later read. 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: -- 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 ,…

Thanks for the example. It seems to be the same as MS SQL Server's CROSS APPLY / OUTER APPLY? Anyway, good that Postgres has it too, now. There are several Postgres features I'd love in SQL Server, like range types...

[deleted]

Re: PostgreSQL’s New LATERAL Join Type

#24

Post by adamtj »

lsaferite wrote: post #7

How does this relate to WITH RECURSIVE (CTEs)? http://www.postgresql.org/docs/8.4/static/queries-with.html

LATERAL allows a subquery or function to refer to the records that it's being joined to when computing results.

WITH RECURSIVE allows a query to refer its own results when computing its results. That may sound mind-bending, but it's really just poorly named way to do a "while" loop in SQL.

A WITH RECURSIVE has the form (base-case-query UNION ALL iterative-step-query). The query for the iterative step can refer to itself via the WITH alias. When it does so, it's actually operating on only those records produced by the previous step of the iteration. The iterative-step-query will execute possibly multiple times, stopping only when it doesn't produce any more records.

Here's the WITH RECURSIVE example from the Postgres docs, translated into Python:

  all_records   = []
  previous_step = [1] # base case, i.e. "VALUES (1)."
  while previous_step:
      all_records.extend(previous_step)
      # iterative step, i.e. "SELECT n+1 FROM t WHERE n
The confusing part is that the alias "t" in the SQL example means different things in different places. Outside the WITH RECURSIVE, "t" is equivalent to "all_records" in the example. Within the WITH RECURSIVE definition, "t" is the same as "previous_step".

Re: PostgreSQL’s New LATERAL Join Type

#25

Post by j-kidd »

tome wrote: post #8
sehrope wrote: post #2

LATERAL is awesome. It makes a lot of queries that required sub-select joins much simpler to write and later read. 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: -- 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 ,…

Am I right in thinking that this does not increase Postgres's expressive power but allows more concise implementation?

Looking at the examples from the official documentation, I agree with your sentiment. Indeed, the conciseness can cause some confusion to people familiar with the existing scoping rules.

IMHO, it's a good thing if LATERAL is only added as some kind of syntactic sugar. I once had to use LATERAL in DB2 as a band-aid solution for its broken scoping rules: https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQ...

Re: PostgreSQL’s New LATERAL Join Type

#26

Post by tracker1 »

bchallenor wrote: post #6
sehrope wrote: post #2

LATERAL is awesome. It makes a lot of queries that required sub-select joins much simpler to write and later read. 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: -- 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 ,…

Thanks for the example. It seems to be the same as MS SQL Server's CROSS APPLY / OUTER APPLY? Anyway, good that Postgres has it too, now. There are several Postgres features I'd love in SQL Server, like range types...

Usually when I've had to use cross/outer apply it's been to work around overly normalized, and somewhat bad data.

Agreed on range types.. proper enums in T-SQL would be nice too. I'm really liking where PL/v8 is going, and would like to see something similar in MS-SQL server as well.. the .Net extensions are just too much of a pain to do much with. It's be nice to have a syntax that makes working with custom data types, or even just JSON and XML easier.

If PostgreSQL adds built-in replication to the Open-Source version that isn't a hacky add-on, and has failover similar to, for example MongoDB's replica sets, I'm so pushing pg for most new projects.

Maria/MySQL seem to be getting interesting as well. Honestly, I like MS-SQL until the cost of running it gets a little wonky (Azure pricing going from a single instance to anything that can have replication for example). Some of Amazon's offerings are really getting compelling here.

Re: PostgreSQL’s New LATERAL Join Type

#27

Post by tracker1 »

buckbova wrote: post #9

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

In MS-SQL I've done similar things with CROSS/OUTER APPLY, which seems to be about the same things... It's usually to work around wonky data and has some overhead.

In my own case it was so I could create a view that would give me a denormalized view of most of the data for a given record... most of the fields were in a common PROPERTIES table with some funky data in it... Sometimes normalization goes too far.

Re: PostgreSQL’s New LATERAL Join Type

#28

Post by tracker1 »

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.

Re: PostgreSQL’s New LATERAL Join Type

#30

Post by jeffdavis »

smegel wrote: post #29

Is this the same as a correlated sub-query (something that exist in Teradata)?

No:

https://news.ycombinator.com/item?id=8690389

But they are similar... Kind of like correlated subqueries in the from clause. But they allow a few new twists in the semantics.

Post reply on HN