<font size=2 face="sans-serif">I just upgraded from 4.0.7 to 4.2.9 and
I am running to some very slow page loads that were not an issue with the
old version of RT.</font>
<br>
<br><font size=2 face="sans-serif">When simply requesting the new search
page (</font><a href="https://rt.mcls.org/Search/Build.html?NewQuery=1"><font size=2 color=blue face="sans-serif">/Search/Build.html?NewQuery=1</font></a><font size=2 face="sans-serif">)
it takes about 20 seconds to load the page. PostgreSQL (version 9.3.5)
logs this slow query.</font>
<br>
<br><font size=3 face="Monaco">LOG: duration: 20985.257 ms execute
<unnamed>: SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL
ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id
) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId
= Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 ON
( CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE ((ACL_3.ObjectType
= 'RT::Queue') OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId
= 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType
= 'Group') AND (ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser')
AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId
= '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled
= '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id !=
'1') ORDER BY main.Name ASC</font>
<br>
<br><font size=2 face="sans-serif">this (slow) query also shows up when
look at the Display/Basics/People/Jumbo tabs of a ticket in the general
queue, but not in other queues.</font>
<br>
<br>
<br><font size=2 face="sans-serif">here is the result of the db expain.
</font>
<br>
<br><font size=2 face="Courier New">rt4data=# explain analyze SELECT DISTINCT
main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1
ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2
ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers
CachedGroupMembers_4 ON ( CachedGroupMembers_4.MemberId = Principals_1.id
) WHERE ((ACL_3.ObjectType = 'RT::Ticket' AND ACL_3.ObjectId
= 62017) OR (ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId =
1) OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1))
AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType
= 'Group') AND (ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser')
AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId
= '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled
= '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id !=
'1') ORDER BY main.Name ASC;</font>
<br><font size=2 face="Courier New">
</font>
<br><font size=2 face="Courier New">QUERY PLAN
</font>
<br><font size=2 face="Courier New">
</font>
<br><font size=2 face="Courier New"> </font>
<br><font size=2 face="Courier New">------------------------------------------------------------------------------------------------------------------------------------------------------</font>
<br><font size=2 face="Courier New">------------------------------------------------------------------------------------------------------------------------------------------------------</font>
<br><font size=2 face="Courier New">------------------------------------------------------------------------------------------------------------------------------------------------------</font>
<br><font size=2 face="Courier New">------------------------------------------------------------------------------------------------------------------------------------------------------</font>
<br><font size=2 face="Courier New">------</font>
<br><font size=2 face="Courier New"> Unique (cost=681.60..681.69
rows=1 width=1136) (actual time=20987.915..20988.043 rows=18 loops=1)</font>
<br><font size=2 face="Courier New"> -> Sort (cost=681.60..681.60
rows=1 width=1136) (actual time=20987.911..20987.919 rows=64 loops=1)</font>
<br><font size=2 face="Courier New"> Sort
Key: main.name, main.id, main.password, main.comments, main.signature,
main.emailaddress, main.freeformcontactinfo, main.organization, m</font>
<br><font size=2 face="Courier New">ain.realname, main.nickname, main.lang,
main.emailencoding, main.webencoding, main.externalcontactinfoid, main.contactinfosystem,
main.externalauthid,</font>
<br><font size=2 face="Courier New"> main.authsystem, main.gecos,
main.homephone, main.workphone, main.mobilephone, main.pagerphone, main.address1,
main.address2, main.city, main.state, </font>
<br><font size=2 face="Courier New">main.zip, main.country, main.timezone,
main.pgpkey, main.creator, main.created, main.lastupdatedby, main.lastupdated,
main.authtoken, main.smimecertif</font>
<br><font size=2 face="Courier New">icate</font>
<br><font size=2 face="Courier New"> Sort
Method: quicksort Memory: 57kB</font>
<br><font size=2 face="Courier New"> ->
Nested Loop (cost=1.83..681.59 rows=1 width=1136) (actual time=1.680..20985.026
rows=64 loops=1)</font>
<br><font size=2 face="Courier New">
-> Nested Loop (cost=1.55..645.50 rows=2
width=1140) (actual time=0.097..163.455 rows=90643 loops=1)</font>
<br><font size=2 face="Courier New">
-> Nested Loop (cost=1.13..643.25
rows=1 width=1144) (actual time=0.057..1.141 rows=36 loops=1)</font>
<br><font size=2 face="Courier New">
-> Nested
Loop (cost=0.71..623.94 rows=13 width=1140) (actual time=0.044..0.578
rows=36 loops=1)</font>
<br><font size=2 face="Courier New">
-> Index Only Scan using disgroumem on cachedgroupmembers
cachedgroupmembers_2 (cost=0.42..189.79 rows=83 width=4) </font>
<br><font size=2 face="Courier New">(actual time=0.031..0.109 rows=37 loops=1)</font>
<br><font size=2 face="Courier New">
Index Cond: ((groupid = 4) AND (disabled = 0))</font>
<br><font size=2 face="Courier New">
Heap Fetches: 37</font>
<br><font size=2 face="Courier New">
-> Index Scan using users_pkey on users main (cost=0.29..5.22
rows=1 width=1136) (actual time=0.008..0.010 rows=1 l</font>
<br><font size=2 face="Courier New">oops=37)</font>
<br><font size=2 face="Courier New">
Index Cond: (id = cachedgroupmembers_2.memberid)</font>
<br><font size=2 face="Courier New">
-> Index
Scan using principals_pkey on principals principals_1 (cost=0.42..1.48
rows=1 width=4) (actual time=0.013..0.014</font>
<br><font size=2 face="Courier New"> rows=1 loops=36)</font>
<br><font size=2 face="Courier New">
Index Cond: (id = main.id)</font>
<br><font size=2 face="Courier New">
Filter: ((id <> 1) AND (disabled = 0) AND ((principaltype)::text
= 'User'::text))</font>
<br><font size=2 face="Courier New">
-> Index Scan using cachedgroupmembers1
on cachedgroupmembers cachedgroupmembers_4 (cost=0.42..2.18 rows=7
width=8) (actual tim</font>
<br><font size=2 face="Courier New">e=0.013..3.208 rows=2518 loops=36)</font>
<br><font size=2 face="Courier New">
Index Cond:
(memberid = principals_1.id)</font>
<br><font size=2 face="Courier New">
Filter: (disabled
= 0)</font>
<br><font size=2 face="Courier New">
-> Index Only Scan using acl1 on acl acl_3
(cost=0.28..18.04 rows=1 width=4) (actual time=0.229..0.229 rows=0
loops=90643)</font>
<br><font size=2 face="Courier New">
Index Cond: ((principaltype =
'Group'::text) AND (principalid = cachedgroupmembers_4.groupid))</font>
<br><font size=2 face="Courier New">
Filter: ((((rightname)::text =
'OwnTicket'::text) OR ((rightname)::text = 'SuperUser'::text)) AND ((((objecttype)::text
= 'RT::Ti</font>
<br><font size=2 face="Courier New">cket'::text) AND (objectid = 62017))
OR (((objecttype)::text = 'RT::Queue'::text) AND (objectid = 1)) OR (((objecttype)::text
= 'RT::System'::text) AN</font>
<br><font size=2 face="Courier New">D (objectid = 1))))</font>
<br><font size=2 face="Courier New">
Rows Removed by Filter: 0</font>
<br><font size=2 face="Courier New">
Heap Fetches: 20424</font>
<br><font size=2 face="Courier New"> Total runtime: 20988.297 ms</font>
<br><font size=2 face="Courier New">(25 rows)</font>
<br>
<br>
<br>
<br><font size=2 face="sans-serif">Any suggestions?</font>
<br>
<br>
<br><font size=2 face="sans-serif"><br>
</font>
<br><font size=2 face="sans-serif">Thanks,<br>
---<br>
Mark Szidik<br>
Midwest Collaborative for Library Services<br>
1407 Rensen Street, Suite 1, Lansing, MI 48910-3657<br>
Ph:800.530.9019 x117 Fax:517.492.3881<br>
Ph:517.492.3817</font>