Search Postgresql Archives

Heavy Function Optimisation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux