Re: AW: Three types of functions, ala function redux.

Acutally, a function can use an index scan *if* it is marked as cacheable:
(the "test" table has 1 field, col (type is int4), which is populated with
numbers 1 thru 5000)

testdb=# create function func_test_cache (int4) returns int4 as '
testdb'# select $1;
testdb'# ' LANGUAGE 'sql' with (iscachable);
CREATE
testdb=# create function func_test (int4) returns int4 as '
testdb'# select $1;
testdb'# ' LANGUAGE 'sql';
CREATE
testdb=# vacuum analyze;
VACUUM
testdb=# explain select * from test where col = func_test_cache(1);
NOTICE: QUERY PLAN:
Index Scan using idxtest on test (cost=0.00..2.01 rows=1 width=4)
EXPLAIN
testdb=# explain select * from test where col = func_test(1);
NOTICE: QUERY PLAN:
Seq Scan on test (cost=0.00..100.00 rows=1 width=4)
EXPLAIN

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Thu, 21 Dec 2000, mlw wrote:

> Zeugswetter Andreas SB wrote:
> >
> > > select * from table where col = function() ;
> >
> > > (2) "function()" returns a number of values that are independent of the
> > > query. Postgres should be able to optimize this to be: "select * from
> > > table where col in (val1, val2, val3, ..valn)." I guess Postgres can
> > > loop until done, using the isDone flag?
> >
> > I think the above needs a different sql statement to begin with.
> > The "= function()" clearly states that function is only allowed to return one row.
> >
> > The following syntax currently works, and is imho sufficient:
> > select * from table where col in (select function());
>
> Both syntaxes work, but always force a table scan. If you have an index
> on 'col' it will not be used. If your table has millions of records,
> this takes time.
>
> --
> http://www.mohawksoft.com
>