Re: [PERFORM] optimizing immutable vs. stable function calls?

От: Tom Lane
Тема: Re: [PERFORM] optimizing immutable vs. stable function calls?
Дата: ,
Msg-id: 14964.1484780049@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответы: Re: [PERFORM] optimizing immutable vs. stable function calls?  ("David G. Johnston")
Re: [PERFORM] optimizing immutable vs. stable function calls?  (Karl Czajkowski)
Список: pgsql-performance

Скрыть дерево обсуждения

Re: [PERFORM] optimizing immutable vs. stable function calls?  (Tom Lane, )
 Re: [PERFORM] optimizing immutable vs. stable function calls?  ("David G. Johnston", )
  Re: [PERFORM] optimizing immutable vs. stable function calls?  (Tom Lane, )
   Re: [PERFORM] optimizing immutable vs. stable function calls?  ("David G. Johnston", )
    Re: [PERFORM] optimizing immutable vs. stable function calls?  (Jim Nasby, )
     Re: [PERFORM] optimizing immutable vs. stable function calls?  (Merlin Moncure, )
 Re: [PERFORM] optimizing immutable vs. stable function calls?  (Tom Lane, )
  Re: [PERFORM] optimizing immutable vs. stable function calls?  (Karl Czajkowski, )
 Re: [PERFORM] optimizing immutable vs. stable function calls?  (Karl Czajkowski, )

Karl Czajkowski <> writes:
> The query planner does not seem to
> recognize that it can eliminate redundant calls to a STABLE function.

No, it doesn't.

> In my case, the function call does not take any arguments and is thus
> trivially independent of row data, and appears in a WHERE clause being
> compared to constants. Why wouldn't the optimizer treat this case the
> same as IMMUTABLE?

"The same as IMMUTABLE" would be to reduce the function to a constant at
plan time, which would be the wrong thing.  It would be valid to execute
it only once at query start, but there's no built-in mechanism for that.

But you could force it by putting it in a sub-SELECT, that is if you
don't like the performance of

      SELECT ... slow_stable_function() ...

try this:

      SELECT ... (SELECT slow_stable_function()) ...

That works because it's an uncorrelated sub-query, which gets evaluated
just once per run.  But the overhead associated with that mechanism is
high enough that forcing it automatically for every stable function would
be a loser.  I'd recommend doing it only where it *really* matters.

            regards, tom lane



В списке pgsql-performance по дате сообщения:

От: Karl Czajkowski
Дата:
Сообщение: Re: [PERFORM] optimizing immutable vs. stable function calls?
От: Clailson
Дата:
Сообщение: [PERFORM] Optimization inner join