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.
