Thread: Performance for indexes on functions

Hi,

I would like to use some indexes with functions like substr :
CREATE INDEX IND1 ON T1 ( substr( col1, 1, 5 ) )...
What are the performance of such an index compared to an index on col1...
In which cases will this index be used in a query : Does the where clause
has to match exactly the function used when creating the index : SELECT ...
WHERE substr( col1, 1, 5 ) = '.....'.
Do you recommend or not to use this kind of indexes ?

Excuse me it it's not really clear but if necessary, I will try to explain
it better...

Thanks a lot

Patrick FICHE

"Patrick FICHE" <pfiche@prologue-software.fr> writes:
> I would like to use some indexes with functions like substr :
> CREATE INDEX IND1 ON T1 ( substr( col1, 1, 5 ) )...

Right now you can't do that: the functional-index support only
handles cases like
    function ( columnname [ , columnname [ , ... ]] )
No constants, no expressions, just one function invoked on one or
more unadorned column names.

Of course, you can get around that pretty easily by writing a
PL function that does exactly the computation you need.  But it's
still an annoying restriction.  (I think we have someone looking
into relaxing the restriction, so that you can build a functional
index on any expression that uses one table's columns.)

What you seem to be asking, though, is whether the system is able
to do anything with a functional index on expression A for a
query involving not-very-closely-related expression B.  The answer
is no...

            regards, tom lane