Re: performance of sql and plpgsql functions

Pavel Stehule <pavel.stehule@xxxxxxxxx> · Mon, 17 Jun 2024 16:07:40 +0200

po 17. 6. 2024 v 15:55 odesílatel Julius Tuskenis <julius.tuskenis@xxxxxxxxx> napsal:

    On 2024-06-17 15:59, Philip Semanchuk
      wrote:

        On Jun 17, 2024, at 5:35 AM, Julius Tuskenis <julius.tuskenis@xxxxxxxxx> 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