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