The document for CREATE INDEX describes how to create an index on
a function instead of a column or set of columns. Having done so,
what can I do with it?
Momjian's on-line book says this:
"In the second syntax shown above, an index is defined on the result
of a user-specified function func_name applied to one or more
attributes of a single class. These functional indices can be used to
obtain fast access to data based on operators that would normally
require some transformation to apply them to the base data."
As a newbie, I find this not very illuminating. Suppose I do
create index funcindex on mytable (func_name(column1, column2));
It appears that I can't use the name funcindex in any sort of
SELECT statement. They deal with column names, not index names.
I can try
select * from mytable where func_name(column1, column2) < 3.14159;
However, EXPLAIN tells me that it doesn't use funcindex for this.
It's always a sequential search. Why doesn't it recognize that
there's an index ready to use?
Momjian's statement suggests to me that maybe there's something
related to operator overloading, but that's deep water. Is that
really what functional indexes are for?
* Patrick L. Nolan *
* W. W. Hansen Experimental Physics Laboratory (HEPL) *
* Stanford University *