[rt-devel] Re: [rt-users] postgres slowdowns? (pg 7.3.4, rt 3.0.4)

[Justin Hawkins]

On Thu, Oct 16, 2003 at 08:54:27PM -0400, Jesse Vincent wrote:
> 
> > Most importantly, I don't know how I can do database specific things in 
> > DBIx::SearchBuilder - or is that even desirable?
> 
> Have a look at searchbuilder 0.93_01. This already does ILIKE instead of
> LIKE for postgres. Having read a bit more about ILIKE, I'm not convinced
> that it's actually going to be faster than apropriate functional
> indices. (We should probably continue this on rt-devel)

Well here is my patch for 0.93_01 to cause case-insensitive '=' clauses to 
become ILIKE's with escaped wildcard expressions, for PostgreSQL. It's 
pretty trivial:

diff -u /usr/local/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle/Pg.pm.orig /usr/local/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle/Pg.pm
--- /usr/local/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle/Pg.pm.orig   Mon Oct 20 14:06:34 2003
+++ /usr/local/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle/Pg.pm        Mon Oct 20 14:06:25 2003
@@ -271,6 +271,11 @@
     if  ($operator =~ /LIKE/i ) {
         $operator =~ s/LIKE/ILIKE/ig; 
         return ($field, $operator, $value);
+    } elsif ($operator eq '=') {
+        $operator = 'ILIKE';
+        $value =~ s#_#\\_#g;
+        $value =~ s#%#\\%#g;
+        return ($field, $operator, $value);
     } else {
         $self->SUPER::_MakeClauseCaseInsensitive($field, $operator,$value);
     }

I agree that making the functional index work might be better, though I don't 
consider the above to be a bad solution. I'll see if I can construct a minimal 
query showing that not working and take it to the postgres folks.

	- Justin