Re: Interesting case of IMMUTABLE significantly hurting performance

2013/8/14 Craig Ringer <craig(at)2ndquadrant(dot)com>

> Hi folks
>
> I've run into an interesting Stack Overflow post where the user shows
> that marking a particular function as IMMUTABLE significantly hurts the
> performance of a query.
>
> http://stackoverflow.com/q/18220761/398670
>
> CREATE OR REPLACE FUNCTION
> to_datestamp_immutable(time_int double precision) RETURNS date AS $$
> SELECT date_trunc('day', to_timestamp($1))::date;
> $$ LANGUAGE SQL IMMUTABLE;
>
> With IMMUTABLE: 33060.918
> With STABLE: 6063.498
>
> The plans are the same for both, though the cost estimate for the
> IMMUTABLE variant is (surprisingly) massively higher.
>
> The question contains detailed instructions to reproduce the issue, and
> I can confirm the same results on my machine.
>
> It looks like the difference is created by to_timestamp , in that if
> to_timestamp is replaced with interval maths the difference goes away.
>
> I'm very curious and am doing a quick profile now, but I wanted to raise
> this on the list for comment/opinions, since it's very
> counter-intuitive. IIRC docs don't suggest that IMMUTABLE can ever be
> more expensive.
>

If I understand, a used IMMUTABLE flag disables inlining. What you see, is
SQL eval overflow.

My rule is - don't use flags in SQL functions, when it is possible.

Pavel

>
> --
> Craig Ringer http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>