Thread: function in index expression and unnecessary function calls in select

Hi,

I have Linux, PostgreSQL 8.1, Python 2.3

pgsql code:

test=# create table t1(name varchar(10), value integer);
CREATE TABLE
test=# create or replace function f1(integer) returns integer as $$
test$# file = open('/tmp/f1.log', 'a')
test$# file.write('log\n')
test$# file.close
test$# return args[0]+1
test$# $$ language plpythonu immutable;
CREATE FUNCTION
test=# select name, f1(value) from t1;
  name | f1
------+----
  r1   |  2
  r2   |  3
(records: 2)

$ cat /tmp/f1.log
log
log

After creating index:

test=# create index i1 on t1 (f1(value));
CREATE INDEX

$ cat /tmp/f1.log
log
log
log
log

After select:

test=# select name, f1(value) from t1;
  name | f1
------+----
  r1   |  2
  r2   |  3
(records: 2)

$ cat /tmp/f1.log
log
log
log
log
log
log

Why f1 was called in last case? Why select can't use index values
instead of function call results? Is it bug? Can it be configured?

--
Thanks,
Eugene Prokopiev

On Sun, Dec 04, 2005 at 01:00:52PM +0300, Eugene Prokopiev wrote:
> Hi,
>
> I have Linux, PostgreSQL 8.1, Python 2.3
>

<snip>

> test=# select name, f1(value) from t1;
>  name | f1
> ------+----
>  r1   |  2
>  r2   |  3
> (records: 2)
>
> $ cat /tmp/f1.log
> log
> log
> log
> log
> log
> log
>
> Why f1 was called in last case? Why select can't use index values
> instead of function call results? Is it bug? Can it be configured?

Because an index is for sorting or finding rows in a table, not for storing
data. Function indexes are to make is quicker to find certain types of
data. The index may not even store the results of f1().

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.