Thread: BUG #10194: Stable function in select clause cann't be optimized to one call?

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)

digoal@126.com writes:
> Why stable function in select clause cann't optimized to one time call?

Because its value might change between planning and execution.

There was some discussion awhile back of performing run-time caching of
the result, but it isn't done yet, and would impose some costs of its own.

BTW, this sort of question is not a bug.

            regards, tom lane