Re: plan cache overhead on plpgsql expression

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.

Thanks,
Amit