From: | Alastair McKinley <a(dot)mckinley(at)analyticsengines(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Performance degradation with non-null proconfig |
Date: | 2020-11-19 09:10:24 |
Message-ID: | PA4PR02MB65421CD9F2CDF6321574BA08E3E00@PA4PR02MB6542.eurprd02.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Jayadevan M | 2020-11-19 10:07:19 | Re: \COPY command and indexes in tables |
Previous Message | Thomas Kellerer | 2020-11-19 08:57:03 | Re: Multiple result set to be returned in procedure/function |