Re: [INTERFACES] case insensitive search in a column

Roland Dirlewanger <rd(at)dr15(dot)cnrs(dot)fr> writes:
> I tried the following queries :
> 1. select * from mybase where myattr like 'word%';
> 2. select * from mybase where myattr ~* '^word';
> The first one is case-sensitive. The second one matches my needs but is
> about 5 or 6 times slower than the first one.

Probably you have an index on myattr?

The system knows how to limit the scan using the index for
case-sensitive queries --- basically it rewrites a query like the
above to
select * from mybase where myattr like 'word%' AND
myattr >= 'word' AND myattr <= 'word\377';
and then the index scanner knows what to do with the extra clauses,
so that most of the table doesn't even get visited.
(This also works for myattr ~ '^word', btw.)

This trick doesn't work for case-insensitive queries, however.

You might be able to get somewhere with a downcased functional
index, ie
create index mybase_myattr_lower
on mybase(lower(myattr) text_ops);
(I might have the syntax slightly off, but it's close) and then
write
select * from mybase where lower(myattr) like 'word%';
I am not sure whether the auto rewriting works in this scenario
however --- you might have to do it yourself, ie actually write out
select * from mybase where lower(myattr) like 'word%' AND
lower(myattr) >= 'word' AND lower(myattr) <= 'word\377';
You could check by seeing whether EXPLAIN says that the simpler
version is being done by sequential scan or index scan.

> Before I start to convert the whole database in either lowercase or
> uppercase, is there a way to fasten up case insensitive searches for
> words a the begining of a column or even in the middle of a row ?

If you want to find any word in a table, you need something like
pgsql/contrib/fulltextindex/ --- but the overhead is pretty high ...

regards, tom lane

PS: this'd be more on-topic in pgsql-sql, please direct any
followups there.