Re: STABLE functions

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> So just for my own understanding, the optimizer does not know that it can
> treat a STABLE function f as constant inside an outer loop of t1 in
> this query:

> select * from t1,t2 where t2.f1 = f(t1.f1)

Sure it does. For example:

regression=# create table t1(f1 int);
CREATE TABLE
regression=# create table t2(f1 int);
CREATE TABLE
regression=# create index t2f1 on t2(f1);
CREATE INDEX
regression=# create function f(int) returns int as '
regression'# select $1 + 1' language sql stable;
CREATE FUNCTION
regression=# explain select * from t1,t2 where t2.f1 = f(t1.f1);
QUERY PLAN
----------------------------------------------------------------------
Nested Loop (cost=0.00..17175.00 rows=5000 width=8)
-> Seq Scan on t1 (cost=0.00..20.00 rows=1000 width=4)
-> Index Scan using t2f1 on t2 (cost=0.00..17.08 rows=5 width=4)
Index Cond: (t2.f1 = f("outer".f1))
(4 rows)

Transforming this query into an indexscan is valid only because f()
is stable or better. With a non-stable function, you get a plain
nestloop:

regression=# create function f2(int) returns int as '
regression'# select $1 + 1' language sql;
CREATE FUNCTION
regression=# explain select * from t1,t2 where t2.f1 = f2(t1.f1);
QUERY PLAN
------------------------------------------------------------
Nested Loop (cost=0.00..35020.00 rows=5000 width=8)
Join Filter: ("inner".f1 = f2("outer".f1))
-> Seq Scan on t1 (cost=0.00..20.00 rows=1000 width=4)
-> Seq Scan on t2 (cost=0.00..20.00 rows=1000 width=4)
(4 rows)

which is slow but semantically impeccable ;-)

The point at issue is that the "stable function" classification was
defined and implemented to provide a semantically valid way of deciding
whether it's safe to treat an expression as an indexscan qualifier.
There is no code that attempts to do anything else with it.

regards, tom lane