Heavy Function Optimisation
От | jg |
---|---|
Тема | Heavy Function Optimisation |
Дата | |
Msg-id | 4974-50d47880-d-6b8b4580@118860035 обсуждение исходный текст |
Ответы |
Coalesce bug ?
Re: Heavy Function Optimisation |
Список | 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 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 ? -- Cordialement, Jean-Gérard Pailloncy
В списке pgsql-general по дате отправления: