On 19.12.2017 11:36, Pavel Stehule wrote: > Hi > > 2017-12-19 12:28 GMT+01:00 Андрей Жиденков <pensna...@gmail.com > <mailto: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.
Would marking it IMMUTABLE not cache the result and thus bypass the actual testing ? > 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 > <http://telegra.ph/Notes-about-PlPgSQL-assignment-performance-12-19> > > Any help would be greatly appreciated. > -- > > -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability https://2ndquadrant.com/
