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

[Kevin Falcone]

On Fri, Apr 15, 2011 at 07:44:27AM -0500, Kenneth Marshall wrote:
> 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
> 
> 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.

I've also been bitten by case-sensitive problems, but in this
particular query, RT inserts the Domain and Type from static internal
strings and builds queries against them in a similar way and you
should never be able to end up with a lowercased version in the DB.
should, famous last words, etc.

-kevin
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 195 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20110415/db12422d/attachment.sig>