[rt-users] Is this slow? It seems slow.

Jamie Wilkinson jaq at spacepants.org
Wed Jun 11 00:34:38 EDT 2003


This one time, at band camp, Jesse Vincent wrote:
>
>
>
>On Wed, Jun 11, 2003 at 11:33:52AM +1000, Jamie Wilkinson wrote:
>> This one time, at band camp, Val Luck wrote:
>> >As it is right now with testing (creating tickets, re-assigning them, resolving them, etc) I can get up to a 5 to 15 second delay when going from "display" to "jumbo" mode.  
>> 
>> And similarly when clicking on Update from the front page, or
>> Resolve/Comment/Reply on the Display page.  Yes, this behaviour has been
>> seen in the past.  version 3.0.3pre2 of RT with a recent version of
>> DBIx::SearchBuilder (now 0.86) has fixed the speed problem for some people.
>> Unfortunately, I am not one of them.  My experience - 3.0.0 and 3.0.1 ran
>> dog slow, 3.0.1 with a 0.83_01 of DBIx::SearchBuilder ran faster, but page
>> loads were still taking about 30 seconds for the above operations, and now
>> with 3.0.3pre2 and DBIx::SearchBuilder 0.86 I am again experiencing 20
>> minute database queries.
>
>That seems Surprising. More details would be appreciated.

Attached is the query from DBIx::Searchbuilder 0.86, the query plan from an
explain on the query, and an explain analyze (which is probably more useful,
and shows the total running time of the query).

I'm trying to see a way in which the query has differed from earlier
versions, but apart from the extra parentheses, it looks familiar.

What does the CachedGroupMembers table do, and what do each of the columns
mean?

-- 
jaq at spacepants.org                           http://spacepants.org/jaq.gpg
-------------- next part --------------
SELECT DISTINCT main.* FROM
 (
  (
   (
    (
     Users main 
	-- user
      JOIN Principals as Principals_1
       ON (main.id = Principals_1.id)
    )
    JOIN CachedGroupMembers as CachedGroupMembers_5
     ON (Principals_1.id = CachedGroupMembers_5.MemberId)
   )
   JOIN CachedGroupMembers as CachedGroupMembers_6
    ON (Principals_1.id = CachedGroupMembers_6.MemberId)
  )
	-- group
  JOIN Principals as Principals_3
   ON (CachedGroupMembers_5.GroupId = Principals_3.id)
 ),
 Groups Groups_2,
 ACL ACL_4

WHERE

 -- has right to own a ticket
 (
  (
   ACL_4.RightName = 'SuperUser'
  )
  OR
  (
   ACL_4.RightName = 'OwnTicket'
  )
 )
 AND
 -- ?
 (
  (
   CachedGroupMembers_6.GroupId = '4'
  )
 )
 AND
 -- principal is not disabled
 (
  (
   Principals_1.Disabled = '0'
  )
 )
 AND
 -- principal is a user
 (
  (
   Principals_1.PrincipalType = 'User'
  )
 )
 AND
 -- acl is system user or it's the current queue
 (
  ACL_4.ObjectType = 'RT::System' 
  OR
  (
   ACL_4.ObjectType = 'RT::Queue' 
   AND
   ACL_4.ObjectId = 5
  )
 )
 AND
 (
  (
   ACL_4.PrincipalId = Principals_3.id
   AND
   Principals_3.id = Groups_2.id
   AND
   ACL_4.PrincipalType = 'Group'
   AND
   (
    Groups_2.Domain = 'SystemInternal'
    OR
    Groups_2.Domain = 'UserDefined'
    OR
    Groups_2.Domain = 'ACLEquivalence'
   )
  )
  OR
  (
   -- principal is in group for the current queue or this exact ticket
   (
    (
     Groups_2.Domain = 'RT::Queue-Role'
     AND
     Groups_2.Instance = 5
    )
    OR
    (
     Groups_2.Domain = 'RT::Ticket-Role'
     AND
     Groups_2.Instance = 388
    )
   )
   AND 
   Groups_2.Type = ACL_4.PrincipalType
   AND
   Groups_2.id = Principals_3.id
   AND
   Principals_3.PrincipalType = 'Group'
  )
 )
 AND
 Principals_1.id = main.id

