[rt-users] query problem after 3.4.5 -> 3.6.4 upgrade

[Joop van de Wege]

Brian Kerr wrote:
> On 7/23/07, Ruslan Zakirov <ruz at bestpractical.com> wrote:
>> Do I understand right that the query is like "Requestor.EmailAddress =
>> 'xxx' OR Status = 'new'  OR Status = 'open'"?
>> * Note all binary operators (aggregators) are ORs.
> 
> The above queries were done with status delimiters.
> 
> The query breaks whether or not Status is involved.  Here is one of
> the broken queries without Status.  The database query below is
> directly from "Requestor.EmailAddress LIKE 'tom' OR Owner = 'tom'"
> formed in query builder.

I get the same query out of Oracle when doing this with rt-3.6.4. This 
is what I copy from Edit Query->Advanced:
Requestor.EmailAddress LIKE 'Joop%' AND Owner = 'Joop'
Which results in this:
SELECT COUNT (DISTINCT main.ID)
   FROM tickets main CROSS JOIN users users_3
        JOIN GROUPS groups_1
        ON (groups_1.domain = 'RT::Ticket-Role')
      AND (groups_1.TYPE = 'Requestor')
      AND (groups_1.INSTANCE = main.ID)
        JOIN cachedgroupmembers cachedgroupmembers_2
        ON (cachedgroupmembers_2.memberid = users_3.ID)
      AND (cachedgroupmembers_2.groupid = groups_1.ID)
  WHERE (main.status != 'deleted')
    AND (    (    LOWER (users_3.emailaddress) LIKE '%joop%%'
              AND cachedgroupmembers_2.ID IS NOT NULL
             )
         AND main.owner = '62'
        )
    AND (main.TYPE = 'ticket')
    AND (main.effectiveid = main.ID)

And it is 'expensive' in that it does a full table scan of Tickets 
because of the %Joop% which will kill any usage of an index on emailaddress.

Plan
SELECT STATEMENT  ALL_ROWSCost: 143  						
   11 SORT GROUP BY  Bytes: 110  Cardinality: 1  				    10 NESTED LOOPS 
  Cost: 143  Bytes: 110  Cardinality: 1  			       7 NESTED LOOPS  Cost: 
142  Bytes: 80  Cardinality: 1  			
          4 NESTED LOOPS  Cost: 140  Bytes: 65  Cardinality: 1  		
            1 TABLE ACCESS FULL TABLE RT_USER.TICKETS Cost: 138  Bytes: 
31  Cardinality: 1  	
            3 TABLE ACCESS BY INDEX ROWID TABLE RT_USER.GROUPS Cost: 2 
Bytes: 34  Cardinality: 1  	
              2 INDEX RANGE SCAN INDEX RT_USER.GROUPS3 Cost: 1 
Cardinality: 4
          6 TABLE ACCESS BY INDEX ROWID TABLE RT_USER.CACHEDGROUPMEMBERS 
Cost: 2  Bytes: 30  Cardinality: 2
            5 INDEX RANGE SCAN INDEX RT_USER.GROUPID_IDX Cost: 1 
Cardinality: 2  	
        9 TABLE ACCESS BY INDEX ROWID TABLE RT_USER.USERS Cost: 1 
Bytes: 30  Cardinality: 1  			
          8 INDEX UNIQUE SCAN INDEX (UNIQUE) RT_USER.USERS_KEY Cost: 0 
Cardinality: 1  		

Changing %Joop% to Joop% gives a index scan instead of full table scan. 
This is one of the things changed in our production RT. People need to 
add explicitly wildcards and they know that they are in for a wait if 
they ask for %text% !
I don't know your ticket/user count but this query performs quite good, 
round 200-300msec for 75 rows retrieved.

> SELECT COUNT(DISTINCT main.id) FROM Tickets main CROSS
> JOIN Users Users_3 JOIN Groups Groups_1  ON ( Groups_1.Domain =
> 'RT::Ticket-Role' ) AND ( Groups_1.Type = 'Requestor' ) AND (
> Groups_1.Instance = main.id ) LEFT JOIN CachedGroupMembers
> CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Users_3.id
> ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE
> (main.Status != 'deleted') AND (main.Owner = '58936' OR  (
> Users_3.EmailAddress LIKE '%tom%' AND CachedGroupMembers_2.id IS NOT
> NULL ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)


Joop