Performance degradation with non-null proconfig

Поиск
Список
Период
Сортировка
От Alastair McKinley
Тема Performance degradation with non-null proconfig
Дата
Msg-id PA4PR02MB65421CD9F2CDF6321574BA08E3E00@PA4PR02MB6542.eurprd02.prod.outlook.com
обсуждение исходный текст
Ответы Re: Performance degradation with non-null proconfig
Список pgsql-general
Дерево обсуждения
Hi all,

I recently came across a subtle performance issue when working with some compiled UDFs to optimise a workload.

These UDFs accidently had "set search_path = 'public'" in their definition.  When profiling with perf, I noticed a large amount of elapsed time spent in the function

    voidAtEOXact_GUC(bool isCommit, int nestLevel)

Reading the comments it is called when exiting a function with a custom proconfig, removing it for my case gave me a very large (3x) speedup.

Below is a contrived test case that illustrates this issue (tested on 13.0, but also seen in 12.4).

create table test as
select r as row_id,
    array_agg(random()::float4) as data
from generate_series(1,100000) r,
generate_series(1,20) e
group by r;

create or replace function array_sum(float4[]) returns float4 as
$$
    select sum(e) from unnest($1) e;
$$ language sql immutable parallel safe;


create or replace function array_sum_public_search_path(float4[]) returns float4 as
$$
    select sum(e) from unnest($1) e;
$$ language sql immutable parallel safe set search_path = 'public';

\timing on
\o /dev/null
select format($q$ explain (analyze,verbose,buffers) select array_sum(data) from test $q$) from generate_series(1,10);
\gexec
select format($q$ explain (analyze,verbose,buffers) select array_sum_public_search_path(data) from test $q$) from generate_series(1,10);
\gexec

Test output:

postgres=# select format($q$ explain (analyze,verbose,buffers) select array_sum(data) from test $q$) from generate_series(1,10);
Time: 0.940 ms
postgres=# \gexec
Time: 745.988 ms
Time: 677.056 ms
Time: 653.709 ms
Time: 651.033 ms
Time: 650.063 ms
Time: 647.741 ms
Time: 650.328 ms
Time: 651.954 ms
Time: 655.384 ms
Time: 650.988 ms
Time: 0.976 ms
postgres=# select format($q$ explain (analyze,verbose,buffers) select array_sum_public_search_path(data) from test $q$) from generate_series(1,10);
Time: 0.774 ms
postgres=# \gexec
Time: 871.628 ms
Time: 853.298 ms
Time: 856.798 ms
Time: 857.794 ms
Time: 861.836 ms
Time: 858.291 ms
Time: 861.763 ms
Time: 850.221 ms
Time: 851.470 ms
Time: 858.875 ms
Time: 1.514 ms
postgres=#


I didn't see this discussed anywhere else, it might be worth adding a note to the documentation if it can't easily be addressed.

Best regards,

Alastair






В списке pgsql-general по дате отправления: