Re: Can LIKE use indexes or not?

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