Thank you Pavel for your input.
You wrote:
no, PLpgSQL functions are not inlinedYes, I understand that. I was referring to SQL functions (not plpgsql).
Regards,
Julius Tuskenis
po 17. 6. 2024 v 11:35 odesílatel Julius Tuskenis <julius.tuskenis@xxxxxxxxx> 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_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 functions is done everytime the functions is executed. I don't mind if planning would take 0.550 ms as when using plain SQL. But why execution takes ~59ms??... What is it spent for?
Isn't PosgreSQL supposed to inline simple SQL functions that are stable or immutable?
no, PLpgSQL functions are not inlined
Regards
Pavel
Any advice on where to look for the cause of this "anomaly" is highly appreciated?
I've tried executing the same query on different server and different database - I could not reproduce the behavior. Using SQL function produces results faster.
I'd be gratefull to receive some insights of how to investigate the behavior. I'm not keen on changing the language or the function not knowing why it is required or how it helps...
Regards,
Julius Tuskenis
