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:
![](/file/f5aef7803c8d3fc995c31.png)
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.
![](/file/2bf9c3d27e55b34031d32.png)
![](/file/71b488c2b28df35d7c037.png)
Also there is a difference between versions 9.2, 9.3 and 9.5:
![](/file/2604a0cf72e85f3a9fd5a.png)
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:
![](/file/56c4a0f5c52bb51e7e5ef.png)
![](/file/e7407aa78fe0ed0354d7a.png)
If you have answer, please email me at pensnarik@gmail.com