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. Regards Pavel > 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 >