2013/8/14 Craig Ringer <cr...@2ndquadrant.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
