Skip site navigation (1) Skip section navigation (2)

Re: [INTERFACES] case insensitive search in a column

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Roland Dirlewanger <rd(at)dr15(dot)cnrs(dot)fr>
Cc: pgsql-interfaces(at)postgreSQL(dot)org
Subject: Re: [INTERFACES] case insensitive search in a column
Date: 1999-05-25 18:21:09
Message-ID: 3694.927656469@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-interfaces
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.

pgsql-interfaces by date

Next:From: Johan GeuzeDate: 1999-05-25 19:36:33
Subject: fetch isnt working in embdedded sql in c
Previous:From: Craig OrsingerDate: 1999-05-25 17:26:59
Subject: Re: [INTERFACES] ECPG feature

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group