On Tue, Sep 16, 2008 at 11:10:01AM -0600, Scott Marlowe wrote: > On Tue, Sep 16, 2008 at 2:31 AM, Mauri Sahlberg > <Mauri.Sahlberg@xxxxxxxxxxxxxxxx> wrote: > > Hi, > > > > We just upgraded Best Practical's RT from 3.6 to 3.81 and gave the > > database completely own machine. And the users still complain that it is > > dog slow. > > Moved up from below: > > > Version : 8.1.11 Vendor: CentOS > > So, you built it its own machine, but you didn't upgrade to at least 8.2? > > Last place I worked we ran rt 3.6.1 and got a noticeable performance > boost from switching to 8.2 but the only thing that was ever really > slow was viewing the rather large approval queue. > > > :-( I installed pg_top and it seems that at the beginning of > > the ticket display RT-issues a query that eats everything the database > > has. Query is as follows: > > > > SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_2 JOIN > > Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN > > CachedGroupMembers CachedGroupMembers_3 ON > > ( CachedGroupMembers_3.MemberId = Principals_1.id ) WHERE > > (Principals_1.Disabled = '0') AND (ACL_2.PrincipalId = > > CachedGroupMembers_3.GroupId) AND (Principals_1.id != '1') AND > > (ACL_2.PrincipalType = 'Group') AND (Principals_1.PrincipalType = > > 'User') AND (ACL_2.RightName = 'OwnTicket') AND ((ACL_2.ObjectType = > > 'RT::Queue' AND ACL_2.ObjectId = 18) OR (ACL_2.ObjectType = > > 'RT::System')) ORDER BY main.Name ASC > What is your version of DBIx-SearchBuilder? In particular, the DISTINCT definition was changed from: $$statementref = "SELECT DISTINCT main.* FROM $$statementref"; to: $$statementref = "SELECT main.* FROM ( SELECT DISTINCT main.id FROM $$statementref ) distinctquery, $table main WHERE (main.id = distinctquery.id) "; You can change the line in your version of SearchBuilder and restart RT with a cache flush to get approximately the same benefit. Ken
