Notes about Pl/PgSQL assignment performance

From: Андрей Жиденков <pensnarik(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Notes about Pl/PgSQL assignment performance
Date: 2017-12-19 11:28:00
Message-ID: CAN=gQ4Bc-CRKh91q38ApZd+B2iyB7ZDwusEPVq+iK8b8AMSG1A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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'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.
--

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2017-12-19 11:36:25 Re: Notes about Pl/PgSQL assignment performance
Previous Message Andrey Borodin 2017-12-19 10:58:35 Re: New gist vacuum.