Re: What are functional indices good for?

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(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026