On 22.08.2019 3:27, Tsunakawa, Takayuki wrote:
From: Konstantin Knizhnik [mailto:k.knizhnik@postgrespro.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;
SPI_freetuple(spi_tuple);
}
SPI_freetuptable(SPI_tuptable);
} else
break;
}
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:
Impementation
| time (ms)
|
| PL/Lua | 32220 |
| PL/pgSQL | 29044 |
PL/pgSQL (JIT)
| 27594
|
| C/SPI | 9406 |
| SQL | 7399 |
SQL (JIT)
| 5532
|
| С/coreAPI | 2873 |
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
