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