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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Johan Geuze | 1999-05-25 19:36:33 | fetch isnt working in embdedded sql in c |
Previous Message | Craig Orsinger | 1999-05-25 17:26:59 | Re: [INTERFACES] ECPG feature |