getting - ¿Por qué el acceso a la matriz PostgreSQL es mucho más rápido en C que en PL/pgSQL?

postgresql getting started

Question

Tengo un esquema de tabla que incluye una columna de matriz int, y una función agregada personalizada que resume el contenido de la matriz. En otras palabras, dado lo siguiente:

CREATE TABLE foo (stuff INT[]);

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

Necesito una función de "suma" que devolvería { 5, 7, 9 } . La versión PL / pgSQL, que funciona correctamente, es la siguiente:

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;

Junto con:

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

Con un conjunto de datos de aproximadamente 150,000 filas, SELECT SUM(stuff) tarda más de 15 segundos en completarse.

Luego reescribí esta función en C, de la siguiente manera:

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

Esta versión tarda solo 800 ms en completarse, lo cual es ... mucho mejor.

(Convertido a una extensión independiente aquí: https://github.com/ringerc/scrapcode/tree/master/postgresql/array_sum )

Mi pregunta es, ¿por qué la versión C es mucho más rápida? Esperaba una mejora, pero 20x parece un poco demasiado. ¿Que esta pasando? ¿Hay algo intrínsecamente lento en el acceso a matrices en PL / pgSQL?

Estoy ejecutando PostgreSQL 9.0.2, en Fedora Core 8 de 64 bits. La máquina es una instancia de EC2 cuádruple extra grande de memoria alta.

Answer 1

¿Por qué?

¿Por qué la versión C es mucho más rápida?

Una matriz PostgreSQL es en sí misma una estructura de datos bastante ineficiente. Puede contener cualquier tipo de datos y es capaz de ser multidimensional, por lo que no es posible realizar muchas optimizaciones. Sin embargo, como has visto, es posible trabajar con la misma matriz mucho más rápido en C.

Esto se debe a que el acceso a la matriz en C puede evitar gran parte del trabajo repetido involucrado en el acceso a la matriz PL / PgSQL. Simplemente eche un vistazo a src/backend/utils/adt/arrayfuncs.c , array_ref . Ahora mire cómo se invoca desde src/backend/executor/execQual.c en ExecEvalArrayRef . Que se ejecuta para cada acceso de matriz individual desde PL / PgSQL, como puede ver al adjuntar gdb al pid encontrado en select pg_backend_pid() , establecer un punto de interrupción en ExecEvalArrayRef , continuar y ejecutar su función.

Más importante aún, en PL / PgSQL cada sentencia que ejecuta se ejecuta a través de la maquinaria del ejecutor de consultas. Esto hace que las declaraciones pequeñas y baratas sean bastante lentas, incluso teniendo en cuenta el hecho de que están preparadas previamente. Algo como:

a := b + c

En realidad es ejecutado por PL / PgSQL más como:

SELECT b + c INTO a;

Puede observar esto si hace que los niveles de depuración sean lo suficientemente altos, adjunte un depurador y rompa en un punto adecuado, o use el módulo auto_explain con análisis de declaraciones anidadas. Para darle una idea de la sobrecarga que esto impone cuando está ejecutando un montón de pequeñas declaraciones simples (como accesos a matrices), eche un vistazo a este ejemplo de seguimiento y mis notas al respecto.

También hay una sobrecarga de inicio significativa para cada invocación de la función PL / PgSQL. No es enorme, pero es suficiente para sumar cuando se usa como un agregado.

Un enfoque más rápido en C

En su caso, probablemente lo haría en C, como lo ha hecho usted, pero evitaría copiar la matriz cuando se le llame como un agregado. Puede verificar si se está invocando en el contexto agregado :

if (AggCheckCallContext(fcinfo, NULL))

y si es así, use el valor original como un marcador de posición mutable, modifíquelo y luego devuélvalo en lugar de asignar uno nuevo. Escribiré una demostración para verificar que esto es posible con arreglos en breve ... (actualización) o no tan pronto, olvidé lo absolutamente horrible que es trabajar con arreglos de PostgreSQL en C. Aquí vamos:

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

y agregue esto 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);

(más correctamente, crearía intarray--1.1.sql e intarray--1.0--1.1.sql y actualizar intarray.control . Esto es solo un truco rápido).

Utilizar:

make USE_PGXS=1
make USE_PGXS=1 install

para compilar e instalar.

Ahora DROP EXTENSION intarray; (si ya lo tienes) y CREATE EXTENSION intarray; .

Ahora tendrá la función agregada sum_intarray_cols disponible para usted (como su sum(int4[]) , así como la función de dos add_intarray_cols (como su array_add ).

Al especializarse en matrices de enteros, desaparece toda una serie de complejidad. Se evita un montón de copias en el caso agregado, ya que podemos modificar de forma segura la matriz de "estado" (el primer argumento) en el lugar. Para mantener la coherencia, en el caso de invocación no agregada, obtenemos una copia del primer argumento, por lo que aún podemos trabajar con él en el lugar y devolverlo.

Este enfoque podría generalizarse para admitir cualquier tipo de datos utilizando el caché fmgr para buscar la función de agregar para el tipo (s) de interés, etc. No estoy particularmente interesado en hacerlo, así que si lo necesita (por ejemplo, para sumar las columnas de matrices NUMERIC ) entonces ... diviértanse.

De manera similar, si necesita manejar longitudes de arreglos diferentes, probablemente pueda averiguar qué hacer a partir de lo anterior.

Answer 2

PL / pgSQL sobresale como pegamento del lado del servidor para elementos SQL. Los elementos de procedimiento y muchas asignaciones no están entre sus puntos fuertes. Las asignaciones, las pruebas o los ciclos son comparativamente costosos y solo están garantizados si ayudan a tomar atajos que uno no podría lograr solo con SQL. La misma lógica implementada en C siempre será más rápida, pero parece que eres muy consciente de eso ...

La mayoría de las veces, las soluciones de SQL puro son más rápidas. ¿Puede comparar esta solución simple y equivalente con la configuración de su prueba?

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

Puede envolver esto en una función SQL simple o, para un mejor rendimiento, integrarlo directamente en su gran consulta . Me gusta esto:

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;

Tenga en cuenta que las funciones de configuración de retorno solo funcionan en paralelo en un SELECT si el número de filas devueltas es idéntico. Es decir: funciona solo para matrices de igual longitud .

También sería una buena idea ejecutar la prueba con una versión actual de PostgreSQL. 9.0 es una versión particularmente impopular que casi nadie usa (más). Eso es aún más cierto para la versión 9.0.2 .

Debe al menos actualizar a la última versión de punto (9.0.15 atm.) O, mejor aún, a la versión actual 9.3.2 para obtener muchos errores importantes y correcciones de seguridad . Podría ser parte de la explicación de la gran diferencia en el rendimiento.

Postgres 9.4

• Mejoras de rendimiento para el manejo de matrices.

Y hay una solución más limpia para desincrustar en paralelo ahora:

• Unnest múltiples matrices en paralelo