[rt-users] Performance Issues after 3.8.0 upgrade -- PostgreSQL delays

[Ruslan Zakirov]

I'm not big expert on optimizing Pg queries using indexes. Below
you'll find suggestions for some.

But please run EXPLAIN for each query before creating any index and
after. Without explains we can not help you precisely, just guessing.
Also, please use some real constants in EXPLAIN, random strings and
numbers will generate plans far from reality.

On Fri, Jul 18, 2008 at 3:27 AM, Jessie Bryan <jessie.bryan at gmail.com> wrote:
> On Wed, Jul 16, 2008 at 8:58 PM, Jessie Bryan <jessie.bryan at gmail.com> wrote:
>> Update-
>>
>> Ok, after several hours of logging, what I see most common in the slow
>> query (1s) log is:
>>
>> LOG:  duration: 3151.786 ms  statement: EXECUTE <unnamed>  [PREPARE:
>> SELECT  * FROM Groups WHERE LOWER(Domain) = LOWER($1) AND LOWER(Type)
>> = LOWER($2)]

EXPLAIN ...
CREATE INDEX RUZ_G1 ON Groups(LOWER(Domain), LOWER(Type));
EXPLAIN ...

>>
>> Several hundred of these queries in the slow query log data.
>>
>
> Any suggestions - or more information I can provide? the slow query
> log is pretty lengthy...
> It's most common slow query is listed above, as well as many of these below:
>
> <2008-07-17 12:49:03 PDT> LOG:  duration: 3737.479 ms  statement:
> EXECUTE <unnamed>  [PREPARE:  SELECT  * FROM GroupMembers WHERE
> GroupId = $1 AND MemberId = $2]
EXPLAIN ...
CREATE INDEX RUZ_GM1 ON GroupMembers(GroupId, MemberId);
EXPLAIN ...

>
> <2008-07-17 17:20:43 PDT> LOG:  duration: 8930.945 ms  statement:
> SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4 JOIN
> Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN
> CachedGroupMembers CachedGroupMembers_2  ON (
> CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3
>  ON ( Groups_3.id = CachedGroupMembers_2.GroupId )  WHERE
> (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType =
> Groups_3.Type) AND (Principals_1.id != '1') AND
> (Principals_1.PrincipalType = 'User') AND (ACL_4.RightName =
> 'OwnTicket') AND (Groups_3.Domain = 'RT::System-Role') AND
> ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType =
> 'RT::System'))  ORDER BY main.Name ASC
Generate an explain, pretty hard to guess what's wrong.


> <2008-07-17 13:42:06 PDT> LOG:  duration: 9115.251 ms  statement:
> SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
> Groups Groups_1  ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
> Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
> CachedGroupMembers CachedGroupMembers_2  ON (
> CachedGroupMembers_2.MemberId = Users_3.id ) AND (
> CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE (Users_3.id =
> '5698') AND (main.Status != 'deleted') AND ( ( CachedGroupMembers_2.id
> IS NOT NULL )  AND  ( main.Status = 'open' OR main.Status = 'new' ) )
> AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)  ORDER BY
> main.Priority DESC  LIMIT 10

This one is request for "More about user XXX" box on the main page of a ticket.
EXPLAIN ...
CREATE INDEX RUZ_CGM1 ON CachedGroupMembers(MemberId, GroupId, Disabled);
EXPLAIN ...

[snip]

> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>



-- 
Best regards, Ruslan.