Horton

Re: SQLFunctionCache and generic plans

Pavel Stehule <pavel.stehule@gmail.com>

View thread View on postgr.es
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>, Alexander Korotkov <aekorotkov@gmail.com>, pgsql-hackers@lists.postgresql.org, Ronan Dunklau <ronan.dunklau@aiven.io>
Date: 2025-02-03T17:55:48Z
Lists: pgsql-hackers 
Hi

I did multiple benchmarking, and still looks so the proposed patch doesn't
help and has significant overhead

testcase:

create or replace function fx(int) returns int as $$ select $1 + $1; $$
language sql immutable;
create or replace function fx2(int) returns int as $$ select 2 * $1; $$
language sql immutable;

I tested

do $$
begin
  for i in 1..1000000 loop
    perform fx((random()*100)::int); -- or fx2
  end loop;
end;
$$;

Results (master, patched):
fx: 17067 ms, 22165 ms
fx2: 2234 ms, 2311 ms

the execution of dynamic sql

2025-02-03 18:47:33) postgres=# do $$
begin
  for i in 1..1000000 loop
    execute 'select $1 + $1' using (random()*100)::int;
  end loop;
end;
$$;
DO
Time: 13412.990 ms (00:13.413)

In the profiler I see a significant overhead of the parser, so it looks
like there is some more (overhead), but plan cache is not used.

Please, can somebody recheck my tests?

Regards

Pavel

Commits

  1. Fix performance issue in deadlock-parallel isolation test.

  2. functions.c: copy trees from source_list before parse analysis etc.

  3. Fix oversight in commit 0dca5d68d.

  4. Change SQL-language functions to use the plan cache.

  5. Reordering DISTINCT keys to match input path's pathkeys