CREATE TABLE foo (stuff INT[]); INSERT INTO foo VALUES ({ 1,2,3 }); INSERT INTO foo VALUES ({ 4,5,6 });
我需要一个“sum”函数,返回{5,7,9}。 PL / pgsql版本正常工作如下:
CREATE OR REPLACE FUNCTION array_add(array1 int[],array2 int[]) RETURNS int[] AS $$ DECLARE result int[] := ARRAY[]::integer[]; l int; BEGIN --- --- First check if either input is NULL,and return the other if it is --- IF array1 IS NULL OR array1 = '{}' THEN RETURN array2; ELSEIF array2 IS NULL OR array2 = '{}' THEN RETURN array1; END IF; l := array_upper(array2,1); SELECT array_agg(array1[i] + array2[i]) FROM generate_series(1,l) i INTO result; RETURN result; END; $$ LANGUAGE plpgsql;
加上:
CREATE AGGREGATE sum (int[]) ( sfunc = array_add,stype = int[] );
使用大约15万行的数据集,SELECT SUM(东西)需要15秒才能完成。
然后我在C中重写了这个函数,如下所示:
#include <postgres.h> #include <fmgr.h> #include <utils/array.h> Datum array_add(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(array_add); /** * Returns the sum of two int arrays. */ Datum array_add(PG_FUNCTION_ARGS) { // The formal Postgresql array objects: ArrayType *array1,*array2; // The array element types (should always be INT4OID): Oid arrayElementType1,arrayElementType2; // The array element type widths (should always be 4): int16 arrayElementTypeWidth1,arrayElementTypeWidth2; // The array element type "is passed by value" flags (not used,should always be true): bool arrayElementTypeByValue1,arrayElementTypeByValue2; // The array element type alignment codes (not used): char arrayElementTypeAlignmentCode1,arrayElementTypeAlignmentCode2; // The array contents,as Postgresql "datum" objects: Datum *arrayContent1,*arrayContent2; // List of "is null" flags for the array contents: bool *arrayNullFlags1,*arrayNullFlags2; // The size of each array: int arrayLength1,arrayLength2; Datum* sumContent; int i; ArrayType* resultArray; // Extract the Postgresql arrays from the parameters passed to this function call. array1 = PG_GETARG_ARRAYTYPE_P(0); array2 = PG_GETARG_ARRAYTYPE_P(1); // Determine the array element types. arrayElementType1 = ARR_ELEMTYPE(array1); get_typlenbyvalalign(arrayElementType1,&arrayElementTypeWidth1,&arrayElementTypeByValue1,&arrayElementTypeAlignmentCode1); arrayElementType2 = ARR_ELEMTYPE(array2); get_typlenbyvalalign(arrayElementType2,&arrayElementTypeWidth2,&arrayElementTypeByValue2,&arrayElementTypeAlignmentCode2); // Extract the array contents (as Datum objects). deconstruct_array(array1,arrayElementType1,arrayElementTypeWidth1,arrayElementTypeByValue1,arrayElementTypeAlignmentCode1,&arrayContent1,&arrayNullFlags1,&arrayLength1); deconstruct_array(array2,arrayElementType2,arrayElementTypeWidth2,arrayElementTypeByValue2,arrayElementTypeAlignmentCode2,&arrayContent2,&arrayNullFlags2,&arrayLength2); // Create a new array of sum results (as Datum objects). sumContent = palloc(sizeof(Datum) * arrayLength1); // Generate the sums. for (i = 0; i < arrayLength1; i++) { sumContent[i] = arrayContent1[i] + arrayContent2[i]; } // Wrap the sums in a new Postgresql array object. resultArray = construct_array(sumContent,arrayLength1,arrayElementTypeAlignmentCode1); // Return the final Postgresql array object. PG_RETURN_ARRAYTYPE_P(resultArray); }
这个版本只需要800毫秒才能完成,这是更好的。
(转换为独立扩展名:https://github.com/ringerc/scrapcode/tree/master/postgresql/array_sum)
我的问题是,为什么C版本这么快?我预计会有所改善,但是20x似乎有点多了。这是怎么回事?在PL / pgsql中访问数组有什么本质上很慢的东西吗?
我在运行Postgresql 9.0.2,Fedora Core 8 64位。该机器是高内存四重超大EC2实例。
why is the C version so much faster?
Postgresql数组是一个非常低效的数据结构。它可以包含任何数据类型,它能够是多维的,所以很多优化是不可能的。然而,正如你所看到的那样,可以在C中更快地处理相同的数组
这是因为C中的数组访问可以避免PL / Pgsql数组访问中涉及的大量重复工作。只需看看src / backend / utils / adt / arrayfuncs.c,array_ref。现在来看看在ExecEvalArrayRef中如何从src / backend / executor / execQual.c调用它。从PL / Pgsql的每个单独数组访问运行,您可以通过将gdb附加到从pg_backend_pid()中找到的pid,在ExecEvalArrayRef中设置断点,继续运行和运行功能,可以看到。
更重要的是,在PL / Pgsql中,您执行的每个语句都通过查询执行器机器运行。这使得小而便宜的语句相当缓慢,甚至允许事先准备好。就像是:
a := b + c
实际上由PL / Pgsql执行更像:
SELECT b + c INTO a;
如果您调试级别足够高,附加调试器并在适当的位置断开,或者使用带有嵌套语句分析的auto_explain模块,您可以观察到这一点。给你一个想法,当你运行大量的简单的语句(如数组访问)时,它施加了多少开销,看看this example backtrace和我的笔记。
每个PL / Pgsql函数调用还有一个重要的启动开销。它不是巨大的,但是当它被用作一个聚合就足够了。
一个更快的方法在C
在你的情况下,我可能会在C中做到这一点,就像你所做的一样,但是当被调用为聚合时,我会避免复制数组。你可以check for whether it’s being invoked in aggregate context:
if (AggCheckCallContext(fcinfo,NULL))
如果是这样,使用原始值作为可变占位符,修改它然后返回它而不是分配一个新的。我会写一个演示,以验证这是可能的数组很快…(更新)或不 – 很快,我忘记了如何绝对可怕的工作与Postgresql数组在C。开始了:
// append to contrib/intarray/_int_op.c PG_FUNCTION_INFO_V1(add_intarray_cols); Datum add_intarray_cols(PG_FUNCTION_ARGS); Datum add_intarray_cols(PG_FUNCTION_ARGS) { ArrayType *a,*b; int i,n; int *da,*db; if (PG_ARGISNULL(1)) ereport(ERROR,(errmsg("Second operand must be non-null"))); b = PG_GETARG_ARRAYTYPE_P(1); CHECKARRVALID(b); if (AggCheckCallContext(fcinfo,NULL)) { // Called in aggregate context... if (PG_ARGISNULL(0)) // ... for the first time in a run,so the state in the 1st // argument is null. Create a state-holder array by copying the // second input array and return it. PG_RETURN_POINTER(copy_intArrayType(b)); else // ... for a later invocation in the same run,so we'll modify // the state array directly. a = PG_GETARG_ARRAYTYPE_P(0); } else { // Not in aggregate context if (PG_ARGISNULL(0)) ereport(ERROR,(errmsg("First operand must be non-null"))); // Copy 'a' for our result. We'll then add 'b' to it. a = PG_GETARG_ARRAYTYPE_P_COPY(0); CHECKARRVALID(a); } // This requirement could probably be lifted pretty easily: if (ARR_NDIM(a) != 1 || ARR_NDIM(b) != 1) ereport(ERROR,(errmsg("One-dimesional arrays are required"))); // ... as could this by assuming the un-even ends are zero,but it'd be a // little ickier. n = (ARR_DIMS(a))[0]; if (n != (ARR_DIMS(b))[0]) ereport(ERROR,(errmsg("Arrays are of different lengths"))); da = ARRPTR(a); db = ARRPTR(b); for (i = 0; i < n; i++) { // Fails to check for integer overflow. You should add that. *da = *da + *db; da++; db++; } PG_RETURN_POINTER(a); }
并附加到contrib / intarray / intarray – 1.0.sql:
CREATE FUNCTION add_intarray_cols(_int4,_int4) RETURNS _int4 AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE; CREATE AGGREGATE sum_intarray_cols(_int4) (sfunc = add_intarray_cols,stype=_int4);
(更准确地说,您将创建intarray – 1.1.sql和intarray – 1.0–1.1.sql并更新intarray.control。这只是一个快速的黑客。)
使用:
make USE_PGXS=1 make USE_PGXS=1 install
编译安装。
现在DROP EXTENSION intarray; (如果你已经有了)和CREATE EXTENSION intarray ;.
现在,您可以使用sum_intarray_cols的汇总函数(如您的sum(int4 [])以及双操作数add_intarray_cols(如array_add)。
通过专门研究整数数组,一大堆复杂性消失了。在集合的情况下,避免了一堆复制,因为我们可以就地安全地修改“state”数组(第一个参数)。为了使事情保持一致,在非总体调用的情况下,我们得到第一个参数的副本,以便我们仍然可以就地使用它并返回。
这种方法可以推广到支持任何数据类型,通过使用fmgr缓存查找感兴趣的类型的添加函数等。我并不特别感兴趣,所以如果你需要它(比如说,总结列数NUMERIC数组)然后…有乐趣。
同样的,如果你需要处理不同的数组长度,你可以从上面说明做什么。