Thread: What are functional indices good for?
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 *
"Patrick L. Nolan" <pln@razzle.Stanford.EDU> writes:
> 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;
This is the correct approach.
> However, EXPLAIN tells me that it doesn't use funcindex for this.
Your question is just a special case of the FAQ "why doesn't Postgres
use an index for FOO". In this case, I believe that "< 3.14159" is not
considered a selective enough WHERE condition to justify using the
index. You would probably see the index used for an equality check
or range check (eg, func_name(column1, column2) < 3.14159 AND
func_name(column1, column2) > 1.0).
regards, tom lane
On Thursday 07 March 2002 17:04, Tom Lane wrote: > > select * from mytable where func_name(column1, column2) < 3.14159; > Your question is just a special case of the FAQ "why doesn't Postgres > use an index for FOO". In this case, I believe that "< 3.14159" is not > considered a selective enough WHERE condition to justify using the > index. You would probably see the index used for an equality check > or range check (eg, func_name(column1, column2) < 3.14159 AND > func_name(column1, column2) > 1.0). It strikes me that the selectivity of the query ought to be balanced against some threshold set based upon an estimate of the compute cost of running the function itself... some functions might be so resource-intensive to evaluate that it's worth using the index even for a query that's expected to return 99% of the rows! ABS -- Alaric B. Snell, Technical abs@frontwire.com
I have added some text to CREATE INDEX manual page: ! For example, a functional index on ! <literal>upper(col)</> would allow the clause ! <literal>WHERE upper(col) = 'JIM'</> to use an index. --------------------------------------------------------------------------- Patrick L. Nolan wrote: > 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 * > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026