Message-ID: <520AD240.9060508@2ndquadrant.com>
Date: Wed, 14 Aug 2013 08:41:36 +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: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Interesting case of IMMUTABLE significantly hurting performance
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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.

-- 
 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