Thread: Functional Index

When building an index over the function f() of a
field a, what does postgres store internally in the
index pages: a, or f(a)?

It could be done either way, but it makes a difference
in some tradeoffs that affect me.  In particular it
affects space usage, and it affects whether

SELECT a from T WHERE f(a) < foobar

can be answered using purely the index without
touching the underlying heapfile. I couldn't quite
tell what postgres actually does from the docs at

http://postgresql.crimelabs.net/users-lounge/docs/7.1/postgres/indices-functional.html

Thanks in advance
Taher


__________________________________________________
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1

"Taher H. Haveliwala" <taherh24@yahoo.com> writes:
> When building an index over the function f() of a
> field a, what does postgres store internally in the
> index pages: a, or f(a)?

f(a).  If we stored a, there'd be no difference from a regular index ...

> affects space usage, and it affects whether
> SELECT a from T WHERE f(a) < foobar
> can be answered using purely the index without
> touching the underlying heapfile.

Once again: Postgres *always* has to consult the heap, so as to see
whether the index entry represents a tuple that is valid from the point
of view of the inquiring transaction.  See the docs concerning MVCC.

However, it is true that we don't have to recompute f() in order to make
use of a functional index for this sort of query.

            regards, tom lane