performance of sql and plpgsql functions
Dear Postgresql performance guru,
For some reason on our client server a function written in SQL language
executes *100 times slower* than the one written in plpgsql...
After updating to "PostgreSQL 12.18, compiled by Visual C++ build 1914,
64-bit" (from pg9.5) our client reported a performance issue. Everything
boils down to a query that uses our function *public.fnk_saskaitos_skola
*to calculate a visitors debt. The function is written in 'sql' language.
The function is simple enough, marked STABLE
```
CREATE OR REPLACE FUNCTION public.fnk_saskaitos_skola(prm_saskaita integer)
RETURNS numeric
LANGUAGE sql
STABLE SECURITY DEFINER
AS $function$
SELECT
COALESCE(sum(mok_nepadengta), 0)
FROM
public.b_pardavimai
JOIN public.b_mokejimai ON (mok_pardavimas = pard_id)
WHERE
(pard_tipas = ANY('{1, 2, 6, 7}'))
AND (mok_saskaita = $1)
$function$
;
```
The problem is when I use it, it takes like 50ms to execute (on our
client server).
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM fnk_saskaitos_skola(7141968)
"Function Scan on public.fnk_saskaitos_skola (cost=0.25..0.26 rows=1
width=32) (actual time=59.824..59.825 rows=1 loops=1)"
" Output: fnk_saskaitos_skola"
" Function Call: fnk_saskaitos_skola(7141968)"
" Buffers: shared hit=20"
"Planning Time: 0.044 ms"
"Execution Time: 59.848 ms"
*How ever, if I rewrite the same function using plpgsql the result is
quite different:*
```
CREATE OR REPLACE FUNCTION public.fnk_saskaitos_skola_jt(IN prm_saskaita
integer)
RETURNS numeric
LANGUAGE 'plpgsql'
STABLE SECURITY DEFINER
PARALLEL UNSAFE
COST 100
AS $BODY$
begin
return (
SELECT
COALESCE(sum(mok_nepadengta), 0)
FROM
public.b_pardavimai
JOIN public.b_mokejimai ON (mok_pardavimas = pard_id)
WHERE
(pard_tipas = ANY('{1, 2, 6, 7}'))
AND (mok_saskaita = $1)
);
end
$BODY$;
```
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT fnk_saskaitos_skola_jt(7141968)
```
"Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.562..0.562
rows=1 loops=1)"
" Output: fnk_saskaitos_skola_jt(7141968)"
" Buffers: shared hit=20"
"Planning Time: 0.022 ms"
"Execution Time: 0.574 ms"
```
If I *analyze the sql that is inside the function* I get results similar
to the ones of using plpgsql function:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
COALESCE(sum(mok_nepadengta), 0)
FROM
public.b_pardavimai
JOIN public.b_mokejimai ON (mok_pardavimas = pard_id)
WHERE
(pard_tipas = ANY('{1, 2, 6, 7}'))
AND (mok_saskaita = 7141968)
```
"Aggregate (cost=2773.78..2773.79 rows=1 width=32) (actual
time=0.015..0.016 rows=1 loops=1)"
" Output: COALESCE(sum((b_mokejimai.mok_nepadengta)::numeric),
'0'::numeric)"
" Buffers: shared hit=4"
" -> Nested Loop (cost=1.00..2771.96 rows=730 width=3) (actual
time=0.013..0.013 rows=0 loops=1)"
" Output: b_mokejimai.mok_nepadengta"
" Inner Unique: true"
" Buffers: shared hit=4"
" -> Index Scan using idx_saskaita on public.b_mokejimai
(cost=0.56..793.10 rows=746 width=7) (actual time=0.012..0.012 rows=0
loops=1)"
" Output: b_mokejimai.mok_id, b_mokejimai.mok_moketojas,
b_mokejimai.mok_pardavimas, b_mokejimai.mok_laikas,
b_mokejimai.mok_suma, b_mokejimai.mok_budas, b_mokejimai.mok_terminas,
b_mokejimai.mok_cekis, b_mokejimai.mok_saskaita,
b_mokejimai.mok_suma_bazine, b_mokejimai.mok_nepadengta,
b_mokejimai.mok_padengta, b_mokejimai.mok_laiko_diena"
" Index Cond: (b_mokejimai.mok_saskaita = 7141968)"
" Buffers: shared hit=4"
" -> Index Scan using pk_b_pardavimai_id on public.b_pardavimai
(cost=0.44..2.65 rows=1 width=4) (never executed)"
" Output: b_pardavimai.pard_id, b_pardavimai.pard_preke,
b_pardavimai.pard_kaina, b_pardavimai.pard_nuolaida,
b_pardavimai.pard_kiekis, b_pardavimai.pard_kasos_nr,
b_pardavimai.pard_laikas, b_pardavimai.pard_prekes_id,
b_pardavimai.pard_pirkejo_id, b_pardavimai.pard_pardavejas,
b_pardavimai.pard_spausdinta, b_pardavimai.pard_reikia_grazinti,
b_pardavimai.pard_kam_naudoti, b_pardavimai.pard_susieta,
b_pardavimai.pard_galima_anuliuoti, b_pardavimai.pard_tipas,
b_pardavimai.pard_pvm, b_pardavimai.pard_apsilankymas,
b_pardavimai.pard_fk, b_pardavimai.pard_kelintas,
b_pardavimai.pard_precekis, b_pardavimai.pard_imone,
b_pardavimai.pard_grazintas, b_pardavimai.pard_debeto_sutartis,
b_pardavimai.pard_kaina_be_nld, b_pardavimai.pard_uzsakymas_pos,
b_pardavimai.pard_pvm_suma, b_pardavimai.pard_uzsakymo_nr,
b_pardavimai.pard_nuolaidos_id, b_pardavimai.pard_nuolaida_taikyti,
b_pardavimai.pard_pirkeja_keisti_galima,
b_pardavimai.pard_suma_keisti_galima"
" Index Cond: (b_pardavimai.pard_id =
b_mokejimai.mok_pardavimas)"
" Filter: (b_pardavimai.pard_tipas = ANY
('{1,2,6,7}'::smallint[]))"
"Planning Time: 0.550 ms"
"Execution Time: 0.049 ms"
```
As I understand, the planning in case of sql fun
Re: performance of sql and plpgsql functions
po 17. 6. 2024 v 11:35 odesílatel Julius Tuskenis
napsal:
> Dear Postgresql performance guru,
>
> For some reason on our client server a function written in SQL language
> executes *100 times slower* than the one written in plpgsql...
>
> After updating to "PostgreSQL 12.18, compiled by Visual C++ build 1914,
> 64-bit" (from pg9.5) our client reported a performance issue. Everything
> boils down to a query that uses our function *public.fnk_saskaitos_skola *to
> calculate a visitors debt. The function is written in 'sql' language.
>
> The function is simple enough, marked STABLE
>
> ```
>
> CREATE OR REPLACE FUNCTION public.fnk_saskaitos_skola(prm_saskaita integer)
> RETURNS numeric
> LANGUAGE sql
> STABLE SECURITY DEFINER
> AS $function$
> SELECT
> COALESCE(sum(mok_nepadengta), 0)
> FROM
> public.b_pardavimai
> JOIN public.b_mokejimai ON (mok_pardavimas = pard_id)
> WHERE
> (pard_tipas = ANY('{1, 2, 6, 7}'))
> AND (mok_saskaita = $1)
> $function$
> ;
>
> ```
>
> The problem is when I use it, it takes like 50ms to execute (on our client
> server).
>
> EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
> SELECT * FROM fnk_saskaitos_skola(7141968)
>
>
> "Function Scan on public.fnk_saskaitos_skola (cost=0.25..0.26 rows=1
> width=32) (actual time=59.824..59.825 rows=1 loops=1)"
> " Output: fnk_saskaitos_skola"
> " Function Call: fnk_saskaitos_skola(7141968)"
> " Buffers: shared hit=20"
> "Planning Time: 0.044 ms"
> "Execution Time: 59.848 ms"
>
>
> *How ever, if I rewrite the same function using plpgsql the result is
> quite different:*
>
> ```
>
> CREATE OR REPLACE FUNCTION public.fnk_saskaitos_skola_jt(IN prm_saskaita
> integer)
> RETURNS numeric
> LANGUAGE 'plpgsql'
> STABLE SECURITY DEFINER
> PARALLEL UNSAFE
> COST 100
> AS $BODY$
> begin
> return (
> SELECT
> COALESCE(sum(mok_nepadengta), 0)
> FROM
> public.b_pardavimai
> JOIN public.b_mokejimai ON (mok_pardavimas = pard_id)
> WHERE
> (pard_tipas = ANY('{1, 2, 6, 7}'))
> AND (mok_saskaita = $1)
> );
> end
> $BODY$;
>
```
>
>
> EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
> SELECT fnk_saskaitos_skola_jt(7141968)
>
>
> ```
>
> "Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.562..0.562
> rows=1 loops=1)"
> " Output: fnk_saskaitos_skola_jt(7141968)"
> " Buffers: shared hit=20"
> "Planning Time: 0.022 ms"
> "Execution Time: 0.574 ms"
>
> ```
>
>
> If I *analyze the sql that is inside the function* I get results similar
> to the ones of using plpgsql function:
>
> EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
> SELECT
> COALESCE(sum(mok_nepadengta), 0)
> FROM
> public.b_pardavimai
> JOIN public.b_mokejimai ON (mok_pardavimas = pard_id)
> WHERE
> (pard_tipas = ANY('{1, 2, 6, 7}'))
> AND (mok_saskaita = 7141968)
>
> ```
>
> "Aggregate (cost=2773.78..2773.79 rows=1 width=32) (actual
> time=0.015..0.016 rows=1 loops=1)"
> " Output: COALESCE(sum((b_mokejimai.mok_nepadengta)::numeric),
> '0'::numeric)"
> " Buffers: shared hit=4"
> " -> Nested Loop (cost=1.00..2771.96 rows=730 width=3) (actual
> time=0.013..0.013 rows=0 loops=1)"
> "Output: b_mokejimai.mok_nepadengta"
> "Inner Unique: true"
> "Buffers: shared hit=4"
> "-> Index Scan using idx_saskaita on public.b_mokejimai
> (cost=0.56..793.10 rows=746 width=7) (actual time=0.012..0.012 rows=0
> loops=1)"
> " Output: b_mokejimai.mok_id, b_mokejimai.mok_moketojas,
> b_mokejimai.mok_pardavimas, b_mokejimai.mok_laikas, b_mokejimai.mok_suma,
> b_mokejimai.mok_budas, b_mokejimai.mok_terminas, b_mokejimai.mok_cekis,
> b_mokejimai.mok_saskaita, b_mokejimai.mok_suma_bazine,
> b_mokejimai.mok_nepadengta, b_mokejimai.mok_padengta,
> b_mokejimai.mok_laiko_diena"
> " Index Cond: (b_mokejimai.mok_saskaita = 7141968)"
> " Buffers: shared hit=4"
> "-> Index Scan using pk_b_pardavimai_id on public.b_pardavimai
> (cost=0.44..2.65 rows=1 width=4) (never executed)"
> " Output: b_pardavimai.pard_id, b_pardavimai.pard_preke,
> b_pardavimai.pard_kaina, b_pardavimai.pard_nuolaida,
> b_pardavimai.pard_kiekis, b_pardavimai.pard_kasos_nr,
> b_pardavimai.pard_laikas, b_pardavimai.pard_prekes_id,
> b_pardavimai.pard_pirkejo_id, b_pardavimai.pard_pardavejas,
> b_pardavimai.pard_spausdinta, b_pardavimai.pard_reikia_grazinti,
> b_pardavimai.pard_kam_naudoti, b_pardavimai.pard_susieta,
> b_pardavimai.pard_galima_anuliuoti, b_pardavimai.pard_tipas,
> b_pardavimai.pard_pvm, b_pardavimai.pard_apsilankymas,
> b_pardavimai.pard_fk, b_pardavimai.pard_kelintas,
> b_pardavimai.pard_precekis, b_pardavimai.pard_imone,
> b_pardavimai.pard_grazintas, b_pardavimai.pard_debeto_sutartis,
> b_pardavimai.pard_kaina_be_nld, b_pardavimai.pard_uzsakymas_pos,
> b_pardavimai.pard_pvm_suma, b_pardavimai.pard_uzsakymo_nr,
> b_pardavimai.pard_nuolaidos_id, b_pardavimai.pard_nuolaida_taikyti,
> b_pardavimai.pard_pirkeja_keisti_galima,
> b_pardavimai.pard_suma_keisti_ga
Re: performance of sql and plpgsql functions
Thank you Pavel for your input.
You wrote:
no, PLpgSQL functions are not inlined
Yes, I understand that. I was referring to SQL functions (not plpgsql).
Regards,
Julius Tuskenis
On 2024-06-17 12:44, Pavel Stehule wrote:
po 17. 6. 2024 v 11:35 odesílatel Julius Tuskenis
napsal:
Dear Postgresql performance guru,
For some reason on our client server a function written in SQL
language executes *100 times slower* than the one written in
plpgsql...
After updating to "PostgreSQL 12.18, compiled by Visual C++ build
1914, 64-bit" (from pg9.5) our client reported a performance
issue. Everything boils down to a query that uses our function
*public.fnk_saskaitos_skola *to calculate a visitors debt. The
function is written in 'sql' language.
The function is simple enough, marked STABLE
```
CREATE OR REPLACE FUNCTION public.fnk_saskaitos_skola(prm_saskaita
integer)
RETURNS numeric
LANGUAGE sql
STABLE SECURITY DEFINER
AS $function$
SELECT
COALESCE(sum(mok_nepadengta), 0)
FROM
public.b_pardavimai
JOIN public.b_mokejimai ON (mok_pardavimas = pard_id)
WHERE
(pard_tipas = ANY('{1, 2, 6, 7}'))
AND (mok_saskaita = $1)
$function$
;
```
The problem is when I use it, it takes like 50ms to execute (on
our client server).
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM fnk_saskaitos_skola(7141968)
"Function Scan on public.fnk_saskaitos_skola (cost=0.25..0.26
rows=1 width=32) (actual time=59.824..59.825 rows=1 loops=1)"
" Output: fnk_saskaitos_skola"
" Function Call: fnk_saskaitos_skola(7141968)"
" Buffers: shared hit=20"
"Planning Time: 0.044 ms"
"Execution Time: 59.848 ms"
*How ever, if I rewrite the same function using plpgsql the result
is quite different:*
```
CREATE OR REPLACE FUNCTION public.fnk_saskaitos_skola_jt(IN
prm_saskaita integer)
RETURNS numeric
LANGUAGE 'plpgsql'
STABLE SECURITY DEFINER
PARALLEL UNSAFE
COST 100
AS $BODY$
begin
return (
SELECT
COALESCE(sum(mok_nepadengta), 0)
FROM
public.b_pardavimai
JOIN public.b_mokejimai ON (mok_pardavimas = pard_id)
WHERE
(pard_tipas = ANY('{1, 2, 6, 7}'))
AND (mok_saskaita = $1)
);
end
$BODY$;
```
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT fnk_saskaitos_skola_jt(7141968)
```
"Result (cost=0.00..0.26 rows=1 width=32) (actual
time=0.562..0.562 rows=1 loops=1)"
" Output: fnk_saskaitos_skola_jt(7141968)"
" Buffers: shared hit=20"
"Planning Time: 0.022 ms"
"Execution Time: 0.574 ms"
```
If I *analyze the sql that is inside the function* I get results
similar to the ones of using plpgsql function:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
COALESCE(sum(mok_nepadengta), 0)
FROM
public.b_pardavimai
JOIN public.b_mokejimai ON (mok_pardavimas = pard_id)
WHERE
(pard_tipas = ANY('{1, 2, 6, 7}'))
AND (mok_saskaita = 7141968)
```
"Aggregate (cost=2773.78..2773.79 rows=1 width=32) (actual
time=0.015..0.016 rows=1 loops=1)"
" Output: COALESCE(sum((b_mokejimai.mok_nepadengta)::numeric),
'0'::numeric)"
" Buffers: shared hit=4"
" -> Nested Loop (cost=1.00..2771.96 rows=730 width=3) (actual
time=0.013..0.013 rows=0 loops=1)"
" Output: b_mokejimai.mok_nepadengta"
" Inner Unique: true"
" Buffers: shared hit=4"
" -> Index Scan using idx_saskaita on public.b_mokejimai
(cost=0.56..793.10 rows=746 width=7) (actual time=0.012..0.012
rows=0 loops=1)"
" Output: b_mokejimai.mok_id,
b_mokejimai.mok_moketojas, b_mokejimai.mok_pardavimas,
b_mokejimai.mok_laikas, b_mokejimai.mok_suma,
b_mokejimai.mok_budas, b_mokejimai.mok_terminas,
b_mokejimai.mok_cekis, b_mokejimai.mok_saskaita,
b_mokejimai.mok_suma_bazine, b_mokejimai.mok_nepadengta,
b_mokejimai.mok_padengta, b_mokejimai.mok_laiko_diena"
" Index Cond: (b_mokejimai.mok_saskaita = 7141968)"
" Buffers: shared hit=4"
" -> Index Scan using pk_b_pardavimai_id on
public.b_pardavimai (cost=0.44..2.65 rows=1 width=4) (never
executed)"
" Output: b_pardavimai.pard_id,
b_pardavimai.pard_preke, b_pardavimai.pard_kaina,
b_pardavimai.pard_nuolaida, b_pardavimai.pard_kiekis,
b_pardavimai.pard_kasos_nr, b_pardavimai.pard_laikas,
b_pardavimai.pard_prekes_id, b_pardavimai.pard_pirkejo_id,
b_pardavimai.pard_pardavejas, b_pardavimai.pard_spausdinta,
b_pardavimai.pard_reikia_grazinti, b_pardavimai.pard_kam_naudoti,
b_pardavimai.pard_susieta, b_pardavimai.pard_galima_anuliuoti,
b_pardavimai.pard_tipas, b_pardavimai.pard_pvm,
b_pardavimai.pa
Re: performance of sql and plpgsql functions
> On Jun 17, 2024, at 5:35 AM, Julius Tuskenis > wrote: > > > Isn't PosgreSQL supposed to inline simple SQL functions that are stable or > immutable? Postgres inlines SQL functions under certain conditions: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions One of those conditions is "the function is not SECURITY DEFINER”. It looks like yours is defined that way, so that might be why it’s not being inlined. Hope this helps Philip
Re: performance of sql and plpgsql functions
On 2024-06-17 15:59, Philip Semanchuk wrote: On Jun 17, 2024, at 5:35 AM, Julius Tuskenis wrote: Isn't PosgreSQL supposed to inline simple SQL functions that are stable or immutable? Postgres inlines SQL functions under certain conditions: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions One of those conditions is "the function is not SECURITY DEFINER”. It looks like yours is defined that way, so that might be why it’s not being inlined. Hope this helps Philip Thank You, Philip. The link you've provided helps a lot explaining why the body of my SQL function is not inlined. Any thoughts on why the execution times differ so much? I see planning of a plain SQL is 0.550ms. So I expect the SQL function to spend that time planning (inside), but I get 50ms (100 times longer). Regards, Julius Tuskenis
Re: performance of sql and plpgsql functions
po 17. 6. 2024 v 15:55 odesílatel Julius Tuskenis napsal: > On 2024-06-17 15:59, Philip Semanchuk wrote: > > On Jun 17, 2024, at 5:35 AM, Julius Tuskenis > wrote: > > > Isn't PosgreSQL supposed to inline simple SQL functions that are stable or > immutable? > > Postgres inlines SQL functions under certain > conditions:https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions > > One of those conditions is "the function is not SECURITY DEFINER”. It looks > like yours is defined that way, so that might be why it’s not being inlined. > > Hope this helps > Philip > > Thank You, Philip. > > The link you've provided helps a lot explaining why the body of my SQL > function is not inlined. > > Any thoughts on why the execution times differ so much? I see planning of > a plain SQL is 0.550ms. So I expect the SQL function to spend that time > planning (inside), but I get 50ms (100 times longer). > Attention planning time is time of optimizations, it is not planned (expected) execution time. Second - The embedded SQL inside PL/pgSQL uses plan cache. Against it, SQL functions are inlined (and then are pretty fast), or not, and then are slower, because there is no plan cache. I don't know exactly where the problem is, but I've got this issue many times, execution of an not inlined SQL function is slow. If you can, try to use a profiler. > Regards, > > Julius Tuskenis >
Re: performance of sql and plpgsql functions
Julius Tuskenis writes:
> EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
> SELECT
> COALESCE(sum(mok_nepadengta), 0)
> FROM
> public.b_pardavimai
> JOIN public.b_mokejimai ON (mok_pardavimas = pard_id)
> WHERE
> (pard_tipas = ANY('{1, 2, 6, 7}'))
> AND (mok_saskaita = 7141968)
I believe that the SQL-language function executor always uses generic
plans for parameterized queries (which is bad, but nobody's gotten
round to improving it). So the above is a poor way of investigating
what will happen, because it corresponds to a custom plan for the
value 7141968. You should try something like
PREPARE p(integer) AS
SELECT COALESCE ...
... AND (mok_saskaita = $1);
SET plan_cache_mode TO force_generic_plan;
EXPLAIN ANALYZE EXECUTE p(7141968);
What I suspect is that the statistics for mok_saskaita are
highly skewed and so with a generic plan the planner will
not risk using a plan that depends on the parameter value
being infrequent, as the one you're showing does.
regards, tom lane
