This one time, at band camp, Jesse Vincent wrote:
Can you tell me exactly what query it was stuck on? 13 minutes on a
query really sounds like your system must be thrashing to hell and back.
Is the machine swapping heavily?
As I said ealier this thread (and I’ve just rechecked now to make sure, by
turning statement logging on in postgres) the query is this:
SELECT DISTINCT main.* FROM Users main, Principals Principals_1, Groups Groups_2, Principals Principals_3, Principals Principals_4, ACL ACL_5, CachedGroupMembers CachedGroupMembers_6, CachedGroupMembers CachedGroupMembers_7 WHERE ((ACL_5.RightName = ‘SuperUser’)OR(ACL_5.RightName = ‘OwnTicket’)) AND ((CachedGroupMembers_7.GroupId = ‘4’)) AND ((Principals_1.Disabled = ‘0’)) AND ((Principals_1.PrincipalType = ‘User’)) AND ((Principals_3.PrincipalType = ‘User’)) AND (ACL_5.ObjectType = ‘RT::System’ OR (ACL_5.ObjectType = ‘RT::Queue’ AND ACL_5.ObjectId = ‘4’) ) AND ( (ACL_5.PrincipalId = Principals_4.Id AND Principals_4.id = Groups_2.Id AND ACL_5.PrincipalType = ‘Group’ AND (Groups_2.Domain = ‘SystemInternal’ OR Groups_2.Domain = ‘UserDefined’ OR Groups_2.Domain = ‘ACLEquivalence’)) OR ( ( (Groups_2.Domain = ‘RT::Queue-Role’ AND Groups_2.Instance = ‘4’) OR ( Groups_2.Domain = ‘RT::Ticket-Role’ AND Groups_2.Instance = ‘376’) ) AND Groups_2.Type = ACL_5.PrincipalType AND Groups_2.Id = Principals_4.id AND Principals_4.PrincipalType = ‘Group’) ) AND main.id = Principals_1.id AND Principals_1.id = CachedGroupMembers_7.MemberId AND main.id = Principals_3.id AND CachedGroupMembers_6.MemberId = Principals_3.Id AND CachedGroupMembers_6.GroupId = Principals_4.Id ORDER BY main.Name ASC
Which I believe to be the query that’s filling out the Owner: dropdown list on
the Update ticket page.
The machine isn’t swapping excessively, there is almost no load on it at all
until this query hits the database.
I’ve added the output of EXPLAIN on that queryi below, which is
unintelligible to me but our postgres guru thinks is odd in that the nesting
has that V pattern.
Given that I can’t see any speedup in the query, is there any way for me to
check that RT is using the right version of DBIx::SearchBuilder?
Unique (cost=176.88…176.97 rows=1 width=1433)
→ Sort (cost=176.88…176.89 rows=1 width=1433)
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=32.57…176.87 rows=1 width=1433)
Join Filter: (((“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 = ‘376’::character varying) OR (“inner”.“domain” = ‘RT::Queue-Role’::character varying)) AND ((“outer”.id = “inner”.id) OR (“inner”.“domain” = ‘RT::Ticket-Role’::character varying) OR (“inner”.instance = ‘4’::character varying)) AND ((“outer”.id = “inner”.id) OR (“inner”.instance = ‘376’::character varying) OR (“inner”.instance = ‘4’::character varying)) 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”.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 = ‘376’::character varying) OR (“inner”.“domain” = ‘RT::Queue-Role’::character varying)) AND ((“outer”.principalid = “outer”.id) OR (“inner”.“domain” = ‘RT::Ticket-Role’::character varying) OR (“inner”.instance = ‘4’::character varying)) AND ((“outer”.principalid = “outer”.id) OR (“inner”.instance = ‘376’::character varying) OR (“inner”.instance = ‘4’::character varying)) 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”.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 = ‘376’::character varying) 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 = ‘4’::character varying)) AND ((“outer”.principaltype = ‘Group’::character varying) OR (“inner”.instance = ‘376’::character varying)
OR (“inner”.instance = ‘4’::character varying)) 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=32.57…64.27 rows=1 width=1396)
Join Filter: (((“inner”.principalid = “outer”.id) OR (“outer”.principaltype = ‘Group’::character varying)) AND ((“inner”.principaltype = ‘Group’::character varying) OR (“outer”.principaltype = ‘Group’::character varying)))
→ Nested Loop (cost=32.57…62.05 rows=1 width=1383)
Join Filter: (“outer”.id = “inner”.memberid)
→ Nested Loop (cost=32.57…56.11 rows=1 width=1379)
→ Nested Loop (cost=32.57…50.25 rows=1 width=1367)
→ Nested Loop (cost=32.57…40.08 rows=1 width=1359)
Join Filter: (“outer”.id = “inner”.id)
→ Hash Join (cost=32.57…34.21 rows=1 width=1355)
Hash Cond: (“outer”.id = “inner”.id)
→ Seq Scan on users main (cost=0.00…1.36 rows=36 width=1351)
→ Hash (cost=32.48…32.48 rows=37 width=4)
→ Seq Scan on principals principals_3 (cost=0.00…32.48 rows=37 width=4)
Filter: (principaltype = ‘User’::character varying)
→ Index Scan using principals_pkey on principals principals_1 (cost=0.00…5.86 rows=1 width=4)
Index Cond: (“outer”.id = principals_1.id)
Filter: ((disabled = 0::smallint) AND (principaltype = ‘User’::character varying))
→ Index Scan using cachedgroupmembers2 on cachedgroupmembers cachedgroupmembers_6 (cost=0.00…10.14 rows=2 width=8)
Index Cond: (cachedgroupmembers_6.memberid = “outer”.id)
→ Index Scan using principals_pkey on principals principals_4 (cost=0.00…5.85 rows=1 width=12)
Index Cond: (“outer”.groupid = principals_4.id)
→ Index Scan using disgroumem on cachedgroupmembers cachedgroupmembers_7 (cost=0.00…5.93 rows=1 width=4)
Index Cond: ((cachedgroupmembers_7.groupid = 4) AND (“outer”.id = cachedgroupmembers_7.memberid))
→ Seq Scan on acl acl_5 (cost=0.00…2.12 rows=5 width=13)
Filter: (((rightname = ‘SuperUser’::character varying) OR (rightname = ‘OwnTicket’::character varying)) AND ((objecttype = ‘RT::Queue’::character varying) OR (objecttype = ‘RT::System’::character varying)) AND ((objectid = 4) OR (objecttype = ‘RT::System’::character varying)))
→ Seq Scan on groups groups_2 (cost=0.00…112.06 rows=3 width=37)
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 = ‘376’::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 (“domain” = ‘RT::Ticket-Role’::character varying) OR (instance = ‘4’::character varying)) AND ((“domain” = ‘SystemInternal’::character varying) OR (“domain” = ‘UserDefined’::character varying) OR (“domain” = ‘ACLEquivalence’::character varying) OR (instance = ‘376’::character varying) OR (instance = ‘4’::character varying)))
(32 rows)
jaq@spacepants.org http://spacepants.org/jaq.gpg
It’s quite