Re: Interesting case of IMMUTABLE significantly hurting performance

On 4/9/25 14:21, Nico Williams wrote:
> On Wed, Apr 09, 2025 at 10:50:00PM +0200, Laurenz Albe wrote:
>> The IMMUTABLE function cannot be inlined because to_char() is not IMMUTABLE.
>
> Q: Why would to_char() not be IMMUTABLE?
>
> A: Because it makes use of locales, and I guess the guc-timezone GUC,
> which could change if the expression is ultimately used in a PlPgSQL
> fragment, or if it's in a prepared statement. (I think.)
>
> That to_char is not immutable is not documented though. Though it's
> clear when looking at the docs for the `jsonb_.*_tz()` functions.

From here:

https://www.postgresql.org/docs/current/catalog-pg-proc.html

select proname, provolatile, prosrc from pg_proc where proname='to_char';
proname | provolatile | prosrc
---------+-------------+---------------------
to_char | s | timestamptz_to_char
to_char | s | numeric_to_char
to_char | s | int4_to_char
to_char | s | int8_to_char
to_char | s | float4_to_char
to_char | s | float8_to_char
to_char | s | interval_to_char
to_char | s | timestamp_to_char

Where 's' is:

"It is s for “stable” functions, whose results (for fixed inputs) do not
change within a scan."

>
> Nico

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com