Re: Why overhead of SPI is so large?

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Why overhead of SPI is so large?
Date: 2019-08-22 11:11:28
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 22.08.2019 3:27, Tsunakawa, Takayuki wrote:
> From: Konstantin Knizhnik [mailto:k(dot)knizhnik(at)postgrespro(dot)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;
        } else

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:

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

> Konstantin Knizhnik
> Postgres Professional:
> The Russian Postgres Company

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro Horiguchi 2019-08-22 12:06:06 Re: [HACKERS] WAL logging problem in 9.4.3?
Previous Message Dave Cramer 2019-08-22 10:45:23 Re: Procedure support improvements