Search Postgresql Archives

Re: Immutable function WAY slower than Stable function?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux