On Tue, Feb 18, 2020 at 6:56 PM Amit Langote <amitlangote09@gmail.com> wrote: > On Tue, Feb 18, 2020 at 2:56 PM Pavel Stehule <pavel.stehule@gmail.com> wrote: > > út 18. 2. 2020 v 6:03 odesílatel Amit Langote <amitlangote09@gmail.com> napsal: > >> I didn't send the patch, because it didn't handle the cases where a > >> simple expression consists of an inline-able function(s) in it, which > >> are better handled by a full-fledged planner call backed up by the > >> plan cache. If we don't do that then every evaluation of such > >> "simple" expression needs to invoke the planner. For example: > >> > >> Consider this inline-able SQL function: > >> > >> create or replace function sql_incr(a bigint) > >> returns int > >> immutable language sql as $$ > >> select a+1; > >> $$; > >> > >> Then this revised body of your function foo(): > >> > >> CREATE OR REPLACE FUNCTION public.foo() > >> RETURNS int > >> LANGUAGE plpgsql > >> IMMUTABLE > >> AS $function$ > >> declare i bigint = 0; > >> begin > >> while i < 1000000 > >> loop > >> i := sql_incr(i); > >> end loop; return i; > >> end; > >> $function$ > >> ; > >> > >> With HEAD `select foo()` finishes in 786 ms, whereas with the patch, > >> it takes 5102 ms. > >> > >> I think the patch might be good idea to reduce the time to compute > >> simple expressions in plpgsql, if we can address the above issue. > > > > > > Your patch is very interesting - minimally it returns performance before 8.2. The mentioned issue can be fixed if we disallow SQL functions in this fast execution. > > I updated the patch to do that. > > With the new patch, `select foo()`, with inline-able sql_incr() in it, > runs in 679 ms. > > Without any inline-able function, it runs in 330 ms, whereas with > HEAD, it takes 590 ms.
I polished it a bit.
the performance looks very interesting - on my comp the execution time of 100000000 iterations was decreased from 34 sec to 15 sec,