ORDER BY main.Name ASC;
-------------- next part --------------
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                         QUERY PLAN
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                             
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=752.10..752.19 rows=1 width=3747)
   ->  Sort  (cost=752.10..752.11 rows=1 width=3747)
         Sort Key: main.name, main.id, main."password", main.comments, main.signature, main.emailaddress, main.freeformcontactinfo, main.organization, main.realname, main.nickname, main.lang, main.emailencoding, main.webencoding, main.externalcontactinfoid, main.contactinfosystem, main.externalauthid, main.authsystem,
main.gecos, main.homephone, main.workphone, main.mobilephone, main.pagerphone, main.address1, main.address2, main.city, main.state, main.zip, main.country, main.timezone, main.pgpkey, main.creator, main.created, main.lastupdatedby, main.lastupdated
         ->  Nested Loop  (cost=150.61..752.09 rows=1 width=3747)
               Join Filter: ((("outer".principalid = "outer".id) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".principalid = "outer".id) OR ("inner".instance = 388) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND
(("outer".principalid = "outer".id) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner".instance = 5)) AND (("outer".principalid = "outer".id) OR ("inner".instance = 388) OR ("inner".instance = 5)) AND (("outer".principalid = "outer".id) OR ("inner"."type" = "outer".principaltype)) AND (("outer".id = "inner".id) OR ("inner"."type" = "outer".principaltype)) AND (("outer".principalid = "outer".id) OR ("inner".id = "outer".id)) AND (("outer".principaltype =
'Group'::character varying) OR ("inner".id = "outer".id)) AND (("outer".id = "inner".id) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".id = "inner".id) OR ("inner".instance = 388) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".id = "inner".id) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner".instance = 5)) AND (("outer".id = "inner".id) OR ("inner".instance = 388) OR ("inner".instance = 5)) AND (("outer".id = "inner".id) OR ("inner".id = "outer".id)) AND (("inner"."domain" = 'SystemInternal'::character varying) OR ("inner"."domain" = 'UserDefined'::character varying) OR ("inner"."domain" = 'ACLEquivalence'::character varying) OR ("inner".id = "outer".id)) AND (("outer".id = "inner".id) OR ("outer".principaltype = 'Group'::character varying)) AND (("inner"."domain" = 'SystemInternal'::character varying) OR ("inner"."domain" = 'UserDefined'::character varying) OR ("inner"."domain" = 'ACLEquivalence'::character varying) OR ("outer".principaltype = 'Group'::character
varying)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner".instance = 388) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner".instance = 5)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner".instance = 388) OR ("inner".instance = 5)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner"."type" = "outer".principaltype)) AND (("inner"."domain" = 'SystemInternal'::character varying) OR ("inner"."domain" = 'UserDefined'::character varying) OR ("inner"."domain" = 'ACLEquivalence'::character varying) OR ("inner"."type" = "outer".principaltype)))
               ->  Nested Loop  (cost=150.61..444.13 rows=1 width=3511)
                     Join Filter: ((("outer".principalid = "inner".id) OR ("inner".principaltype = 'Group'::character varying)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner".principaltype = 'Group'::character varying)))
                     ->  Seq Scan on acl acl_4  (cost=0.00..2.55 rows=1 width=59)
                           Filter: (((rightname = 'SuperUser'::character varying) OR (rightname = 'OwnTicket'::character varying)) AND ((objecttype = 'RT::Queue'::character varying) OR (objecttype = 'RT::System'::character varying)) AND ((objectid = 5) OR (objecttype = 'RT::System'::character varying)))
                     ->  Materialize  (cost=441.48..441.48 rows=5 width=3452)
                           ->  Nested Loop  (cost=150.61..441.48 rows=5 width=3452)
                                 ->  Hash Join  (cost=150.61..412.49 rows=5 width=3406)
                                       Hash Cond: ("outer".id = "inner".memberid)
                                       ->  Nested Loop  (cost=0.00..261.27 rows=31 width=3402)
                                             ->  Nested Loop  (cost=0.00..110.16 rows=1 width=3394)
                                                   ->  Seq Scan on principals principals_1  (cost=0.00..104.34 rows=1 width=4)
                                                         Filter: ((disabled = 0::smallint) AND (principaltype = 'User'::character varying))
                                                   ->  Index Scan using users_pkey on users main  (cost=0.00..5.81 rows=1 width=3390)
                                                         Index Cond: ((main.id = "outer".id) AND ("outer".id = main.id))
                                             ->  Index Scan using cachedgroupmembers2 on cachedgroupmembers cachedgroupmembers_5  (cost=0.00..150.48 rows=51 width=8)
                                                   Index Cond: (cachedgroupmembers_5.memberid = "outer".id)
                                       ->  Hash  (cost=150.48..150.48 rows=51 width=4)
                                             ->  Index Scan using cachedgroupmembers3 on cachedgroupmembers cachedgroupmembers_6  (cost=0.00..150.48 rows=51 width=4)
                                                   Index Cond: (groupid = 4)
                                 ->  Index Scan using principals_pkey on principals principals_3  (cost=0.00..5.95 rows=1 width=46)
                                       Index Cond: ("outer".groupid = principals_3.id)
               ->  Seq Scan on groups groups_2  (cost=0.00..307.80 rows=1 width=236)
                     Filter: ((("domain" = 'SystemInternal'::character varying)
OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR ("domain" = 'RT::Ticket-Role'::character varying) OR ("domain" = 'RT::Queue-Role'::character varying)) AND (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR (instance = 388) OR ("domain" = 'RT::Queue-Role'::character varying)) AND (("domain" = 'SystemInternal'::character
varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR ("domain" = 'RT::Ticket-Role'::character varying) OR (instance = 5)) AND (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR (instance = 388) OR (instance = 5)))
(28 rows)
 
-------------- next part --------------
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                         QUERY PLAN
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                             
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=752.10..752.19 rows=1 width=3747) (actual time=1264916.94..1264917.64 rows=5 loops=1)
   ->  Sort  (cost=752.10..752.11 rows=1 width=3747) (actual time=1264916.94..1264916.94 rows=20 loops=1)
         Sort Key: main.name, main.id, main."password", main.comments, main.signature, main.emailaddress, main.freeformcontactinfo, main.organization, main.realname, main.nickname, main.lang, main.emailencoding, main.webencoding, main.externalcontactinfoid, main.contactinfosystem, main.externalauthid, main.authsystem,
main.gecos, main.homephone, main.workphone, main.mobilephone, main.pagerphone, main.address1, main.address2, main.city, main.state, main.zip, main.country, main.timezone, main.pgpkey, main.creator, main.created, main.lastupdatedby, main.lastupdated
         ->  Nested Loop  (cost=150.61..752.09 rows=1 width=3747) (actual time=905218.84..1264915.58 rows=20 loops=1)
               Join Filter: ((("outer".principalid = "outer".id) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".principalid = "outer".id) OR ("inner".instance = 388) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND
(("outer".principalid = "outer".id) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner".instance = 5)) AND (("outer".principalid = "outer".id) OR ("inner".instance = 388) OR ("inner".instance = 5)) AND (("outer".principalid = "outer".id) OR ("inner"."type" = "outer".principaltype)) AND (("outer".id = "inner".id) OR ("inner"."type" = "outer".principaltype)) AND (("outer".principalid = "outer".id) OR ("inner".id = "outer".id)) AND (("outer".principaltype =
'Group'::character varying) OR ("inner".id = "outer".id)) AND (("outer".id = "inner".id) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".id = "inner".id) OR ("inner".instance = 388) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".id = "inner".id) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner".instance = 5)) AND (("outer".id = "inner".id) OR ("inner".instance = 388) OR ("inner".instance = 5)) AND (("outer".id = "inner".id) OR ("inner".id = "outer".id)) AND (("inner"."domain" = 'SystemInternal'::character varying) OR ("inner"."domain" = 'UserDefined'::character varying) OR ("inner"."domain" = 'ACLEquivalence'::character varying) OR ("inner".id = "outer".id)) AND (("outer".id = "inner".id) OR ("outer".principaltype = 'Group'::character varying)) AND (("inner"."domain" = 'SystemInternal'::character varying) OR ("inner"."domain" = 'UserDefined'::character varying) OR ("inner"."domain" = 'ACLEquivalence'::character varying) OR ("outer".principaltype = 'Group'::character
varying)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner".instance = 388) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner".instance = 5)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner".instance = 388) OR ("inner".instance = 5)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner"."type" = "outer".principaltype)) AND (("inner"."domain" = 'SystemInternal'::character varying) OR ("inner"."domain" = 'UserDefined'::character varying) OR ("inner"."domain" = 'ACLEquivalence'::character varying) OR ("inner"."type" = "outer".principaltype)))
               ->  Nested Loop  (cost=150.61..444.13 rows=1 width=3511) (actual
time=244.02..1432.59 rows=28042 loops=1)
                     Join Filter: ((("outer".principalid = "inner".id) OR ("inner".principaltype = 'Group'::character varying)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner".principaltype = 'Group'::character varying)))
                     ->  Seq Scan on acl acl_4  (cost=0.00..2.55 rows=1 width=59) (actual time=0.06..0.30 rows=7 loops=1)
                           Filter: (((rightname = 'SuperUser'::character varying) OR (rightname = 'OwnTicket'::character varying)) AND ((objecttype = 'RT::Queue'::character varying) OR (objecttype = 'RT::System'::character varying)) AND ((objectid = 5) OR (objecttype = 'RT::System'::character varying)))
                     ->  Materialize  (cost=441.48..441.48 rows=5 width=3452) (actual time=34.84..41.15 rows=4006 loops=7)
                           ->  Nested Loop  (cost=150.61..441.48 rows=5 width=3452) (actual time=13.04..237.89 rows=4006 loops=1)
                                 ->  Hash Join  (cost=150.61..412.49 rows=5 width=3406) (actual time=12.98..153.15 rows=4006 loops=1)
                                       Hash Cond: ("outer".id = "inner".memberid)
                                       ->  Nested Loop  (cost=0.00..261.27 rows=31 width=3402) (actual time=0.23..102.61 rows=5761 loops=1)
                                             ->  Nested Loop  (cost=0.00..110.16 rows=1 width=3394) (actual time=0.10..13.88 rows=325 loops=1)
                                                   ->  Seq Scan on principals principals_1  (cost=0.00..104.34 rows=1 width=4) (actual time=0.02..6.38 rows=326
loops=1)
                                                         Filter: ((disabled = 0::smallint) AND (principaltype = 'User'::character varying))
                                                   ->  Index Scan using users_pkey on users main  (cost=0.00..5.81 rows=1 width=3390) (actual time=0.01..0.01 rows=1 loops=326)
                                                         Index Cond: ((main.id = "outer".id) AND ("outer".id = main.id))
                                             ->  Index Scan using cachedgroupmembers2 on cachedgroupmembers cachedgroupmembers_5  (cost=0.00..150.48 rows=51 width=8) (actual time=0.02..0.12 rows=18 loops=325)
                                                   Index Cond: (cachedgroupmembers_5.memberid = "outer".id)
                                       ->  Hash  (cost=150.48..150.48 rows=51 width=4) (actual time=0.25..0.25 rows=0 loops=1)
                                             ->  Index Scan using cachedgroupmembers3 on cachedgroupmembers cachedgroupmembers_6  (cost=0.00..150.48 rows=51 width=4) (actual time=0.15..0.22 rows=13 loops=1)
                                                   Index Cond: (groupid = 4)
                                 ->  Index Scan using principals_pkey on principals principals_3  (cost=0.00..5.95 rows=1 width=46) (actual time=0.01..0.01 rows=1 loops=4006)
                                       Index Cond: ("outer".groupid = principals_3.id)
               ->  Seq Scan on groups groups_2  (cost=0.00..307.80 rows=1 width=236) (actual time=0.03..43.74 rows=338 loops=28042)
                     Filter: ((("domain" = 'SystemInternal'::character varying)
OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR ("domain" = 'RT::Ticket-Role'::character varying) OR ("domain" = 'RT::Queue-Role'::character varying)) AND (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR (instance = 388) OR ("domain" = 'RT::Queue-Role'::character varying)) AND (("domain" = 'SystemInternal'::character
varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR ("domain" = 'RT::Ticket-Role'::character varying) OR (instance = 5)) AND (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR (instance = 388) OR (instance = 5)))
 Total runtime: 1264919.82 msec
(29 rows)
 


More information about the rt-users mailing list