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

I have a table schema which includes an int array column, and a custom aggregate function which sums the array contents. In other words, given the following:

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

CREATE TABLE foo (stuff INT[]);

INSERT INTO foo VALUES ({ 1, 2, 3 });
INSERT INTO foo VALUES ({ 4, 5, 6 });

I need a "sum" function that would return { 5, 7, 9 }. The PL/pgSQL version, which works correctly, is as follows:

我需要一个返回{5,7,9}的“sum”函数。正确运行的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;

Coupled with:

加上：

CREATE AGGREGATE sum (int[])
(
    sfunc = array_add,
    stype = int[]
);

With a data set of about 150,000 rows, SELECT SUM(stuff) takes over 15 seconds to complete.

对于大约150,000行的数据集，SELECT SUM（stuff）需要15秒才能完成。

I then re-wrote this function in C, as follows:

然后我在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, arrayElementType1, arrayElementTypeWidth1, arrayElementTypeByValue1, arrayElementTypeAlignmentCode1);

  // Return the final PostgreSQL array object.
  PG_RETURN_ARRAYTYPE_P(resultArray);
}

This version takes only 800 ms to complete, which is.... much better.

这个版本只需要800毫秒即可完成，这要好得多。

(Converted to a stand-alone extension here: https://github.com/ringerc/scrapcode/tree/master/postgresql/array_sum)

（转换为独立扩展程序：https：//github.com/ringerc/scrapcode/tree/master/postgresql/array_sum）

My question is, why is the C version so much faster? I expected an improvement, but 20x seems a bit much. What's going on? Is there something inherently slow about accessing arrays in PL/pgSQL?

我的问题是，为什么C版本更快？我预计会有所改善，但20倍似乎有点多。这是怎么回事？在PL / pgSQL中访问数组有什么本质上的缓慢？

I'm running PostgreSQL 9.0.2, on Fedora Core 8 64-bit. The machine is a High-Memory Quadruple Extra-Large EC2 instance.

我在Fedora Core 8 64位上运行PostgreSQL 9.0.2。该机器是高内存四倍超大型EC2实例。

2 个解决方案

#1

Why?

why is the C version so much faster?

为什么C版这么快？

A PostgreSQL array is its self a pretty inefficient data structure. It can contain any data type and it's capable of being multi-dimensional, so lots of optimisations are just not possible. However, as you've seen it's possible to work with the same array much faster in C.

PostgreSQL数组本身就是一种非常低效的数据结构。它可以包含任何数据类型，并且它可以是多维的，因此很多优化都是不可能的。但是，正如您所见，可以在C中更快地使用相同的数组。

That's because array access in C can avoid a lot of the repeated work involved in PL/PgSQL array access. Just take a look at src/backend/utils/adt/arrayfuncs.c, array_ref. Now look at how it's invoked from src/backend/executor/execQual.c in ExecEvalArrayRef. Which runs for each individual array access from PL/PgSQL, as you can see by attaching gdb to the pid found from select pg_backend_pid(), setting a breakpoint at ExecEvalArrayRef, continuing, and running your function.

那是因为C中的数组访问可以避免PL / PgSQL数组访问中涉及的大量重复工作。只需看一下src / backend / utils / adt / arrayfuncs.c，array_ref。现在看看它是如何从ExecEvalArrayRef中的src / backend / executor / execQual.c调用的。从PL / PgSQL中为每个单独的数组访问运行，正如您可以通过将gdb附加到select pg_backend_pid（）中找到的pid，在ExecEvalArrayRef中设置断点，继续并运行您的函数。

More importantly, in PL/PgSQL every statement you execute is run through the query executor machinery. This makes small, cheap statements fairly slow even allowing for the fact that they're pre-prepared. Something like:

更重要的是，在PL / PgSQL中，您执行的每个语句都通过查询执行程序机制运行。这使得小而廉价的陈述相当缓慢，甚至允许它们已经预先准备好了。就像是：

a := b + c

is actually executed by PL/PgSQL more like:

实际上是由PL / PgSQL执行的更像是：

SELECT b + c INTO a;

You can observe this if you turn debug levels high enough, attach a debugger and break at a suitable point, or use the auto_explain module with nested statement analysis. To give you an idea of how much overhead this imposes when you're running lots of tiny simple statements (like array accesses), take a look at this example backtrace and my notes on it.

如果您将调试级别调高到足够高，附加调试器并在适当的位置中断，或者使用带有嵌套语句分析的auto_explain模块，则可以观察到此情况。为了让您了解当您运行许多微小的简单语句（如数组访问）时，这会产生多少开销，请查看此示例的回溯和我的注释。

There is also a significant start-up overhead to each PL/PgSQL function invocation. It isn't huge, but it's enough to add up when it's being used as an aggregate.

每个PL / PgSQL函数调用也有很大的启动开销。它并不大，但它足以在它被用作聚合时加起来。

A faster approach in C

In your case I would probably do it in C, as you have done, but I'd avoid copying the array when called as an aggregate. You can check for whether it's being invoked in aggregate context:

在你的情况下，我可能会像你一样在C中完成它，但是当我作为聚合调用时，我会避免复制数组。您可以检查是否在聚合上下文中调用它：

if (AggCheckCallContext(fcinfo, NULL))

and if so, use the original value as a mutable placeholder, modifying it then returning it instead of allocating a new one. I'll write a demo to verify that this is possible with arrays shortly... (update) or not-so-shortly, I forgot how absolute horrible working with PostgreSQL arrays in C is. Here we go:

如果是这样，请将原始值用作可变占位符，修改它然后返回它而不是分配新值。我将编写一个演示来验证这是否可以使用数组...（更新）或不那么 - 很快，我忘了在C中使用PostgreSQL数组是多么绝对可怕。开始了：

// 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);
}

