Notes about Pl/PgSQL assignment performance

Notes about Pl/PgSQL assignment performance

pensnarik

Few day ago a faced a problem: Pl/PgSQL procedure works slower when running in parallel threads. I found the correlation between number of assignments in procedure code and performance. I decided to write the simple benchmark procedures and perform some test on PostgreSQL 9.6.5 database installed on the server with 20 CPU cores (2 Xeon E5-2690V2 CPUs). There is how these procedures look like:

The source code of a benchmark procedure

The first procedure is just a simple FOR loop with one assignment inside. The second procedure calls the first one pCount time and calculates execution time. When I run this test with pCount = 100 000 running time for benchmark_test() is about 0.221 ms. When I run this test in 2 threads I expect that running time will be the same, because PostgreSQL will fork process for the second connection and this process will be served by a separate CPU core because I have more than 2 cores. But in fact I got running time = 0.327 ms and when I increase threads count this value increases.

Vertical: function call time, horizontal: number of threads
Vertical: TPS for one thread, horizontal: number of threads

Also there is a difference between versions 9.2, 9.3 and 9.5:

Blue: 9.2, yellow: 9.3, red: 9.5 (assignments per second)

What is the point? I know, that Pl/PgSQL performs a SELECT query to calculate each value for assignment but I didn't expect that it produce side effects like this. If there is some buffer lock or anything else?

UPD: call graph, data collected by perf:

2 threads
10 threads


If you have answer, please email me at pensnarik@gmail.com


Report Page