performance of sql and plpgsql functions

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