Re: plan cache overhead on plpgsql expression

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

Thanks,
Amit