[rt-users] Huge Attachments table in 3.4.4

[Dan O'Neill]

[ lots of info cut for brevity except the problem query ]

> SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL
> ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4  WHERE
> ((ACL_2.RightName = 'OwnTicket')) AND ((CachedGroupMembers_4.MemberId =
> Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND
> ((Principals_1.Disabled = '0')or(Principals_1.Disabled = '0')) AND
> ((Principals_1.id != '1')) AND ((main.id = Principals_1.id)) AND  ( (
> ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = 'Group' AND
> (   Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain =
> 'UserDefined' OR Groups_3.Domain = 'ACLEquivalence')) OR ( (
> (Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = 23)  OR (
> Groups_3.Domain = 'RT::Ticket-Role' AND Groups_3.Instance = 238803)  )
> AND Groups_3.Type = ACL_2.PrincipalType) )  AND (ACL_2.ObjectType =
> 'RT::System' OR  (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId =
> 23) )    ORDER BY main.Name ASC;

Maybe this is the answer:

http://rt3.fsck.com/Ticket/Display.html?id=6624

  Note: The above link used to work, now it asks
        me for a login.  We add these indicies to
        speed up the ACL processing stuff.

Jesse, isn't the RT bug database open for public review?

Here are the postgresql index creation instructions.

create index groups3 on groups(instance);
create index groups4 on groups(lower(type));
create index groups5 on groups(lower(domain));


You may also want to add more RAM to your server. We also found that
when the sessions table gets large that some degredation tends to occur.
Here is the postgresql statement that we use to clean out older sessions.

DELETE FROM sessions WHERE lastupdated < (NOW() - INTERVAL '3 DAY')

Let us all know your results. Thanks.

dano