Re: Notes about Pl/PgSQL assignment performance

2017-12-19 12:46 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2017-12-19 12:40 GMT+01:00 Hannu Krosing <hkrosing(at)gmail(dot)com>:
>
>> On 19.12.2017 11:36, Pavel Stehule 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.
>>
>>
>> Would marking it IMMUTABLE not cache the result and thus bypass the
>> actual testing ?
>>
>
> CREATE OR REPLACE FUNCTION public.fx1()
> RETURNS void
> LANGUAGE plpgsql
> AS $function$
> begin
> for i in 1..10
> loop
> raise notice '%', i;
> end loop;
> end;
> $function$
>
> postgres=# do $$
> postgres$# begin
> postgres$# for i in 1..2
> postgres$# loop
> postgres$# perform fx1();
> postgres$# end loop;
> postgres$# end;
> postgres$# $$;
> NOTICE: 1
> NOTICE: 2
> NOTICE: 3
> NOTICE: 4
> NOTICE: 5
> NOTICE: 6
> NOTICE: 7
> NOTICE: 8
> NOTICE: 9
> NOTICE: 10
> NOTICE: 1
> NOTICE: 2
> NOTICE: 3
> NOTICE: 4
> NOTICE: 5
> NOTICE: 6
> NOTICE: 7
> NOTICE: 8
> NOTICE: 9
> NOTICE: 10
> DO
>
> test it.
>
> Personally - this test is little bit bad. What is goal? PLpgSQL is glue
> for SQL queries - nothing less, nothing more.
>

I am wrong - sorry

It needs a fake parameter

postgres=# create or replace function fx1(int)
returns void as $$
begin
for i in 1..10
loop
raise notice '%', i;
end loop;
end;
$$ language plpgsql immutable;

postgres=# do $$
begin
for i in 1..2
loop
perform fx1(i);
end loop;
end;
$$;
NOTICE: 1
NOTICE: 2
NOTICE: 3
NOTICE: 4
NOTICE: 5
NOTICE: 6
NOTICE: 7
NOTICE: 8
NOTICE: 9
NOTICE: 10
NOTICE: 1
NOTICE: 2
NOTICE: 3
NOTICE: 4
NOTICE: 5
NOTICE: 6
NOTICE: 7
NOTICE: 8
NOTICE: 9
NOTICE: 10
DO

>
>
>
>
>>
>> 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.
>>> --
>>>
>>>
>>
>> --
>> Hannu Krosing
>> PostgreSQL Consultant
>> Performance, Scalability and High Availabilityhttps://2ndquadrant.com/
>>
>>
>