arrays - 确保通过合并 2 个 bigint 数组创建数组中的唯一元素

Question

确保通过合并其他 2 个 bigint 数组创建的 bigint 数组中值的唯一性的最有效方法是什么？

例如这个操作select ARRAY[1,2] || ARRAY[2, 3] 的结果应该是 1,2,3。我检查了扩展intarray并看到它不适用于 bigint。

Answer 1

由于您要求高效 - 可以优化功能:

您的问题中没有任何内容需要排序输出。所以:

CREATE OR REPLACE FUNCTION f_merge_uniq(bigint[], bigint[])
  RETURNS bigint[] AS
$func$
SELECT ARRAY(
  SELECT unnest($1)
  UNION
  SELECT unnest($2)
   )
$func$  LANGUAGE sql IMMUTABLE;

但您也可以对其进行排序，而且几乎不会增加成本:

CREATE OR REPLACE FUNCTION f_merge_uniq_sort(bigint[], bigint[])
  RETURNS bigint[] AS
$func$
SELECT ARRAY(
   SELECT DISTINCT x
   FROM (
      SELECT unnest($1)
      UNION ALL 
      SELECT unnest($2)
      ) sub(x)
   ORDER BY 1
   )
$func$  LANGUAGE sql IMMUTABLE;

db<> fiddle here

由于多种原因，计划和执行接近两倍:

• 如果您UNION 和稍后 ORDER BY，Postgres 会做额外的工作。使用 (array_agg(x order by x)) 对每个结果行进行排序是最坏的情况。对于一个结果行(如本例)，它甚至更慢，因为有更多的开销。在子查询中排序(如果可能)通常更有效:

  • Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

• DISTINCT 操作可以立即基于排序。我的第二个函数 f_merge_uniq_sort() 实现了这一点，因此它实际上与第一个 f_merge_uniq() 一样快，尽管它返回排序后的数组。 (在我使用 Postgres 12 的测试中似乎更快了一点!似乎 UNION 的效率略低于 DISTINCT。)

• ARRAY 构造函数比 array_agg() 更快。​​

  • Why is array_agg() slower than the non-aggregate ARRAY() constructor?