Date: Tue, 16 Sep 2008 13:58:00 -0500
From: Kenneth Marshall <ktm@rice.edu>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Mauri.Sahlberg@claymountain.com, pgsql-admin@postgresql.org
Subject: Re: Help request: how to tune performance?
Message-ID: <20080916185800.GQ22468@it.is.rice.edu>
References: <1221553908.3410.11.camel@fault.in.tuolla.net>
	<dcc563d10809161010j4a04a9f9ufefab82abf2dce08@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <dcc563d10809161010j4a04a9f9ufefab82abf2dce08@mail.gmail.com>
User-Agent: Mutt/1.5.16 (2007-06-09)

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@claymountain.com> 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