Re: plan cache overhead on plpgsql expression

út 18. 2. 2020 v 17:08 odesílatel Amit Langote <amitlangote09(at)gmail(dot)com>
napsal:

> On Tue, Feb 18, 2020 at 6:56 PM Amit Langote <amitlangote09(at)gmail(dot)com>
> wrote:
> > On Tue, Feb 18, 2020 at 2:56 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > > út 18. 2. 2020 v 6:03 odesílatel Amit Langote <amitlangote09(at)gmail(dot)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,

So it is interesting speedup

Pavel

> Thanks,
> Amit
>