pá 13. 9. 2019 v 9:09 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal:
> > > On 24.08.2019 19:13, Pavel Stehule wrote: > > > > so 24. 8. 2019 v 18:01 odesílatel David Fetter <da...@fetter.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.knizh...@postgrespro.ru> napsal: >> > >> > > >> > > >> > > On 22.08.2019 18:56, Pavel Stehule wrote: >> > > >> > > >> > > >> > > čt 22. 8. 2019 v 17:51 odesílatel Konstantin Knizhnik < >> > > k.knizh...@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. >> > > 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 > >
