Re: performance of sql and plpgsql functions

po 17. 6. 2024 v 15:55 odesílatel Julius Tuskenis <julius(dot)tuskenis(at)gmail(dot)com>
napsal:

> On 2024-06-17 15:59, Philip Semanchuk wrote:
>
> On Jun 17, 2024, at 5:35 AM, Julius Tuskenis <julius(dot)tuskenis(at)gmail(dot)com> <julius(dot)tuskenis(at)gmail(dot)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
>