Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA47054 for ; Tue, 25 May 1999 14:22:35 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id OAA03696; Tue, 25 May 1999 14:21:09 -0400 (EDT) To: Roland Dirlewanger cc: pgsql-interfaces@postgreSQL.org Subject: Re: [INTERFACES] case insensitive search in a column In-reply-to: Your message of Tue, 25 May 1999 14:45:58 +0000 <374AB7A6.D6C313D0@dr15.cnrs.fr> Date: Tue, 25 May 1999 14:21:09 -0400 Message-ID: <3694.927656469@sss.pgh.pa.us> From: Tom Lane Roland Dirlewanger 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.