2017-12-19 12:45 GMT+01:00 Andrey Zhidenkov <pensna...@gmail.com>: > 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. >
surely not - test it. I am lazy think about it - but probably real reason is +/- execution of read only transactions or possibly write transactions. PostgreSQL is primary ACID database. You cannot to think about it like scripting environment only. Regards Pavel > 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-pe >>> rformance-12-19 >>> >>> Any help would be greatly appreciated. >>> -- >>> >>> >> > > > -- > С уважением, Андрей Жиденков. >
