Re: Notes about Pl/PgSQL assignment performance

On 19.12.2017 11:36, Pavel Stehule wrote:
> Hi
>
> 2017-12-19 12:28 GMT+01:00 Андрей Жиденков <pensnarik(at)gmail(dot)com
> <mailto: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.

Would marking it IMMUTABLE not cache the result and thus bypass the
actual testing ?

> 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
> <http://telegra.ph/Notes-about-PlPgSQL-assignment-performance-12-19>
>
> Any help would be greatly appreciated.
> --
>
>

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
https://2ndquadrant.com/