Message-ID: <520B0B26.70204@2ndquadrant.com>
Date: Wed, 14 Aug 2013 12:44:22 +0800
From: Craig Ringer <craig@2ndquadrant.com>
Organization: 2nd Quadrant
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130625 Thunderbird/17.0.7
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Interesting case of IMMUTABLE significantly hurting
 performance
References: <520AD240.9060508@2ndquadrant.com>
 <22812.1376453860@sss.pgh.pa.us>
In-Reply-To: <22812.1376453860@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance