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

Started by 德哥almost 12 years ago3 messagesbugs
Jump to latest
#1德哥
digoal@126.com

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)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: 德哥 (#1)
Re: BUG #10194: Stable function in select clause cann't be optimized to one call?

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#3德哥
digoal@126.com
In reply to: Tom Lane (#2)
Re: BUG #10194: Stable function in select clause cann't be optimized to one call?

HI, >Because its value might change between planning and execution.When in seqscan mode, stable function not be optimized to one-call .
But in index-scan mode, it's in one-call mode.
If its value might change between planning and execution. I think stable function also not allowed used in index scan mode.

--
公益是一辈子的事,I'm Digoal,Just Do It.

At 2014-05-02 03:37:16,"Tom Lane" <tgl@sss.pgh.pa.us> wrote:

Show quoted text

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