为什么PostgreSQL数组在C中比PL/pgSQL中的访问速度要快得多？

发布时间：2020-12-13 16:35:32 所属栏目：百科来源：网络整理

导读：我有一个表模式，其中包含一个int数组列和一个自定义聚合函数，它将数组内容相加。换句话说，给出如下： CREATE TABLE foo (stuff INT[]);INSERT INTO foo VALUES ({ 1,2,3 });INSERT INTO foo VALUES ({ 4,5,6 }); 我需要一个“sum”函数，返回{5,7,9}。 PL

我有一个表模式，其中包含一个int数组列和一个自定义聚合函数，它将数组内容相加。换句话说，给出如下：

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数组)然后…有乐趣。

同样的，如果你需要处理不同的数组长度，你可以从上面说明做什么。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!