Re: Interesting case of IMMUTABLE significantly hurting performance

Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
> It's interesting that this variant doesn't seem to be slow:

> create or replace function to_datestamp_immutable(
> time_int double precision
> ) returns date as $$
> select date_trunc('day', timestamp 'epoch' + $1 * interval '1
> second')::date;
> $$ language sql immutable;

> and there's no sign it's parsed each time. So it's not just the
> IMMUTABLE flag.

If you're working with timestamp not timestamptz, I think the functions
being called here actually are immutable (they don't have any dependency
on the timezone parameter). So this function is safely inline-able
and there's no performance hit from multiple executions.

As Pavel mentioned upthread, the safest rule of thumb for SQL functions
that you want to get inlined is to not mark them as to either mutability
or strictness. That lets the planner inline them without any possible
change of semantics. (The basic point here is that a function marked
volatile can be expanded to its contained functions even if they're
immutable; but the other way around represents a potential semantic
change, so the planner won't do it.)

regards, tom lane