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?
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
