Обсуждение: Immutable function WAY slower than Stable function?

Ken Tanzer <ken.tanzer@gmail.com> writes:
> Hi.  I was recently troubleshooting a function, and realized it had
> incorrectly been declared as Immutable, when it should have been declared
> Stable.  When I changed it to Stable, the query I was running ran
> dramatically faster. Digging into this a little more, this is what I found:

EXPLAIN VERBOSE might shed a bit more light.  I suspect you're falling
foul of the rather arcane rules about whether a SQL function can be
inlined.  (Although it seems like only the wrapper function would be
inline-able anyway, so I'm not sure why the penalty is that high.)

            regards, tom lane

On 08/06/2018 03:49 PM, Ken Tanzer wrote:
> Hi.  I was recently troubleshooting a function, and realized it had 
> incorrectly been declared as Immutable, when it should have been 
> declared Stable.  When I changed it to Stable, the query I was running 
> ran dramatically faster. Digging into this a little more, this is what I 
> found:
> 

> 
> Happy to provide any additional relevant info, or for someone to point 
> out what obvious thing I'm overlooking.  Thanks in advance!

What is the definition for target_date()?

> 
> Ken
> 
> 
> 
> -- 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 08/06/2018 04:44 PM, Ken Tanzer wrote:
> On Mon, Aug 6, 2018 at 4:36 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
> 
>     What is the definition for target_date()?
> 
> 
> Hi Adrian.  Happy to provide this info.  Though on a side note, I don't 
> understand why it should matter, if functions are black box optimization 

Black boxes the bane of my existence, different field, control 
circuits:) My interest was because you are chaining functions and a 
chain is only as strong as it's weakest link. From the surface I am not 
seeing anything, will need to mull it over a bit.

> fences.  But here are the definitions:
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Ken Tanzer <ken.tanzer@gmail.com> writes:
> Hi Adrian.  Happy to provide this info.  Though on a side note, I don't
> understand why it should matter, if functions are black box optimization
> fences.

They aren't, at least not when they are SQL-language functions that
meet the conditions for inlining.  The reason that EXPLAIN VERBOSE
is helpful here is that you can see whether the function got inlined
or not: do you see a call to the function, or a representation of
its body?

            regards, tom lane

Ken Tanzer wrote:
> On Mon, Aug 6, 2018 at 4:11 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Ken Tanzer <ken.tanzer@gmail.com> writes:
> > > Hi.  I was recently troubleshooting a function, and realized it had
> > > incorrectly been declared as Immutable, when it should have been declared
> > > Stable.  When I changed it to Stable, the query I was running ran
> > > dramatically faster. Digging into this a little more, this is what I found:
>
> spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT client_id,si_imm(client_id) FROM tbl_residence_own;
>                                                           QUERY PLAN
        
 
>
-------------------------------------------------------------------------------------------------------------------------------
>  Seq Scan on public.tbl_residence_own  (cost=0.00..1990.02 rows=6977 width=8) (actual time=3.771..22665.604 rows=6983
loops=1)
>    Output: client_id, si_imm(client_id)
>    Buffers: shared hit=199814
>  Planning time: 0.156 ms
>  Execution time: 22677.333 ms
> (5 rows)
> 
> spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT client_id,si_stable(client_id) FROM tbl_residence_own;
>                                                           QUERY PLAN
       
 
>
------------------------------------------------------------------------------------------------------------------------------
>  Seq Scan on public.tbl_residence_own  (cost=0.00..3734.27 rows=6977 width=8) (actual time=3.100..1302.888 rows=6983
loops=1)
>    Output: client_id, staff_inspector_stable(client_id, target_date())
>    Buffers: shared hit=60174
>  Planning time: 0.354 ms
>  Execution time: 1315.746 ms
> (5 rows)

That seems to say that the _stable function is running much faster.
Buth functions don't get inlined.

I'd dig into the functions and find out how long the queries in
them take.  auto_explain is a good helper for that.

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

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

On 08/07/2018 12:38 AM, Ken Tanzer wrote:
> 
> 
> On Tue, Aug 7, 2018 at 12:05 AM Ken Tanzer <ken.tanzer@gmail.com 
> <mailto:ken.tanzer@gmail.com>> wrote:

