Re: Immutable function WAY slower than Stable function?

Ken Tanzer wrote:
> > Buth functions don't get inlined.
>
> OK, I clearly don't understand the output lines. What does it mean then that the stable
> output line shows the underlying (two-argument) function, while the immutable one shows
> the convenience function?
>
> Output: client_id, si_imm(client_id)
> Output: client_id, staff_inspector_stable(client_id, target_date())

I was wrong, obviously "si_imm" gets inlined and replaced with the other
function call in the STABLE case.

> > I'd dig into the functions and find out how long the queries in
> > them take. auto_explain is a good helper for that.
>
> I'm definitely not understanding why or how auto-explain would help here. (Also, don't
> overlook the fact that both si_stable and si_imm have the exact same definition
> (except for stable v. immutable), and are calling the same function (staff_inspector_stable)).
>
> Let me know if that is not helpful. Or if you need something from auto-explain,
> please help me with some more specifics. Thanks!

If you set

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 0
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_verbose = on
auto_explain.log_nested_statements = on

and run both the slow and the fast query, the log will contain the execution plans and
time for all SQL statements that were called from the functions.

Then you can identify in which nested query the time is spent, which should give us
more material to determine the cause.

The most likely explanation for the difference is that the same query is running with
different execution plans in both cases.

Do you notice a difference if you start a new database session and run the queries
several times? Is there a difference in execution time from the sixth execution on?
If yes, generic plans may be part of the problem.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com