Re: Why overhead of SPI is so large?

pá 13. 9. 2019 v 9:09 odesílatel Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> napsal:

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

I think so your patch is good enough for commitfest.

It doesn't remove all overhead - I think so there is lot of overhead
related to plan cache, but it in good direction.

Probably for these expressions is our final target using a cached JIT - but
nobody knows when it will be. I'll not have to time for my experiments
before October.

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