Почему массив PostgreSQL намного быстрее работает в C, чем в PL/pgSQL?

Ответ 1

Почему?

почему версия C намного быстрее?

Массив PostgreSQL - это неэффективная структура данных. Он может содержать любой тип данных и способен быть многомерным, поэтому множество оптимизаций просто невозможно. Однако, как вы видели, возможно работать с тем же массивом намного быстрее в C.

Что, поскольку доступ к массиву в C может избежать много повторной работы, связанной с доступом к массиву PL/PgSQL. Просто взгляните на src/backend/utils/adt/arrayfuncs.c, array_ref. Теперь посмотрим, как он вызывается из src/backend/executor/execQual.c в ExecEvalArrayRef. Что выполняется для каждого отдельного доступа к массиву из PL/PgSQL, как вы можете видеть, добавив gdb к pid, найденному из select pg_backend_pid(), установив точку останова в ExecEvalArrayRef, продолжая и запуская свою функцию.

Что еще более важно, в PL/PgSQL каждое выполняемое вами выражение запускается через механизм выполнения запросов. Это делает небольшие, дешевые заявления довольно медленными, даже учитывая тот факт, что они подготовлены заранее. Что-то вроде:

a := b + c

фактически выполняется PL/PgSQL, как:

SELECT b + c INTO a;

Это можно наблюдать, если вы достаточно высоко увеличиваете уровни отладки, присоединяете отладчик и ломаете его в подходящей точке или используете модуль auto_explain с анализом вложенных операторов. Чтобы дать вам представление о том, сколько накладных расходов это накладывает, когда вы запускаете множество простых простых операторов (например, обращения к массиву), посмотрите этот пример backtrace и мои заметки об этом.

Кроме того, для каждого вызова функции PL/PgSQL существует значительный накладной старт-ап. Он не огромен, но его достаточно, чтобы его добавить, когда он используется как совокупность.

Более быстрый подход в C

В вашем случае я, вероятно, сделаю это в C, как вы это делали, но я бы не стал копировать массив при вызове как совокупность. Вы можете проверить, вызвана ли она в совокупном контексте:

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), тогда... получайте удовольствие.

Аналогично, если вам нужно обрабатывать разнородные длины массивов, вы, вероятно, можете решить, что делать из вышеперечисленного.

Ответ 2

PL/pgSQL превосходит серверный клей для элементов SQL. Процедурные элементы и множество заданий не входят в число его сильных сторон. Задания, тесты или цикл являются сравнительно дорогими и гарантируются только в том случае, если они помогают использовать ярлыки, которых невозможно достичь только с помощью SQL. Та же логика, реализованная на C, всегда будет быстрее, но вы, кажется, хорошо знаете об этом...

В большинстве случаев решения чистого SQL выполняются быстрее. Можете ли вы сравнить это простое, эквивалентное решение с вашей тестовой настройкой?

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

Вы можете обернуть это в простую функцию 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;

Обратите внимание, что набор возвращаемых функций работает только параллельно в SELECT, если количество возвращаемых строк идентично. I.e.: работает только для массивов равной длины.

Было бы неплохо запустить тест с текущей версией PostgreSQL. 9.0 является особенно непопулярным релизом, который вряд ли кто-либо использует (не более). Это еще более справедливо для безнадежно устаревшего точечного релиза 9.0.2.

Вы должны, по крайней мере, обновить последний выпуск (9.0.15 atm.) или, что еще лучше, до текущей версии 9.3.2, чтобы получить множество важных ошибок и исправлений безопасности. Может быть частью объяснения большой разницы в производительности.

Postgres 9.4

• Улучшения производительности для обработки массивов.

И теперь есть более чистое решение для параллельной проверки:

• Параллельный просмотр нескольких массивов