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

[Justin Hawkins]

On Thu, Oct 16, 2003 at 05:01:17PM -0400, Jesse Vincent wrote:
> 
> Most, if not all, of our customers run RT on MySQL which means that it
> _is_ our preferred platform and the platform that we spend the most time
> working on. That said, we do spend time and energy on Postgres. (In
> fact, I've spent the the last 8 hours on postgres-related work in RT and
> SearchBuilder.)  
> 
> We have a couple of optimizations that we're hoping will help a great
> deal and we're putting in as much time as we can spare to make them go.
> 
> We want RT to kick ass on as wide a set of database platforms as
> possible, but we can't do it without users' help.  
> 
> If you want RT to work better on Postgres, you can send us:
> 
> 	1) Patches that improve postgres performance without
> 	compromising functionality on Postgres or any other database
> 	platform

Here's a start. It's only a start and it's got some failings. 

Most importantly, I don't know how I can do database specific things in 
DBIx::SearchBuilder - or is that even desirable?

Once I can do that, I can change the code to only do the ILIKE thing
if we are using postgres AND our operator is '='. The fallback will be 
the existing code.

rt3# diff -u /usr/local/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder.pm.orig  /usr/local/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder.pm
--- /usr/local/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder.pm.orig     Fri Oct 17 09:43:22 2003
+++ /usr/local/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder.pm  Fri Oct 17 09:52:53 2003
@@ -789,8 +789,18 @@
     if ( $self->_Handle->CaseSensitive && defined $args{'VALUE'} && $args{'VALUE'} ne '' ) {
 
         unless ( $args{'CASESENSITIVE'} ) {
-            $QualifiedField = "lower($QualifiedField)";
-            $args{'VALUE'} = lc( $args{'VALUE'} );
+            # $QualifiedField = "lower($QualifiedField)";
+            # $args{'VALUE'} = lc( $args{'VALUE'} );
+
+            # changing this to an ILIKE only makes sense if it's an '='
+            # operation
+            if ($args{'OPERATOR'} eq '=') {
+              # escape any wildcards in the value since we'll be doing
+              # an ILIKE
+              $args{'VALUE'} =~ s#_#\\_#g;
+              $args{'VALUE'} =~ s#%#\\%#g;
+              $args{'OPERATOR'} = 'ILIKE';
+            }
         }
 
     }

	- Justin