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. Yes, IMMUTABLE flag helps, but I think It's just because Postgres actually executes procedure only once.
On Tue, Dec 19, 2017 at 2:36 PM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > Hi > > 2017-12-19 12:28 GMT+01:00 Андрей Жиденков <pensna...@gmail.com>: > >> 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). >> >> This benchmark showed me that a simple Pl/PgSQL procedure with a simple >> loop inside works slower when running even in 2 threads. There is a >> procedure: >> >> CREATE OR REPLACE FUNCTION benchmark_test() RETURNS VOID AS $$ >> DECLARE >> v INTEGER; i INTEGER; >> BEGIN >> for i in 1..1000 loop >> v := 1; >> end loop; >> END; >> $$ LANGUAGE plpgsql; >> >> 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? >> > > I am little bit lost when you are speaking about threads. Postgres doesn't > use it. > > your test is not correct - benchmark_test should be marked as immutable. > What will be result? > > Regards > > Pavel > > > > >> >> I've been written a post with charts and detailed explanation to display >> these side effects: http://telegra.ph/Notes-about-PlPgSQL-assignment- >> performance-12-19 >> >> Any help would be greatly appreciated. >> -- >> >> > -- С уважением, Андрей Жиденков.
