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
spi_test.tgz
Description: application/compressed-tar
