Why overhead of SPI is so large?

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