postgresql - 提高 PostgreSQL 中自定义聚合函数的性能

Question

我有接受 boolean 数据类型的自定义聚合求和函数:

create or replace function badd (bigint, boolean)
  returns bigint as
$body$
select $1 + case when $2 then 1 else 0 end;
$body$ language sql;

create aggregate sum(boolean) (
  sfunc=badd,
  stype=int8,
  initcond='0'
);

此聚合应计算具有 TRUE 的行数。例如，以下应返回 2(确实如此):

with t (x) as 
   (values 
      (true::boolean), 
      (false::boolean), 
      (true::boolean),
      (null::boolean)      
      )
select sum(x) from t;

但是，它的性能很差，比使用转换为整数慢 5.5 倍:

with t as (select (gs > 0.5) as test_vector from generate_series(1,1000000,1) gs)
select sum(test_vector) from t; -- 52012ms

with t as (select (gs > 0.5) as test_vector from generate_series(1,1000000,1) gs)
select sum(test_vector::int) from t; -- 9484ms

是改进此聚合以编写一些新的 C 函数的唯一方法 - 例如src/backend/utils/adt/numeric.c 中的 int2_sum 函数的替代方案？

Answer 1

您的测试用例具有误导性，您只计算TRUE。如果适用，您应该同时拥有 TRUE 和 FALSE - 或者甚至 NULL。

Like @foibs already explained ，人们不会为此使用自定义聚合函数。内置 C 函数的速度多并且可以完成这项工作。改用(也演示了一个更简单、更明智的测试):

SELECT count(NULLIF(g%2 = 1, FALSE)) AS ct
FROM  generate_series(1,100000,1) g;

这是如何运作的？
Compute percents from SUM() in the same SELECT sql query

在 dba.SE 上的这个相关答案下的几种快速简单的方法(加上基准):
For absolute performance, is SUM faster or COUNT?

或者更快，在 WHERE 子句中测试 TRUE，在可能的情况下:

SELECT count(*) AS ct
FROM   generate_series(1,100000,1) g;
WHERE  g%2 = 1             -- excludes FALSE and NULL !

---

如果您出于某种原因必须编写自定义聚合，那么这种形式会更好:

CREATE OR REPLACE FUNCTION test_sum_int8 (int8, boolean)
  RETURNS bigint as
'SELECT CASE WHEN $2 THEN $1 + 1 ELSE $1 END' LANGUAGE sql;

添加仅在必要时执行。您的原件将为 FALSE 案例添加 0。

更好的是，使用plpgsql 函数。它节省了每次调用的一些开销，因为它像准备好的语句一样工作(查询没有重新计划)。对调用多次 次的微小聚合函数产生影响:

CREATE OR REPLACE FUNCTION test_sum_plpgsql (int8, boolean)
  RETURNS bigint AS
$func$
BEGIN
RETURN CASE WHEN $2 THEN $1 + 1 ELSE $1 END;
END
$func$ LANGUAGE plpgsql;

CREATE AGGREGATE test_sum_plpgsql(boolean) (
  sfunc = test_sum_plpgsql
 ,stype = int8
 ,initcond = '0'
);

比您拥有的更快，但比使用标准 count() 的替代方案慢得多。而且也比任何其他 C 函数都慢。

->SQLfiddle