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)
