From: Tom Lane <tgl@sss.pgh.pa.us>
To: Roland Dirlewanger <rd@dr15.cnrs.fr>
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] case insensitive search in a column
Date: Tue, 25 May 1999 14:21:09 -0400
Message-ID: <3694.927656469@sss.pgh.pa.us> (raw)
In-Reply-To: <374AB7A6.D6C313D0@dr15.cnrs.fr>
Roland Dirlewanger <rd@dr15.cnrs.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, rd@dr15.cnrs.fr
Subject: Re: [INTERFACES] case insensitive search in a column
In-Reply-To: <3694.927656469@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox