Re: Why overhead of SPI is so large?

On 24.08.2019 19:13, Pavel Stehule wrote:
>
>
> so 24. 8. 2019 v 18:01 odesílatel David Fetter <david(at)fetter(dot)org
> <mailto:david(at)fetter(dot)org>> napsal:
>
> On Fri, Aug 23, 2019 at 11:10:28AM +0200, Pavel Stehule wrote:
> > pá 23. 8. 2019 v 11:05 odesílatel Konstantin Knizhnik <
> > k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)ru>>
> napsal:
> >
> > >
> > >
> > > On 22.08.2019 18:56, Pavel Stehule wrote:
> > >
> > >
> > >
> > > čt 22. 8. 2019 v 17:51 odesílatel Konstantin Knizhnik <
> > > k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)ru>>
> napsal:
> > >
> > >> Some more information...
> > >> First of all I found out that marking PL/pgSQL function as
> immutable
> > >> significantly increase speed of its execution:
> > >> 19808 ms vs. 27594. It happens because exec_eval_simple_expr
> is taken
> > >> snapshot if function is volatile (default).
> > >> I wonder if PL/pgSQL compiler can detect that evaluated
> expression itself
> > >> is actually immutable  and there is no need to take snapshot
> > >> for each invocation of this function. Also I have tried yet
> another PL
> > >> language - JavaScript, which is now new outsider, despite to
> the fact that
> > >> v8 JIT compiler is very good.
> > >>
> > >
> > > I have a plan to do some work in this direction. Snapshot is
> not necessary
> > > for almost buildin functions. If expr calls only buildin
> functions, then
> > > probably can be called without snapshot and without any work
> with plan
> > > cache.
> > >
> > >
> > > I wonder if the following simple patch is correct?
> > >
> >
> > You cannot to believe to user defined functions so immutable flag is
> > correct. Only buildin functions are 100% correct.
> >
> > CREATE OR REPLACE FUNCTION foo()
> > RETURNS int AS $$
> > SELECT count(*) FROM pg_class;
> > $$ LANGUAGE sql IMMUTABLE;
> >
> > is working.
>
> No, it's lying to the RDBMS, so it's pilot error. The problem of
> determining from the function itself whether it is in fact immutable
> is, in general, equivalent to the Halting Problem, so no, we can't
> figure it out. We do need to trust our users not to lie to us, and we
> do not need to protect them from the consequences when they do.
>
>
> I have not any problem with fixing this behave when there will be any
> alternative.
>
> I can imagine new special flag that can be used for STABLE functions,
> that enforce one shot plans and can be optimized similar like
> IMMUTABLE functions now - using result in planning time.
>
> The users lie because they must - there is not a alternative. There is
> not any other solution - and estimation errors related to a joins are
> fundamental issue.
>

Pavel, I wonder if I can put my patch (with fix which performs this
optimization only for built-in functions) to commitfest or you prefer to
do it yourself in some other way and propose your own solution?

Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company