Re: How to boost performance of ilike queries ?

On Tue, 25 Jan 2005, Antony Paul wrote:

> Creating an index and using lower(column) does not change the explain
> plan estimates.
> It seems that it is not using index for like or ilike queries
> irrespective of whether it have a pattern matching character in it or
> not. (using PostgreSQL 7.3.3)

I believe in 7.3.x an index is only considered for like in "C" locale, I
think the *_pattern_op opclasses were added in 7.4 for which you can make
indexes that are considered for non wildcard starting search strings in
non "C" locales. And it may have trouble doing estimates before 8.0 on the
functional index because of lack of statistics. You may want to consider
an upgrade once 8.0 shakes out a bit.