Re: Notes about Pl/PgSQL assignment performance

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