po 17. 6. 2024 v 11:35 odesílatel Julius Tuskenis
<julius.tuske...@gmail.com> 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
