Re: Functional Index

"Taher H. Haveliwala" <taherh24(at)yahoo(dot)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