and append this to contrib/intarray/intarray--1.0.sql:

并将其附加到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);

(more correctly you'd create intarray--1.1.sql and intarray--1.0--1.1.sql and update intarray.control. This is just a quick hack.)

（更准确地说，你创建intarray - 1.1.sql和intarray - 1.0--1.1.sql并更新intarray.control。这只是一个快速的黑客攻击。）

Use:

使用：

make USE_PGXS=1
make USE_PGXS=1 install

to compile and install.

编译和安装。

Now DROP EXTENSION intarray; (if you already have it) and CREATE EXTENSION intarray;.

现在DROP EXTENSION intarray; （如果你已经拥有它）和CREATE EXTENSION intarray;

You'll now have the aggregate function sum_intarray_cols available to you (like your sum(int4[]), as well as the two-operand add_intarray_cols (like your array_add).

您现在可以使用聚合函数sum_intarray_cols（如sum（int4 []），以及双操作数add_intarray_cols（如array_add）。

By specializing in integer arrays a whole bunch of complexity goes away. A bunch of copying is avoided in the aggregate case, since we can safely modify the "state" array (the first argument) in-place. To keep things consistent, in the case of non-aggregate invocation we get a copy of the first argument so we can still work with it in-place and return it.

通过专注于整数数组，一大堆复杂性消失了。在聚合的情况下避免了一堆复制，因为我们可以安全地修改“状态”数组（第一个参数）。为了保持一致，在非聚合调用的情况下，我们得到第一个参数的副本，这样我们仍然可以就地使用它并返回它。

This approach could be generalised to support any data type by using the fmgr cache to look up the add function for the type(s) of interest, etc. I'm not particularly interested in doing that, so if you need it (say, to sum columns of NUMERIC arrays) then ... have fun.

这种方法可以推广到支持任何数据类型，方法是使用fmgr缓存来查找感兴趣的类型的添加函数等。我对此并不特别感兴趣，所以如果你需要它（比方说，总结NUMERIC数组的列然后......玩得开心。

Similarly, if you need to handle dissimilar array lengths, you can probably work out what to do from the above.

同样，如果您需要处理不同的数组长度，您可以从上面的方法中找出解决方法。

#2

PL/pgSQL excels as server-side glue for SQL elements. Procedural elements and lots of assignments are not among its strengths. Assignments, tests or looping are comparatively expensive and only warranted if they help to take shortcuts one could not achieve with just SQL. The same logic implemented in C will always be faster, but you seem to be well aware of that ...

PL / pgSQL擅长作为SQL元素的服务器端粘合剂。程序要素和许多任务不属于其优势。分配，测试或循环比较昂贵，只有在它们有助于采用仅用SQL无法实现的快捷方式时才有必要。在C中实现的相同逻辑总是会更快，但你似乎很清楚......

Most of the time, pure SQL solutions are faster. Can you compare this simple, equivalent solution with your test setup?

大多数时候，纯SQL解决方案更快。你可以将这个简单的等效解决方案与测试设置进行比较吗？

SELECT array_agg(a + b)
FROM  (
   SELECT unnest('{1, 2, 3 }'::int[]) AS a
         ,unnest('{4, 5, 6 }'::int[]) AS b
   ) x

You can wrap this into a simple SQL function or, for better performance, integrate it directly into your big query. Like this:

您可以将其包装到一个简单的SQL函数中，或者为了获得更好的性能，可以将它直接集成到您的大查询中。喜欢这个：

SELECT tbl_id, array_agg(a + b)
FROM  (
   SELECT tbl_id
         ,unnest(array1) AS a
         ,unnest(array2) AS b
   FROM   tbl
   ORDER  BY tbl_id
   ) x
GROUP  BY tbl_id;

Note, that set returning functions only work in parallel in a SELECT if the number of returned rows is identical. I.e.: works only for arrays of equal length.

注意，如果返回的行数相同，则设置返回函数仅在SELECT中并行工作。即：仅适用于相同长度的阵列。

It would also be a good idea to run the test with a current version PostgreSQL. 9.0 is a particularly unpopular release that hardly anybody uses (any more). That's even more true for the hopelessly outdated point release 9.0.2.

使用当前版本的PostgreSQL运行测试也是一个好主意。 9.0是一个特别不受欢迎的版本，几乎没有人使用（任何更多）。对于毫无希望的过时版本9.0.2，情况更是如此。

You must at least update to the last point release (9.0.15 atm.) or, better yet, to the current version 9.3.2 to get many important bug and security fixes. Might be part of the explanation for the big difference in performance.

您必须至少更新到最后一个版本（9.0.15 atm。），或者更好的是，更新到当前版本9.3.2以获得许多重要的错误和安全修复程序。可能是解释性能差异的一部分。

Postgres 9.4

• Performance improvements for array handling.
• 阵列处理的性能改进。

And there is a cleaner solution for unnesting in parallel now:

现在有一个更清晰的并行解决方案：

• Unnest multiple arrays in parallel
• 并联多个阵列