Re: Notes about Pl/PgSQL assignment performance

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.

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