Re: Why overhead of SPI is so large?

From: Konstantin Knizhnik [mailto:k.knizhnik@postgrespro.ru]

PL/pgSQL:   29044.361 ms
C/SPI:          22785.597 ms

The fact that difference between PL/pgSQL and function implemented in C
using SPI is not so large  was expected by me.

This PL/pgSQL overhead is not so significant compared with the three times, but makes me desire some feature like Oracle's ALTER PROCEDURE ... COMPILE; that compiles the PL/SQL logic to native code.  I've seen a few dozen percent speed up.

Actually my implementation of C/SPI version is not optimal: it is better to fetch several records:

    while (true)
    {
        SPI_cursor_fetch(portal, true, 100);
        if (SPI_processed) {
            for (i = 0; i < SPI_processed; i++) {
                HeapTuple spi_tuple = SPI_tuptable->vals[i];
                Datum val = SPI_getbinval(spi_tuple, SPI_tuptable->tupdesc, 1, &is_null);
                double x = DatumGetFloat8(val);
                result += x*x;
                SPI_freetuple(spi_tuple);
            }
            SPI_freetuptable(SPI_tuptable);
        } else
            break;
    }

This version shows result 9405.694 ms which is comparable with result of SQL query.
Unfortunately (or fortunately) PL/pgSQL is already using prefetch. If it is disables (when iterate through explicitly created cursor), time of query execution is increased almost twice (46552.935 ms)

So PL/SPI ratio is more than three times.

Updatede results are the following:

Impementation	time (ms)
PL/Lua	32220
PL/pgSQL	29044
PL/pgSQL (JIT)	27594
C/SPI	9406
SQL	7399
SQL (JIT)	5532
С/coreAPI	2873

--

Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company