Re: Index selection bug

Andriy I Pilipenko <bamby@marka.net.ua> writes:
> 	create function func() returns int as 'select 1' language 'sql';

> 	set enable_seqscan to 'off';

> 	explain select * from t where f = 1;

> 	  Index Scan using i on t  (cost=0.00..2.01 rows=1 width=4)

> 	explain select * from t where f = func();

> 	  Seq Scan on t  (cost=100000000.00..100000001.34 rows=1 width=4)

Not a bug, because you didn't declare the function 'iscachable'.
For all the system knows, func() is like random() and will return a
different result at every row.  An indexscan can't be used unless it's
safe to fold the function call down to a constant.  See
http://www.postgresql.org/users-lounge/docs/7.0/postgres/sql-createfunction.htm

			regards, tom lane