Re: Performance for indexes on functions

"Patrick FICHE" <pfiche(at)prologue-software(dot)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