Re: plan cache overhead on plpgsql expression

st 19. 2. 2020 v 7:30 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

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

but regress tests fails

> Pavel
>
>
>
>> Thanks,
>> Amit
>>
>