PostgreSQL stored C function is 60 times slower than standalone C code

Question

I am trying to build stored function in C, which will take one array of bigint as argument, and return another array which is copy of parameter array but with all elements incremented by 1. My problem is that when I run this code as standalone program, it runs very fast: 0.1s for 10M elements, but it takes 6s when run as Postgresql C stored procedure.

I am wondering if someone can spot if I am doing anything obviously wrong?..

My C stored procedure:

#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"
#include "utils/array.h"
#include "catalog/pg_type.h"

PG_MODULE_MAGIC;

#define ARRPTR(x)  ( (int64 *) ARR_DATA_PTR(x) )
#define ARRNELEMS(x)  ArrayGetNItems(ARR_NDIM(x), ARR_DIMS(x))

PG_FUNCTION_INFO_V1(test_inc);
Datum
test_inc(PG_FUNCTION_ARGS)
{
    ArrayType  *a = PG_GETARG_ARRAYTYPE_P(0);
    int n = ARRNELEMS(a);
    int nbytes = ARR_OVERHEAD_NONULLS(1) + sizeof(int64) * n;

    ArrayType  *r = (ArrayType *) palloc0(nbytes);
    SET_VARSIZE(r, nbytes);
    ARR_NDIM(r) = 1;
    r->dataoffset = 0; // marker for no null bitmap
    ARR_ELEMTYPE(r) = INT8OID;
    ARR_DIMS(r)[0] = n;
    ARR_LBOUND(r)[0] = 1;

    int64 *ad = ARRPTR(a);
    int64 *rd = ARRPTR(r);

    ereport(WARNING,
        errcode(ERRCODE_WARNING),
        errmsg("Before loop"));
    for (int i = 0; i < n; i++) {
        rd[i] = ad[i] + 1;
    }
    ereport(WARNING,
        errcode(ERRCODE_WARNING),
        errmsg("After loop"));

    PG_RETURN_POINTER(r);
}

I compile and install it this way:

gcc -fPIC -O2 -I/usr/include/postgresql/15/server -I/usr/include/postgresql/internal -c test.c
gcc -shared -o test.so test.o
/usr/bin/install -c -m 755  test.so '/usr/lib/postgresql/15/lib/'

SQL code I use to test it:

CREATE FUNCTION test_inc(_int8) RETURNS _int8 AS '/usr/lib/postgresql/15/lib/test.so'
LANGUAGE C IMMUTABLE PARALLEL SAFE;

create table t as select generate_series(0, 10000000) n;
create table t2 as select array_agg(n order by n) n from t;
create table t3 as select test_inc(n) n from t2;

Last query runs in 6s. Removing rd[i] = ad[i] + 1; line makes code runs in 0.6s. Also, looking at warning messages I see that execution stuck somewhere after the loop, and not inside the loop..

My standalone C code looks like following and runs in 0.1s:

#include <stdio.h>
#include <stdlib.h>

int main(int argc, char ** argv) {
    long n = 10000000;
    long *a = (long*)malloc(n * sizeof(long));
    long *r = (long*)malloc(n * sizeof(long));
    for (int i = 0; i < n; i++) {
        a[i] = i;
    }
    for (int i = 0; i < n; i++) {
        r[i] = a[i] + 1;
    }
    // To make sure compiler does not remove previous loop because result is unused.
    long res = 0;
    for (int i = 0; i < n; i++) {
        res += r[i];
    }

    printf("%ld", res);
}

Answer 1

This is mostly about storage. If you just throw away the output (explain analyze select test_inc(n) n from t2;) rather than creating a table with it, it is much faster.

And in your dummy code, all the values are zero, which makes the output string highly compressible and compressing it faster than storing it. (30MB with real code vs 960kB with dummy code).

Experimenting further, the main slowness is not that the compression is not very good, but rather that it takes a long time to not be very good. Switching to lz4 makes it much faster, as does just turning off compression.