Re: Notes about Pl/PgSQL assignment performance

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(dot)stehule(at)gmail(dot)com>
wrote:

> Hi
>
> 2017-12-19 12:28 GMT+01:00 Андрей Жиденков <pensnarik(at)gmail(dot)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.
>> --
>>
>>
>

--
С уважением, Андрей Жиденков.