Notes about Pl/PgSQL assignment performance
pensnarikFew 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 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.
Also there is a difference between versions 9.2, 9.3 and 9.5:
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:
If you have answer, please email me at pensnarik@gmail.com