Re: Notes about Pl/PgSQL assignment performance - Mailing list pgsql-hackers
From | Pavel Stehule |
---|---|
Subject | Re: Notes about Pl/PgSQL assignment performance |
Date | |
Msg-id | CAFj8pRCjUEk8Z+oEigNEhUAazfKC3iprDWYb88pZCMgBOb_DYg@mail.gmail.com Whole thread Raw |
In response to | Re: Notes about Pl/PgSQL assignment performance (Pavel Stehule <pavel.stehule@gmail.com>) |
List | pgsql-hackers |
2017-12-19 12:46 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:
2017-12-19 12:40 GMT+01:00 Hannu Krosing <hkrosing@gmail.com>:On 19.12.2017 11:36, Pavel Stehule wrote:Hi2017-12-19 12:28 GMT+01:00 Андрей Жиденков <pensnarik@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 $$DECLAREv INTEGER; i INTEGER;BEGINfor i in 1..1000 loopv := 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
DOtest 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;
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
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?RegardsPavelI'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 Availability https://2ndquadrant.com/
pgsql-hackers by date: