arrays - Postgres : How to use arrays as stored procedure parameters efficiently?

Question

我需要创建一个 Postgres 9.1 PL/pgSQL 存储过程，除其他参数外，它采用一系列值，这些值直接引用我的一个数据库列中的值。据我所知，在 Postgres 中执行此操作的规范方法是 array .

当然，这是一项相当基本的任务。我的问题是可伸缩性:我的代码基本上可以正常工作，但一旦传入的序列变大(如数百或数千个值)，性能就会很差:

在我的存储过程中使用表单中的数组甚至是相当简单的 SELECT 语句

SELECT <some columns>
FROM   <some tables>
WHERE  <some other select criteria>
AND    <column with values selected by array parameter>
         IN (SELECT * FROM unnest(<array parameter>))

即使数据库还不是很大并且数组中只有几十个值，也需要几秒钟的时间来执行。

我的第一个怀疑是 unnest(...) 是问题所在，但仅从包含数组参数中引用的列的表中进行选择确实很快:

SELECT <some columns>
FROM   <table with column ref'd in array parameter>
WHERE  <column with values selected by array parameter>
         IN (SELECT * FROM unnest(<array parameter>))

只需要几毫秒。

我的问题:

1. 是否可以使用数组作为参数？
2. 如何让我的查询执行得更好？

Answer 1

How can I make my queries perform better ?

如果您重写查询，我希望性能更快

SELECT <some columns>
FROM   <some tables>
WHERE  <some other select criteria>
AND    <column with values selected by array parameter>
         IN (SELECT * FROM unnest(<array parameter>));

到:

SELECT <some columns>
FROM   (SELECT unnest(<array parameter>) AS param) x
JOIN   <filtered table>  ON <filter column> = x.param
JOIN   <other table> ON <join criteria>
WHERE  <some other select criteria>;

这听起来像是查询规划器选择了一个次优计划，与 IN 子句相比，错误判断了您的其他 WHERE 条件的成本。通过将其转换为显式 JOIN 子句，您应该可以获得更好的查询计划。

通常，JOIN 往往比 PostgreSQL 中的大型 IN 子句更快。

---

Is there an alternative to using an array as parameter ?

是的。
您可以创建临时表，填充它并针对它运行查询连接。

CREATE TEMP TABLE x(id int);

INSERT INTO x VALUES
(1), (2), (17), (18);

SELECT <some columns>
FROM   x
JOIN   <filtered table>  ON <filter column> = x.id
JOIN   <other table> ON <join criteria>
WHERE  <some other select criteria>;

或者，更快，使用 CTE出于同样的目的:

WITH x(id) AS (
    VALUES (1::int), (2), (17), (18) -- type-cast on first element is enough
    )
SELECT <some columns>
FROM   x
JOIN   <filtered table>  ON <filter column> = x.id
JOIN   <other table> ON <join criteria>
WHERE  <some other select criteria>;

只要你想用函数，数组参数，unnested inside也是我的选择。您也可以在函数内部使用我上一个示例中的 CTE，只需使用 unnest(arr) 而不是 VALUES 子句。