pg.ddx.io pgsql-interfaces@postgresql.org mailing list archivehelp / color / mirror / Atom feed
case insensitive search in a column 2+ messages / 2 participants [nested] [flat]
* case insensitive search in a column @ 1999-05-25 14:45 Roland Dirlewanger <rd@dr15.cnrs.fr> 0 siblings, 1 reply; 2+ messages in thread From: Roland Dirlewanger @ 1999-05-25 14:45 UTC (permalink / raw) To: pgsql-interfaces Hope the following problem is related enough to interfaces ... I'm looking for the most efficient way to look through a PGSQL database with a case insensitive search. 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. 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 ? Thanks a lot in advance for your help. -- Roland Dirlewanger CNRS - Délégation Aquitaine et Poitou-Charentes Esplanade des Arts et Métiers 33402 TALENCE CEDEX Mél : rd@dr15.cnrs.fr, Tél : 05.57.35.58.52, Fax : 05.57.35.58.01 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [INTERFACES] case insensitive search in a column @ 1999-05-25 18:21 Tom Lane <tgl@sss.pgh.pa.us> parent: Roland Dirlewanger <rd@dr15.cnrs.fr> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 1999-05-25 18:21 UTC (permalink / raw) To: Roland Dirlewanger <rd@dr15.cnrs.fr>; +Cc: pgsql-interfaces 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. ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-05-25 18:21 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 1999-05-25 14:45 case insensitive search in a column Roland Dirlewanger <rd@dr15.cnrs.fr> 1999-05-25 18:21 ` Tom Lane <tgl@sss.pgh.pa.us>
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