From: | "jg" <jg(at)rilk(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Heavy function optimisation ? |
Date: | 2012-12-20 20:26:17 |
Message-ID: | 3010-50d37480-3d-6b8b4580@98418124 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 ?
--
Cordialement,
Jean-Gérard Pailloncy
From | Date | Subject | |
---|---|---|---|
Next Message | Denis Papathanasiou | 2012-12-20 20:55:55 | POSIX Regular Expressions on xml type fields |
Previous Message | Stephen Touset | 2012-12-20 19:52:15 | Implicit transaction not rolling back after error |