Re: Why overhead of SPI is so large?

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.knizhnik@postgrespro.ru> napsal:
>
> >
> >
> > On 22.08.2019 18:56, Pavel Stehule wrote:
> >
> >
> >
> > čt 22. 8. 2019 v 17:51 odesílatel Konstantin Knizhnik <
> > k.knizhnik@postgrespro.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.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate