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
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
create index mybase_myattr_lower
on mybase(lower(myattr) text_ops);
(I might have the syntax slightly off, but it's close) and then
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
pgsql-interfaces by date
|Next:||From: Johan Geuze||Date: 1999-05-25 19:36:33|
|Subject: fetch isnt working in embdedded sql in c|
|Previous:||From: Craig Orsinger||Date: 1999-05-25 17:26:59|
|Subject: Re: [INTERFACES] ECPG feature|