Why overhead of SPI is so large?

Started by Konstantin Knizhnikover 6 years ago39 messageshackers

k.knizhnik@postgrespro.ru

over 6 years ago

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