po 17. 6. 2024 v 15:55 odesílatel Julius Tuskenis <julius.tuske...@gmail.com> napsal:
> On 2024-06-17 15:59, Philip Semanchuk wrote: > > On Jun 17, 2024, at 5:35 AM, Julius Tuskenis <julius.tuske...@gmail.com> > <julius.tuske...@gmail.com> wrote: > > > Isn't PosgreSQL supposed to inline simple SQL functions that are stable or > immutable? > > Postgres inlines SQL functions under certain > conditions:https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions > > One of those conditions is "the function is not SECURITY DEFINER”. It looks > like yours is defined that way, so that might be why it’s not being inlined. > > Hope this helps > Philip > > Thank You, Philip. > > The link you've provided helps a lot explaining why the body of my SQL > function is not inlined. > > Any thoughts on why the execution times differ so much? I see planning of > a plain SQL is 0.550ms. So I expect the SQL function to spend that time > planning (inside), but I get 50ms (100 times longer). > Attention planning time is time of optimizations, it is not planned (expected) execution time. Second - The embedded SQL inside PL/pgSQL uses plan cache. Against it, SQL functions are inlined (and then are pretty fast), or not, and then are slower, because there is no plan cache. I don't know exactly where the problem is, but I've got this issue many times, execution of an not inlined SQL function is slow. If you can, try to use a profiler. > Regards, > > Julius Tuskenis >
