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

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

"David G. Johnston" <> writes:
> ​I'm feeling a bit dense here but even after having read a number of these
> kinds of interchanges I still can't get it to stick.  I think part of the
> problem is this sentence from the docs:
> https://www.postgresql.org/docs/current/static/xfunc-volatility.html

> (Stable): "​This category allows the optimizer to optimize multiple calls
> of the function to a single call"

> I read that sentence (and the surrounding paragraph) and wonder why then
> doesn't it do so in this case.

It says "allows", it doesn't say "requires".

The reason we have this category is that without it, it would be formally
invalid to optimize an expression involving a non-immutable function into
an index comparison value, because in that context the function is indeed
only evaluated once (before the comparison value is fed into the index
machinery).  But there isn't a mechanism for that behavior outside of
index scans.

> If PostgreSQL cannot execute it only once at query start then all this talk
> about optimization seems misleading.  At worse there should be an sentence
> explaining when the optimizations noted in that paragraph cannot occur -
> and probably examples of both as well since its not clear when it can occur.

If you want an exact definition of when things will happen or not happen,
start reading the source code.  I'm loath to document small optimizer
details since they change all the time.

            regards, tom lane



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

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