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@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