[rt-users] RT 3.8.9 + PostgreSQL 8.4.7 - missing/bad indexes

[Kenneth Marshall]

On Fri, Apr 15, 2011 at 12:09:58PM +1000, Jesse Vincent wrote:
> 
> 
> 
> On Thu 14.Apr'11 at 15:26:33 +0200, V?clav Ovs?k wrote:
> > Hi,
> > because of horrible performance of spreadsheet export of tickets I found
> > some badly created or missing indexes. I didn't investigate if other
> > RDBMS schemas has the same problem.
> > To discover problems I rewrote Results.tsv into command-line shape and
> > run it with DBI_TRACE=2 finally. 
> > 
> > For every row of Tickets the following additional problematic queries are
> > executed:
> > 
> > ########################################################################
> > 
> > SELECT  * FROM Groups WHERE Instance = ? AND LOWER(Domain) = LOWER(?) AND LOWER(Type) = LOWER(?)
> > 
> > e.g. one my bind params: 106431 'RT::Ticket-Role' 'Requestor'
> > 
> > rt=# EXPLAIN ANALYZE SELECT * FROM Groups WHERE Instance = 106431 AND LOWER(Domain) = LOWER('RT::Ticket-Role') AND LOWER(Type) = LOWER('Requestor');
> > 							     QUERY PLAN                                                              
> 
> I'd actually consider this a code bug. We shouldn't be LOWER()ing either 
> of those parameters.  Please open a ticket by mailing
> rt-bugs at bestpractical.com
> 
> Thanks!
> 

Okay, but does removing the LOWER()'s make the product harder to use?
In other words, when writing a Scrip that uses Type would it now be
case sensitive? i.e. Were AdminCc and admincc both acceptable? Some
of my most painful bug hunts were case-only related. Maybe the RT
code already handles that. My two cents.

Ken