From: | Craig Ringer <craig(at)2ndquadrant(dot)com> |
---|---|
To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Interesting case of IMMUTABLE significantly hurting performance |
Date: | 2013-08-14 00:41:36 |
Message-ID: | 520AD240.9060508@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2013-08-14 02:46:52 | Re: Interesting case of IMMUTABLE significantly hurting performance |
Previous Message | Scott Marlowe | 2013-08-13 23:42:07 | Re: subselect requires offset 0 for good performance. |