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