Re: Can LIKE use indexes or not?

try this:
CREATE [ UNIQUE ] INDEX my_index ON t ( lower(f));

John Sidney-Woollett wrote:

>David Garamond said:
>
>
>>Would using an index potentially help the performance of this query, and
>>if yes, how do I force Postgres to use the index?
>>
>>db1=> select * from t where lower(f) like 'mmm%';
>>
>>
>
>I suspect the fact that you're specifying the lower function on the column
>data, ie lower(f), implies that the function has to be applied to every
>row in the table in order to calculate the value prior to testing the like
>condition.
>
>I don't know enough about what you can and cannot do index-wise in PG, in
>terms of creating an index based on a computed (upper/lower) value of a
>column.
>
>But you could consider adding an extra column to the table and a trigger
>so that the trigger places an UPPER or LOWER version of the column "f"
>into the new column.
>
>Like searches would then be
>
>select * from t where new_upper_f like upper('MMM%');
>
>Provided that there is an index on the new column, new_upper_f, you should
>avoid the full table scan. (I think, I haven't tested this out)...
>
>John Sidney-Woollett
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>