Thread: What are functional indices good for?

What are functional indices good for?

From
"Patrick L. Nolan"
Date:
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                                       *


Re: What are functional indices good for?

From
Tom Lane
Date:
"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

Re: What are functional indices good for?

From
Alaric B Snell
Date:
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

Re: What are functional indices good for?

From
Bruce Momjian
Date:
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