From: | Amit Langote <amitlangote09(at)gmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: plan cache overhead on plpgsql expression |
Date: | 2020-02-18 16:08:45 |
Message-ID: | CA+HiwqHOnO4+TriKOF021To3018j5pXAO_ySgi1rqt7MfMtu5w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
Thanks,
Amit
Attachment | Content-Type | Size |
---|---|---|
plpgsql-simple-exprs_v3.patch | application/octet-stream | 11.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Nagaraj Raj | 2020-02-18 17:46:28 | DB running out of memory issues after upgrade |
Previous Message | Tom Lane | 2020-02-18 15:59:11 | Re: Resolving the python 2 -> python 3 mess |