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

From: digoal(at)126(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #10194: Stable function in select clause cann't be optimized to one call?
Date: 2014-05-01 09:09:04
Message-ID: 20140501090904.1398.86628@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 10194
Logged by: digoal.zhou
Email address: digoal(at)126(dot)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)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2014-05-01 19:37:16 Re: BUG #10194: Stable function in select clause cann't be optimized to one call?
Previous Message Tom Lane 2014-04-30 23:52:48 Re: BUG #10189: Limit in 9.3.4 no longer works when ordering using a composite multi-type index