BUG #10194: Stable function in select clause cann't be optimized to one call?
От | digoal@126.com |
---|---|
Тема | BUG #10194: Stable function in select clause cann't be optimized to one call? |
Дата | |
Msg-id | 20140501090904.1398.86628@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #10194: Stable function in select clause cann't be optimized to one call?
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 10194 Logged by: digoal.zhou Email address: digoal@126.com PostgreSQL version: 9.3.4 Operating system: CentOS 6.5 x64 Description: When i use stable function and constant parameters in select clause, the function call many times(when use seq scan), but when i change this function to immutable, it call one time(it's insteaded to result by planner). And the same function in where clause , the stable and immutable function same call one time. Why stable function in select clause cann't optimized to one time call? Can we optimized it? See exp : digoal=# create table t7(id int); digoal=# insert into t7 values (1),(2),(3); digoal=# create or replace function f_t7(i int) returns int as $$ declare begin raise notice 'called'; return i; end; $$ language plpgsql stable; digoal=# select f_t7(1),* from t7; NOTICE: called NOTICE: called NOTICE: called f_t7 | id ------+---- 1 | 1 1 | 2 1 | 3 digoal=# alter function f_t7(int) immutable; digoal=# select f_t7(1),* from t7; NOTICE: called f_t7 | id ------+---- 1 | 1 1 | 2 1 | 3 (3 rows) digoal=# alter function f_t7(int) stable; digoal=# select * from t7 where id=f_t7(1); NOTICE: called NOTICE: called NOTICE: called NOTICE: called id ---- 1 digoal=# explain select * from t7 where id=f_t7(1); NOTICE: called QUERY PLAN -------------------------------------------------- Seq Scan on t7 (cost=0.00..1.79 rows=1 width=4) Filter: (id = f_t7(1)) (2 rows) digoal=# select * from t7 where f_t7(1)=1; NOTICE: called id ---- 1 2 3 (3 rows) digoal=# explain select * from t7 where f_t7(1)=1; QUERY PLAN -------------------------------------------------------- Result (cost=0.25..1.28 rows=3 width=4) One-Time Filter: (f_t7(1) = 1) -> Seq Scan on t7 (cost=0.25..1.28 rows=3 width=4) (3 rows) digoal=# select * from t7 where f_t7(1)=id; NOTICE: called NOTICE: called id ---- 1 (1 row) digoal=# explain select * from t7 where f_t7(1)=id; NOTICE: called QUERY PLAN ------------------------------------------------------------------------- Index Only Scan using idx_t7_id on t7 (cost=0.38..8.40 rows=1 width=4) Index Cond: (id = f_t7(1)) (2 rows) digoal=# alter function f_t7(int) immutable; ALTER FUNCTION digoal=# explain select * from t7 where f_t7(1)=id; NOTICE: called QUERY PLAN ------------------------------------------------------------------------- Index Only Scan using idx_t7_id on t7 (cost=0.13..8.15 rows=1 width=4) Index Cond: (id = 1) (2 rows) digoal=# explain select * from t7 where f_t7(1)=1; NOTICE: called QUERY PLAN -------------------------------------------------------------------------- Index Only Scan using idx_t7_id on t7 (cost=0.13..12.18 rows=3 width=4) (1 row) digoal=# set enable_seqscan=on; SET digoal=# explain select * from t7 where f_t7(1)=1; NOTICE: called QUERY PLAN -------------------------------------------------- Seq Scan on t7 (cost=0.00..1.03 rows=3 width=4) (1 row) digoal=# select * from t7 where f_t7(1)=1; NOTICE: called id ---- 1 2 3 (3 rows) digoal=# select * from t7 where f_t7(1)=id; NOTICE: called id ---- 1 (1 row)
В списке pgsql-bugs по дате отправления: