Re: Interesting case of IMMUTABLE significantly hurting performance

On 08/14/2013 12:17 PM, Tom Lane wrote:
> [ shrug... ] Using IMMUTABLE to lie about the mutability of a function
> (in this case, date_trunc) is a bad idea. It's likely to lead to wrong
> answers, never mind performance issues. In this particular case, I
> imagine the performance problem comes from having suppressed the option
> to inline the function body ... but you should be more worried about
> whether you aren't getting flat-out bogus answers in other cases.

Oh, I totally agree, and I'd never do this myself. I was just curious
about the behaviour.

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 nothing else this strongly suggests that the docs don't cover this
area particularly comprehensively.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services