> Whoops, scratch that previous explain and query.  I accidentally left in 
> a hard-coded client_id from earlier testing.  The correct query is:
> 
> EXPLAIN (VERBOSE,ANALYZE,BUFFERS) SELECT client_id,
> CASE WHEN
> (SELECT program_type_code FROM reg_spc WHERE target_date() BETWEEN reg_spc_date AND
COALESCE(reg_spc_date_end,target_date())AND client_id=tro.client_id LIMIT 1)
 
> ILIKE 'SSP%' THEN
> 
>      COALESCE((SELECT staff_id FROM staff_employment_current WHERE staff_position_code='COORD_PROP' AND
agency_project_code='SSP'LIMIT 1),(SELECT staff_id FROM staff_employment_current WHERE staff_position_code='MGRPROJ'
ANDagency_project_code='SSP' LIMIT 1))
 
> ELSE
> 
>      (SELECT staff_inspector FROM tbl_residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) WHERE
client_id=tro.client_idAND target_date() BETWEEN residence_date AND COALESCE(residence_date_end,target_date()) AND NOT
ro.is_deletedLIMIT 1)
 
> END
> FROM tbl_residence_own tro;
> 
> The corrected explain output is attached, and the actual timing was:
> 
>   Planning time: 2.741 ms
>   Execution time: 2538.277 ms
> 
> Sorry for the confusion!  It's been a long day, and filled with many 20+ 
> second waits for queries to finish. :)

Hmm, whatever it is looks to be tied to si_* being IMMUTABLE. Just a 
thought but have you tried(NOTE: DEFAULT value):

CREATE OR REPLACE FUNCTION staff_inspector_stable( client INTEGER, asof 
DATE DEFAULT target_date()) RETURNS INTEGER AS $$
SELECT
CASE WHEN
(SELECT program_type_code FROM reg_spc WHERE asof BETWEEN reg_spc_date 
AND COALESCE(reg_spc_date_end,asof) AND client_id=client LIMIT 1)
ILIKE 'SSP%' THEN
--- SSP answer
     COALESCE((SELECT staff_id FROM staff_employment_current WHERE 
staff_position_code='COORD_PROP' AND agency_project_code='SSP' LIMIT 
1),(SELECT staff_id FROM staff_employment_current WHERE 
staff_position_code='MGRPROJ' AND agency_project_code='SSP' LIMIT 1))
ELSE
---SPC answer
     (SELECT staff_inspector FROM tbl_residence_own ro LEFT JOIN 
l_housing_project USING (housing_project_code) WHERE client_id=client 
AND asof BETWEEN residence_date AND COALESCE(residence_date_end,asof) 
AND NOT ro.is_deleted LIMIT 1)

END
--LIMIT 1
$$ LANGUAGE SQL STABLE;

> 
> Cheers,
> Ken
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Ken Tanzer <ken.tanzer@gmail.com> writes:
> On Mon, Aug 6, 2018 at 7:42 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The reason that EXPLAIN VERBOSE
>> is helpful here is that you can see whether the function got inlined
>> or not: do you see a call to the function, or a representation of
>> its body?

> There is the difference in the two output lines, which I guess suggests
> that the stable one got inlined and the immutable one didn't?

> *   Output: client_id, si_imm(client_id)

Yeah, exactly: here we have the original call to si_imm ...

> *   Output: client_id, staff_inspector_stable(client_id, target_date())

... but here, that's been replaced by si_imm's body.  So we've
successfully eliminated one layer of SQL-function call overhead.

The reason this happens in one case and not the other is that one of
the arcane rules for function inlining is that it's not allowed to make
the expression look more volatile than it was before.  So if si_imm()
is marked immutable, inlining it is disallowed if it would insert any
stable or volatile functions.  I can give you a disquisition on why
that is if you like, but it's not really relevant right now, since
clearly none of these functions should have been marked immutable.

Still, though, we've only eliminated the SQL-function overhead for one
out of three SQL functions that are getting called at each row, so you'd
think the inlining could not save more than 1/3rd of the runtime at the
absolute most.  How is it making more than an order-of-magnitude
difference?

