自从 Postgres 推出LATERAL连接功能以来,我一直在阅读它,因为我目前正在为我的团队进行复杂的数据转储,其中包含许多低效的子查询,这使得整个查询需要四分钟或更长时间。
LATERAL
我知道LATERALjoin 可能会对我有所帮助,但即使在阅读了 Heap Analytics 中的这篇文章之后,我仍然不太了解。
联接的用例是LATERAL什么?联接和子查询有什么区别LATERAL?
该功能是在 PostgreSQL 9.3 中引入的。手册:
出现的子查询FROM前面可以有关键字 LATERAL。这允许他们引用前面 FROM项目提供的列。(没有LATERAL,每个子查询都是独立评估的,因此不能交叉引用任何其他FROM项目。) 出现的表函数FROM前面也可以有关键字LATERAL,但是对于函数,关键字是可选的;FROM在任何情况下,函数的参数都可以包含对前面项目提供的列的引用。
出现的子查询FROM前面可以有关键字 LATERAL。这允许他们引用前面 FROM项目提供的列。(没有LATERAL,每个子查询都是独立评估的,因此不能交叉引用任何其他FROM项目。)
FROM
出现的表函数FROM前面也可以有关键字LATERAL,但是对于函数,关键字是可选的;FROM在任何情况下,函数的参数都可以包含对前面项目提供的列的引用。
那里给出了基本的代码示例。
连接LATERAL更像是相关子查询,而不是普通子查询,因为LATERAL连接右侧的表达式对其左侧的每一行都被评估一次 - 就像 相关 子查询一样 - 而普通子查询(表表达式)被评估 一次 只要。(不过,查询规划器有办法优化两者的性能。)
连接可以做一些 事情,LATERAL但(相关的)子查询不能(轻松地)。相关的子查询只能返回一个值,而不是多列和多行 - 除了裸函数调用(如果它们返回多行,则将结果行相乘)。但即使是某些集合——返回函数也只允许在FROM子句中。就像unnest()Postgres 9.4 或更高版本中的多个参数一样。手册:
unnest()
这仅在FROM子句中允许;
所以这可行,但不能(容易)用子查询替换:
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 [, ... ] ):
,
CROSS JOIN
UNNEST( array_expression [, ... ] )
SELECT
您也可以直接使用列表unnest()中的集合返回函数SELECT。SELECT在Postgres 9.6 之前的同一个列表中,这曾经表现出令人惊讶的行为,其中不止一个这样的函数。但它终于用 Postgres 10 进行了清理,现在是一个有效的替代方案(即使不是标准 SQL)。
基于上面的例子:
SELECT *, unnest(a1) AS elem1, unnest(a2) AS elem2 FROM tbl;
比较:
第 9.6 页的 dbfiddle 此处 为 pg 10 的 dbfiddle
手册:
对于INNERandOUTER连接类型,必须指定连接条件,即恰好是NATURAL、ON join_condition 或USING( join_column [, …]) 之一。含义见下文。 对于CROSS JOIN,这些子句都不能出现。
INNER
OUTER
NATURAL
ON
USING
所以这两个查询是有效的(即使不是特别有用):
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;~~