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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
