Skip to main content
Database Administrators

Return to Question

added 293 characters in body
Source Link
Riku Iki
Riku Iki
  • 11
  • 3
#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);
}

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..

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

    for (int i = 0; i < n; i++) {
        rd[i] = ad[i] + 1;
    }

    PG_RETURN_POINTER(r);
}

Last query runs in 6s. Removing rd[i] = ad[i] + 1; line makes code runs in 0.6s.

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

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..

added 2 characters in body
Source Link
Riku Iki
Riku Iki
  • 11
  • 3
ccgcc -fPIC -O2 -I/usr/include/postgresql/15/server -I/usr/include/postgresql/internal -c test.c
ccgcc -shared -o test.so test.o
/usr/bin/install -c -m 755  test.so '/usr/lib/postgresql/15/lib/'

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

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/'
Source Link
Riku Iki
Riku Iki
  • 11
  • 3

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

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

    for (int i = 0; i < n; i++) {
        rd[i] = ad[i] + 1;
    }

    PG_RETURN_POINTER(r);
}

I compile and install it this way:

cc -fPIC -O2 -I/usr/include/postgresql/15/server -I/usr/include/postgresql/internal -c test.c
cc -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.

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