Re: performance of sql and plpgsql functions

po 17. 6. 2024 v 11:35 odesílatel Julius Tuskenis <julius(dot)tuskenis(at)gmail(dot)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
>
>
>
>