Why overhead of SPI is so large? - Mailing list pgsql-hackers

Hi, hackers.

One of our customers complains about slow execution of PL/pgSQL 
functions comparing with Oracle.
So he wants to compile PL/pgSQL functions (most likely just-in-time 
compilation).
Certainly interpreter adds quite large overhead comparing with native 
code (~10 times) but
most of PL/pgSQL functions are just running some SQL queues and 
iterating through results.

I can not believe that JIT can significantly speed-up such functions.
So I decided to make simple experiment:  I created large enough table 
and implemented functions
which calculates norm of one column in different languages.

Results are frustrating (at least for me):

PL/pgSQL:   29044.361 ms
C/SPI:          22785.597 ms
С/coreAPI:     2873.072 ms
PL/Lua:        33445.520 ms
SQL:              7397.639 ms (with parallel execution disabled)

The fact that difference between PL/pgSQL and function implemented in C 
using SPI is not so large  was expected by me.
But why it is more than 3 time slower than correspondent SQL query?
The answer seems to be in the third result: the same function in C 
implemented without SPI (usign table_beginscan/heap_getnext)
Looks like SPI adds quite significant overhead.
And as far as almost all PL languages are using SPI, them all suffer 
from it.

Below is profile of SPI function execution:

   9.47%  postgres  libc-2.23.so       [.] __memcpy_avx_unaligned
    9.19%  postgres  spitest.so         [.] spi_norm
    8.09%  postgres  postgres           [.] AllocSetAlloc
    4.50%  postgres  postgres           [.] tts_buffer_heap_getsomeattrs
    4.36%  postgres  postgres           [.] heap_form_tuple
    3.41%  postgres  postgres           [.] standard_ExecutorRun
    3.35%  postgres  postgres           [.] ExecScan
    3.31%  postgres  postgres           [.] palloc0
    2.41%  postgres  postgres           [.] heapgettup_pagemode
    2.40%  postgres  postgres           [.] AllocSetReset
    2.25%  postgres  postgres           [.] PopActiveSnapshot
    2.17%  postgres  postgres           [.] PortalRunFetch
    2.16%  postgres  postgres           [.] HeapTupleSatisfiesVisibility
    1.97%  postgres  libc-2.23.so       [.] __sigsetjmp
    1.90%  postgres  postgres           [.] _SPI_cursor_operation
    1.87%  postgres  postgres           [.] AllocSetFree
    1.86%  postgres  postgres           [.] PortalRunSelect
    1.79%  postgres  postgres           [.] heap_getnextslot
    1.75%  postgres  postgres           [.] heap_fill_tuple
    1.70%  postgres  postgres           [.] spi_dest_startup
    1.50%  postgres  postgres           [.] spi_printtup
    1.49%  postgres  postgres           [.] nocachegetattr
    1.45%  postgres  postgres           [.] MemoryContextDelete
    1.44%  postgres  postgres           [.] ExecJustAssignScanVar
    1.38%  postgres  postgres           [.] CreateTupleDescCopy
    1.33%  postgres  postgres           [.] SPI_getbinval
    1.30%  postgres  postgres           [.] PushActiveSnapshot
    1.30%  postgres  postgres           [.] AllocSetContextCreateInternal
    1.22%  postgres  postgres           [.] heap_compute_data_size
    1.22%  postgres  postgres           [.] MemoryContextCreate
    1.14%  postgres  postgres           [.] heapgetpage
    1.05%  postgres  postgres           [.] palloc
    1.03%  postgres  postgres           [.] SeqNext

As you can see, most of the time is spent in allocation and copying memory.
I wonder if somebody tried to address this problem and are there some 
plans for improving speed of PL/pgSQL and other
stored languages?

I attached to this mail sources of spi_test extension with my experiments.
Please build it and run norm.sql file.

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