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

[Ruslan Zakirov]

On Fri, Apr 15, 2011 at 1:01 PM, Dominic Hargreaves
<dominic.hargreaves at oucs.ox.ac.uk> 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:
>> > 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
>
> There are already a couple of related bugs:
>
> <http://issues.bestpractical.com/Ticket/Display.html?id=8568>
> <http://issues.bestpractical.com/Ticket/Display.html?id=13056>

Updated tickets. Linked them. You can find more tickets over there.

> (linked from
> <http://requesttracker.wikia.com/wiki/DatabaseIndexes> where I
> documented the same index change that I found useful).

Vivek Khera posted his list long time ago and still uses it:
http://issues.bestpractical.com/Ticket/Display.html?id=6059

May be it's on the wiki too, if it's not then may be you can merge it.

As well I've done some review and created a code branch for that:
https://github.com/bestpractical/rt/commit/180dbddc4ca051ab2fb424cf0acb8d08f40c3cb5

As you can see from changes some indexes goes into 4.2, but some are not there.

> More in the thread starting
> <http://lists.bestpractical.com/pipermail/rt-users/2009-November/062329.html>
>
> --
> Dominic Hargreaves, Systems Development and Support Team
> Computing Services, University of Oxford

-- 
Best regards, Ruslan.