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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
