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