Notes about Pl/PgSQL assignment performance
| От | Андрей Жиденков |
|---|---|
| Тема | Notes about Pl/PgSQL assignment performance |
| Дата | |
| Msg-id | CAN=gQ4Bc-CRKh91q38ApZd+B2iyB7ZDwusEPVq+iK8b8AMSG1A@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Notes about Pl/PgSQL assignment performance
|
| Список | 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.
-- В списке pgsql-hackers по дате отправления: