LATERAL和PostgreSQL中的子查询有什么区别? [英] What is the difference between LATERAL and a subquery in PostgreSQL?

问题描述

自从Postgres具备进行LATERAL联接的能力以来，我一直在阅读它，因为我目前为我的团队执行复杂的数据转储，其中包含许多效率不高的子查询，使得整个查询花费了四分钟或更多.

Since Postgres came out with the ability to do LATERAL joins, I've been reading up on it, since I currently do complex data dumps for my team with lots of inefficient subqueries that make the overall query take four minutes or more.

我知道LATERAL联接可能对我有帮助，但是即使阅读了

I understand that LATERAL joins may be able to help me, but even after reading articles like this one from Heap Analytics, I still don't quite follow.

LATERAL联接的用例是什么? LATERAL联接和子查询之间有什么区别?

What is the use case for a LATERAL join? What is the difference between a LATERAL join and a subquery?

推荐答案

更像是相关子查询

LATERAL连接(Postgres 9.3或更高版本)更像是相关子查询，不是普通的子查询.像 Andomar指出的一样，必须为每行对一次LATERAL联接右侧的函数或子查询求值一次.它的左侧-就像 correlated 子查询一样-而普通子查询(表表达式)仅被评估一次 . (不过，查询计划者可以通过多种方法来优化两者的性能.)
这个相关的答案有并排的代码示例，可以解决相同的问题:

More like a correlated subquery

A LATERAL join (Postgres 9.3 or later) is more like a correlated subquery, not a plain subquery. Like Andomar pointed out, a function or subquery to the right of a LATERAL join has to be evaluated once for each row left of it - just like a correlated subquery - while a plain subquery (table expression) is evaluated once only. (The query planner has ways to optimize performance for either, though.)
This related answer has code examples for both side by side, solving the same problem:

• 将GROUP BY查询优化为检索每个用户的最新行

要返回多列，LATERAL连接通常更简单，更简洁，更快捷.
另外，请记住，相关子查询的等效项是 LEFT JOIN LATERAL ... ON true :

For returning more than one column, a LATERAL join is typically simpler, cleaner and faster.
Also, remember that the equivalent of a correlated subquery is LEFT JOIN LATERAL ... ON true:

• 呼叫一组多次返回带有数组参数的函数

它比我们在此处要回答的任何问题更具权威性:

It is more authoritative than anything we are going to put into answers here:

• https://www.postgresql. org/docs/current/static/queries-table-expressions.html#QUERIES-LATERAL
• http://www.postgresql.org/docs/current/static/sql-select.html

• https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-LATERAL
• http://www.postgresql.org/docs/current/static/sql-select.html

LATERAL联接可以完成 项操作，但是(相关)子查询不能(轻松)进行.相关的子查询只能返回一个值，不能返回多列，不能返回多行-裸函数调用除外(如果返回多行，结果行将相乘).但是，即使某些返回集合的功能也仅允许在FROM子句中使用.与unnest()类似，在Postgres 9.4或更高版本中具有多个参数. 手册:

There are things that a LATERAL join can do, but a (correlated) subquery cannot (easily). A correlated subquery can only return a single value, not multiple columns and not multiple rows - with the exception of bare function calls (which multiply result rows if they return multiple rows). But even certain set‑returning functions are only allowed in the FROM clause. Like unnest() with multiple parameters in Postgres 9.4 or later. The manual:

这仅在FROM子句中允许；

所以这可行，但不能轻易用子查询替换:

So this works, but cannot easily be replaced with a subquery:

CREATE TABLE tbl (a1 int[], a2 int[]);
SELECT * FROM tbl, unnest(a1, a2) u(elem1, elem2);  -- implicit LATERAL

FROM子句中的逗号(,)是CROSS JOIN的缩写. 表功能自动假定为LATERAL.
有关UNNEST( array_expression [, ... ] )的特殊情况的更多信息:

The comma (,) in the FROM clause is short notation for CROSS JOIN.
LATERAL is assumed automatically for table functions.
More about the special case of UNNEST( array_expression [, ... ] ):

• 您还可以直接使用SELECT列表中的unnest()之类的设置返回功能.过去，在相同的SELECT列表中，直到Postgres 9.6都具有多个这样的功能，从而表现出令人惊讶的行为. 但最终它已被Postgres 10消毒并且现在是有效的替代方法(即使不是标准SQL).参见:

  You can also use set-returning functions like unnest() in the SELECT list directly. This used to exhibit surprising behavior with more than one such function in the same SELECT list up to Postgres 9.6. But it has finally been sanitized with Postgres 10 and is a valid alternative now (even if not standard SQL). See:

  • SELECT子句中多个返回集合的函数的预期行为是什么?

  以上述示例为基础:

  SELECT *, unnest(a1) AS elem1, unnest(a2) AS elem2
  FROM   tbl;
  

  比较:

  dbfiddle 此处
  第10页的 dbfiddle 此处

  dbfiddle for pg 9.6 here
  dbfiddle for pg 10 here

  手册:

  对于INNER和OUTER联接类型，联接条件必须为 指定，即NATURAL，ON join_condition 中的一个， 或USING( join_column [，...]).含义见下文.
  对于CROSS JOIN，这些子句都不会出现.

  For the INNER and OUTER join types, a join condition must be specified, namely exactly one of NATURAL, ON join_condition, or USING (join_column [, ...]). See below for the meaning.
  For CROSS JOIN, none of these clauses can appear.

  因此，这两个查询是有效的(即使不是特别有用):

  So these two queries are valid (even if not particularly useful):

  SELECT *
  FROM   tbl t
  LEFT   JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t ON TRUE;
  
  SELECT *
  FROM   tbl t, LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t;
  

  这不是:

  SELECT *
  FROM   tbl t
  LEFT   JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t;

  这就是为什么 @Andomar的代码示例正确(CROSS JOIN不需要连接条件)和 @Attila的 是无效.

  That's why @Andomar's code example is correct (the CROSS JOIN does not require a join condition) and @Attila's is was invalid.

  这篇关于LATERAL和PostgreSQL中的子查询有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！