Interesting case of IMMUTABLE significantly hurting performance
От | Olleg Samoylov |
---|---|
Тема | Interesting case of IMMUTABLE significantly hurting performance |
Дата | |
Msg-id | 662101e2-5ca5-4bf5-9307-2519efde8690@ya.ru обсуждение исходный текст |
Ответы |
Re: Interesting case of IMMUTABLE significantly hurting performance
|
Список | pgsql-general |
PostgreSQL 17.4 CREATE OR REPLACE FUNCTION formatted_num_immutable(p_summa bigint) RETURNS text LANGUAGE sql IMMUTABLE STRICT RETURN ltrim(to_char(p_summa, '999 999 999 999 999 999 999 999')); CREATE OR REPLACE FUNCTION formatted_num_stable(p_summa bigint) RETURNS text LANGUAGE sql STABLE STRICT RETURN ltrim(to_char(p_summa, '999 999 999 999 999 999 999 999')); explain analyze select formatted_num_immutable(i) from generate_series(1,1000000) as i(i); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Function Scan on generate_series i (cost=0.00..262500.00 rows=1000000 width=32) (actual time=56.892..1548.656 rows=1000000 loops=1) Planning Time: 0.039 ms JIT: Functions: 4 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 0.184 ms (Deform 0.040 ms), Inlining 0.000 ms, Optimization 0.115 ms, Emission 1.570 ms, Total 1.869 ms Execution Time: 1587.741 ms (7 rows) explain analyze select formatted_num_stable(i) from generate_series(1,1000000) as i(i); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Function Scan on generate_series i (cost=0.00..17500.00 rows=1000000 width=32) (actual time=54.993..573.333 rows=1000000 loops=1) Planning Time: 0.056 ms Execution Time: 598.190 ms (3 rows) First interesting thing is immutable variant has cost in 15 time more, then stable. That's why jit compilation is tuned on. Second, immutable function is working much longer (3 times). And jit is not the reason. => set jit=off; SET => explain analyze select formatted_num_immutable(i) from generate_series(1,1000000) as i(i); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Function Scan on generate_series i (cost=0.00..262500.00 rows=1000000 width=32) (actual time=54.888..1537.602 rows=1000000 loops=1) Planning Time: 0.052 ms Execution Time: 1575.985 ms (3 rows) -- Olleg
В списке pgsql-general по дате отправления: