Re: Why overhead of SPI is so large? - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: Why overhead of SPI is so large? |
| Date | |
| Msg-id | CAFj8pRDzaHU3qimoH3o3twYF0OV__vnernDobxqvhYshfqoubw@mail.gmail.com Whole thread Raw |
| In response to | Re: Why overhead of SPI is so large? (Konstantin Knizhnik <k.knizhnik@postgrespro.ru>) |
| Responses |
Re: Why overhead of SPI is so large?
|
| List | pgsql-hackers |
pá 23. 8. 2019 v 13:21 odesílatel Konstantin Knizhnik <k.knizhnik@postgrespro.ru> napsal:
On 23.08.2019 12:10, 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.
But such definition of the function contradicts IMMUTABLE contract, doesn't it?
If creator of the function incorrectly classify it, then usage of such function can cause incorrect behavior.
For example, if function is marked as "parallel safe" but actually it is not parallel safe, then using it in parallel plan may cause incorrect results.
But it is a reason for disabling parallel plans for all user defined functions, isn't it?
In reality it is not IMMUTABLE function. On second hand, there are lot of application that depends on this behave.
It is well know trick how to reduce estimation errors related to JOINs. When immutable function has constant parameters, then it is evaluated in planning time.
So sometimes was necessary to use
SELECT ... FROM tab WHERE foreign_key = immutable_function('constant parameter')
instead JOIN.
It is ugly, but it is working perfectly. I think so until we will have multi table statistics, this behave should be available in Postgres.
Sure, this function should not be used for functional indexes.
Also nothing terrible will happen in any case. If expression is calling function which is marked is immutable but actually is not, then we can just get old (deteriorated)
result of expression. Right now, if caller function (one containing evaluated expression) is marked as non-volatile, then snapshot is also not taken.
So if such function expression is calling foo() function as declared above, then results will be also incorrect.
So I do not think some principle difference here and do not understand why we should not believe user (function creator) only in this case.
-- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
pgsql-hackers by date: