PostgreSQL 的 LATERAL JOIN 如何优化数组参数上的多个函数调用？

数组参数上多个函数调用的查询优化

在 Postgres 中，在数组参数上多次调用函数可能效率很低，特别是当函数返回多列时。让我们探索一种查询优化策略来应对这一挑战。

考虑一个函数 foo，它处理具有给定参数的行数组并返回一组行和一个新列：

CREATE OR REPLACE FUNCTION foo(data data[], parameter int) RETURNS SETOF enhanceddata AS
...

最初，该函数使用以下方法处理一组数据：

SELECT * FROM foo( (SELECT ARRAY_AGG(data) FROM datatable GROUP BY dataid WHERE dataid = something), 1) 

但是，目标是使其适用于多个组不指定 dataid 参数的数据。

一种方法涉及使用子查询将数据聚合到数组中，然后将其传递给 foo 函数：

SELECT dataid, (foo(ARRAY_AGG(data)),1).*
FROM dataset
WHERE dataid = something -- only testing on 1
GROUP BY dataid

虽然这看起来合乎逻辑，它存在多次调用 foo 的问题，每个数据行调用一次。

使用横向连接优化

要优化此查询，可以使用 PostgreSQl LATERAL JOIN。这种强大的技术在子查询的结果和另一个表的行之间创建基于行的笛卡尔积。在这种情况下，子查询将数据聚合到一个数组中，横向连接对聚合数组中的每一行执行 foo 一次。

使用 PostgreSQL 9.3 或更高版本，以下查询优化了多个函数调用：

SELECT sub.dataid, f.*
FROM  (
   SELECT dataid, array_agg(data) AS arr
   FROM   dataset
   WHERE  dataid = something
   GROUP  BY 1
   ) sub
LEFT   JOIN LATERAL foo(sub.arr) f ON true;

LEFT JOIN LATERAL 确保保留左表（子查询）中的所有行，即使没有返回任何行富。如果函数不能返回任何行，则这是首选语法。

对于始终返回结果的函数，可以使用简化语法：

CROSS JOIN LATERAL foo(sub.arr)

或其等效简写：

, foo(sub.arr)

正如 Postgres 手册中强调的那样，使用 LATERAL JOIN 可以通过 foo 等返回集合的函数来优化查询。该技术可以有效地处理多组数据，而不会产生不必要的函数评估。

以上是PostgreSQL 的 LATERAL JOIN 如何优化数组参数上的多个函数调用？的详细内容。更多信息请关注PHP中文网其他相关文章！