On Fri, Dec 21, 2012 at 8:55 AM, jg <jg@rilk.com> 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
ofthe 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