[rt-users] Re: Poor performance in upgrade from RT2 to RT3

[Andrew J. Korty]

Carl Gibbons <cgibbons at du.edu> writes:

> Logging into the RT2 system and clicking on "Home" to display the
> "25 highest priority tickets I own" takes a fraction of a second.
>
> Logging into the RT3 system and clicking on "Home" to display the
> "10 highest priority tickets I own" takes about 50 seconds.

I have the same problem only slightly worse.  Looks like the offending
query is

    SELECT DISTINCT main.* FROM ((((Tickets main JOIN Groups as
    Groups_1 ON ( main.id = Groups_1.Instance)) JOIN Principals as
    Principals_2 ON ( Groups_1.id = Principals_2.ObjectId)) JOIN
    CachedGroupMembers as CachedGroupMembers_3 ON ( Principals_2.id =
    CachedGroupMembers_3.GroupId)) JOIN Users as Users_4 ON (
    CachedGroupMembers_3.MemberId = Users_4.id)) WHERE
    ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND (
    ( ( (lower(Users_4.EmailAddress) =
    'w2kadmin at exchange.indiana.edu')AND(Groups_1.Domain =
    'RT::Ticket-Role')AND(Groups_1.Type =
    'Requestor')AND(Principals_2.PrincipalType = 'Group') ) ) AND (
    (main.Status = 'new')OR(main.Status = 'open') ) ) ORDER BY
    main.Priority DESC LIMIT 10

A similar query is done when displaying an individual ticket so it can
generate the list of ten most interesting tickets by the same
originator.

EXPLAIN ANALYZE gives me the attached query plan.  The only way I
could avoid the sequential scan (apart from disabling them altogether)
was to create the very specific index

    CREATE INDEX groups_domain_role_type_requestor ON groups (domain, type)
    WHERE domain = 'RT::Ticket-Role' AND type = 'Requestor';

That index reduces the duration of the query from 75 to 50 seconds for
me.  Still not good enough, but it helps.  I couldn't get the query
planner to use a full index on (domain, type) or even simple indexes
on (domain) and (type).  Not sure why.

I've tried other permutations of indexes but haven't found one that
contributes more than 1%.

-- 
Andrew J. Korty, Principal Security Engineer, GCIA, GCFA
Office of the Vice President for Information Technology
Indiana University
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: query_plan
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20030828/18bf592d/attachment.ksh>