On Fri, Dec 21, 2012 at 8:55 AM, jg <jg@xxxxxxxx> wrote: > Hi, > > In a projet, I have an heavy fonction that double the time of the query. > I was surprised because the function was IMMUTABLE but no cache happens. > So I wrote a small test. > > test.sql > --------------------------------------- > \timing on > > CREATE OR REPLACE FUNCTION dum(a int) > RETURNS int > LANGUAGE SQL > STRICT IMMUTABLE > AS $$ > SELECT pg_sleep(1); > SELECT 1000+$1; > $$; > > SELECT dum(a) FROM ( > SELECT 1::int AS a UNION ALL > SELECT 2::int AS a UNION ALL > SELECT 2::int AS a UNION ALL > SELECT 3::int AS a UNION ALL > SELECT 3::int AS a UNION ALL > SELECT 3::int AS a > ) t; > > WITH data AS ( > SELECT 1::int AS a UNION ALL > SELECT 2::int AS a UNION ALL > SELECT 2::int AS a UNION ALL > SELECT 3::int AS a UNION ALL > SELECT 3::int AS a UNION ALL > SELECT 3::int AS a) > ,map AS (SELECT a, dum(a) FROM data GROUP BY a) > SELECT m.dum FROM data AS d JOIN map AS m ON d.a=m.a; > --------------------------------------- > > test=# \i test.sql > Timing is on. > CREATE FUNCTION > Time: 1.479 ms > dum > ------ > 1001 > 1002 > 1002 > 1003 > 1003 > 1003 > (6 rows) > > Time: 6084.172 ms > a | dum > ---+------ > 1 | 1001 > 2 | 1002 > 2 | 1002 > 3 | 1003 > 3 | 1003 > 3 | 1003 > (6 rows) > > Time: 3029.617 ms > > I was expecting the first query takes only 3 seconds, because I was (wrongly) thinking the results of the computation of the function computation was cached. > So I emulate it with the WITH query to compute only one time by value the function dum. > > Do you think, this optimisation may be added to the optimizer ? Probably not in the sense that you mean. IMMUTABLE functions don't mean the input to output values are cached. What it does mean is that the function can be used in cases where immutable semantics are required (like create index) and that, as with STABLE, the function call can be moved around so that more or less calls are made as long as the final results are the same. IMMUTABLE functions can also in some special cases be resolved at plan time so the results are reused if all the inputs are known. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