I was confused about that too, so I set up a simple test case similar
to Ken's and poked into it a bit, and what I found out is that nested
SQL functions are just about completely broken performance-wise,
unless one or the other gets inlined :-(.  The reason is that the SQL
function mechanism is designed to cache the results of parsing and
planning the function body ... but only for the duration of the
calling query.  So if you issue a direct SQL command "SELECT myf(x)
FROM big_table", and myf() isn't inline-able, then myf's body gets
parsed/planned once and then re-used at each row of the table.  That's
not too awful.  But each execution of myf's body counts as a separate
"calling query" for this purpose, so if it's calling some other SQL
function that's also not inline-able, then we re-parse-and-plan that
other function for each row of big_table.  *That* is where the time
is going.

I've had a to-do item to rewrite and improve the SQL function cache
mechanism for a long time, but I hadn't thought it was high priority.
Maybe it should be.

In the meantime, I think you could dodge the issue by converting either
level of function into plpgsql.  Or just be careful about inline-ability
of convenience wrappers.

            regards, tom lane

> On Aug 7, 2018, at 11:42, Ken Tanzer <ken.tanzer@gmail.com> wrote:
> I assume that's "for all users and all sessions," but either in theory or in practice is there a limit to how long a
stalevalue might persist?  And, if you were to drop and recreate a function with the same name & parameters, would it
startfresh at that point?  And is there a way to flush any caching?  (It's surely best to just declare Stable, but I'm
wonderingabout cases that might have _very_ infrequently-changed values.) 

Well, the extreme case is an IMMUTABLE function used to create an expression index; then, the value lasts as long as
theindex does.  The best way to think of an IMMUTABLE is that it is a pure function, unchanged by system state.  (This
isone of the reasons that datetime-related functions are often STABLE rather than IMMUTABLE, due to time zone changes.) 

--
-- Christophe Pettus
   xof@thebuild.com

Ken Tanzer <ken.tanzer@gmail.com> writes:
> I've wondered in the past about Immutable functions, and particularly the
> scope & lifetime of "forever" in "guaranteed to return the same results
> given the same arguments forever."  I assume that's "for all users and all
> sessions," but either in theory or in practice is there a limit to how long
> a stale value might persist?

The two issues I can think of offhand are (a) plans that depend on the
assumption of immutability might persist for the life of active sessions;
(b) if you created an index on the result of an immutable function,
changing the function's behavior would likely render the index
inconsistent.  (REINDEX would be enough to fix that, though.)

> And, if you were to drop and recreate a
> function with the same name & parameters, would it start fresh at that
> point?

DROP (not CREATE OR REPLACE) would surely get rid of any dependent
values.

> Finally, I'm wondering if there's any legitimate reason for a immutable
> function to call anything stable or volatile, and if not would it make any
> sense to emit a warning when the function is created.

We've been around on that before, and concluded that it's not worth it.
People have in the past used phony-immutable functions to coerce the
optimizer to do what they want --- in fact, that use case is one of the
reasons why we have the inlining restriction.  I'm not sure if such
practices are still popular, but somebody who was doing that would not
appreciate nannyism.

            regards, tom lane

On 2018.08.07 18:10, Tom Lane wrote:
> I've had a to-do item to rewrite and improve the SQL function cache
> mechanism for a long time, but I hadn't thought it was high priority.
> Maybe it should be.
>
> In the meantime, I think you could dodge the issue by converting either
> level of function into plpgsql.  Or just be careful about inline-ability
> of convenience wrappers.

I now remember I stumbled across this same problem last year when I 
needed an immutable unaccent() function for the purpose of creating a 
functional index. I simply wrapped unaccent(), which is stable, in an 
immutable custom function (and accepting the need to recreate the index 
in case when unaccent rules change). I can't remember the specifics but 
I was also surprised to learn that query execution plans were not 
optimized at all when the function was immutable and in some cases they 
were even slower than when the function was stable, quite contrary to 
all logic and to what the docs say. So I think it would be great if this 
issue were resolved in Postgres.