Postgresql performance loading global user rights page in 4.4.2

1

Hi,

Pulling up the Global->User Rights page on our existing RT 3.8.13 system takes about 3 secs. On an import/upgrade of the same database to RT 4.4.2, the same page takes 5 minutes to load. Here is the query that is running:

SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers Cac
hedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN GroupMembers GroupMembers_4 ON ( GroupMembers_4.MemberId = Principals_1.id )
JOIN Groups Groups_5 ON ( Groups_5.id = GroupMembers_4.GroupId ) WHERE ((ACL_3.ObjectType = ‘RT::System’ AND ACL_3.ObjectId = 1)) AND (ACL_3.PrincipalI
d = GroupMembers_4.GroupId) AND (ACL_3.PrincipalType = ‘Group’) AND (ACL_3.RightName IS NOT NULL) AND (CachedGroupMembers_2.Disabled = ‘0’) AND (CachedGroup
Members_2.GroupId = ‘4’) AND (LOWER(Groups_5.Domain) = ‘aclequivalence’) AND (LOWER(Groups_5.Name) = ‘userequiv’) AND (Principals_1.Disabled = ‘0’) AND (Pri
ncipals_1.PrincipalType = ‘User’) AND (Principals_1.id != ‘1’) ORDER BY main.Name ASC

And here is the EXPLAIN for the query:

EXPLAIN (BUFFERS,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 GroupMembers GroupMembers_4 ON ( GroupMembers_4.MemberId = Principals_1.id ) JOIN Groups Groups_5 ON ( Groups_5.id = GroupMembers_4.GroupId ) WHERE ((ACL_3.ObjectType = ‘RT::System’ AND ACL_3.ObjectId = 1)) AND (ACL_3.PrincipalId = GroupMembers_4.GroupId) AND (ACL_3.PrincipalType = ‘Group’) AND (ACL_3.RightName IS NOT NULL) AND (CachedGroupMembers_2.Disabled = ‘0’) AND (CachedGroupMembers_2.GroupId = ‘4’) AND (LOWER(Groups_5.Domain) = ‘aclequivalence’) AND (LOWER(Groups_5.Name) = ‘userequiv’) AND (Principals_1.Disabled = ‘0’) AND (Principals_1.PrincipalType = ‘User’) AND (Principals_1.id != ‘1’) ORDER BY main.Name ASC
rt4_test-# ;

                                                                   QUERY PLAN

Unique (cost=265.67…265.75 rows=1 width=339) (actual time=300334.261…300334.319 rows=28 loops=1)
Buffers: shared hit=51961034
→ Sort (cost=265.67…265.68 rows=1 width=339) (actual time=300334.259…300334.262 rows=46 loops=1)
Sort Key: main.name, main.id, main.password, main.comments, main.signature, main.emailaddress, main.freeformcontactinfo, main.organization, main.realname, main.nickname, main.lang, main.gecos, main.homephone, main.workphone, main.mobilephone, main.pagerphone, main.address1, main.address2, main.city, main.state, main.zip, main.country, main.timezone, main.creator, main.created, main.lastupdatedby, main.lastupdated, main.authtoken, main.smimecertificate
Sort Method: quicksort Memory: 46kB
Buffers: shared hit=51961034
→ Nested Loop (cost=2.42…265.66 rows=1 width=339) (actual time=2.120…300333.664 rows=46 loops=1)
Buffers: shared hit=51961025
→ Nested Loop (cost=1.99…265.04 rows=1 width=347) (actual time=0.237…300298.204 rows=8517 loops=1)
Buffers: shared hit=51926906
→ Nested Loop (cost=1.71…85.73 rows=1 width=343) (actual time=0.109…3521.632 rows=625573 loops=1)
Join Filter: (principals_1.id = main.id)
Buffers: shared hit=2506638
→ Nested Loop (cost=1.29…85.27 rows=1 width=16) (actual time=0.096…402.074 rows=625573 loops=1)
Buffers: shared hit=4264
→ Nested Loop (cost=0.86…80.80 rows=1 width=8) (actual time=0.071…6.167 rows=350 loops=1)
Buffers: shared hit=1415
→ Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_2 (cost=0.43…4.61 rows=9 width=4) (actual time=0.048…0.242 rows=352 loops=1)
Index Cond: ((groupid = 4) AND (disabled = ‘0’::smallint))
Heap Fetches: 0
Buffers: shared hit=5
→ Index Scan using principals_pkey on principals principals_1 (cost=0.43…8.46 rows=1 width=4) (actual time=0.015…0.016 rows=1 loops=352)
Index Cond: (id = cachedgroupmembers_2.memberid)
Filter: ((id <> 1) AND (disabled = ‘0’::smallint) AND ((principaltype)::text = ‘User’::text))
Rows Removed by Filter: 0
Buffers: shared hit=1410
→ Index Only Scan using shredder_gm1 on groupmembers groupmembers_4 (cost=0.43…3.09 rows=139 width=8) (actual time=0.011…0.635 rows=1787 loops=350)
Index Cond: (memberid = principals_1.id)
Heap Fetches: 104
Buffers: shared hit=2849
→ Index Scan using users_pkey on users main (cost=0.42…0.44 rows=1 width=339) (actual time=0.003…0.004 rows=1 loops=625573)
Index Cond: (id = groupmembers_4.memberid)
Buffers: shared hit=2502374
→ Index Only Scan using acl1 on acl acl_3 (cost=0.29…179.30 rows=1 width=4) (actual time=0.473…0.474 rows=0 loops=625573)
Index Cond: ((rightname IS NOT NULL) AND (objecttype = ‘RT::System’::text) AND (objectid = 1) AND (principaltype = ‘Group’::text) AND (principalid = groupmembers_4.groupid))
Heap Fetches: 0
Buffers: shared hit=49420268
→ Index Scan using groups_pkey on groups groups_5 (cost=0.43…0.61 rows=1 width=4) (actual time=0.004…0.004 rows=0 loops=8517)
Index Cond: (id = groupmembers_4.groupid)
Filter: ((lower((domain)::text) = ‘aclequivalence’::text) AND (lower((name)::text) = ‘userequiv’::text))
Rows Removed by Filter: 1
Buffers: shared hit=34119
Planning time: 6.077 ms
Execution time: 300334.575 ms
(44 rows)

Time: 300346.437 ms

That is 100x slower than the 3.8.x version. What can be done to improve that?

Regards,
Ken

2

Hi,

Are other people observing long times to display Global->User Rights in RT 4.4.2? Is there something wrong with my database?

Regards,
Ken

3

Hi,

In RT 3.8.x this is the code for displaying the UserRights edit page:

Find out which users we want to display ACL selects for

my $Privileged = RT::Group->new($session{‘CurrentUser’});
$Privileged->LoadSystemInternalGroup(‘Privileged’);
my $Users = $Privileged->UserMembersObj();
$Users->OrderBy( FIELD => $UserOrderBy, ORDER => $UserOrder );

}}}

</%INIT>

<%ARGS>
$UserOrderBy => ‘Name’
$UserOrder => ‘ASC’
</%ARGS>

While in RT 4.4.2 it uses the following call:

<%INIT>
my @results = ProcessACLs(%ARGS);
my @principals = GetPrincipalsMap($RT::System, ‘Users’);
</%INIT>

Which is in Interface/Web.pm:

    elsif (/Users/) {
        my $Users = RT->PrivilegedUsers->UserMembersObj();
        $Users->OrderBy( FIELD => 'Name', ORDER => 'ASC' );

        # Only show users who have rights granted on this object
        my $group_members = $Users->WhoHaveGroupRight(
            Right   => '',
            Object  => $object,
            IncludeSystemRights => 0,
            IncludeSubgroupMembers => 0,
        );

        # Limit to UserEquiv groups
        my $groups = $Users->Join(
            ALIAS1 => $group_members,
            FIELD1 => 'GroupId',
            TABLE2 => 'Groups',
            FIELD2 => 'id',
        );
        $Users->Limit( ALIAS => $groups, FIELD => 'Domain', VALUE => 'ACLEquivalence', CASESENSITIVE => 0 );
        $Users->Limit( ALIAS => $groups, FIELD => 'Name', VALUE => 'UserEquiv', CASESENSITIVE => 0 );

        push @map, [
           'Users' => $Users,  # loc_left_pair
            'Format' => 0
        ];
    }
}
return @map;

It definitely looks like 4.4.2 is doing much more work that 3.8.13. Is there anything that can be done to make the performance more reasonable? 5 minutes to pull the page is going to leave a very poor impression about the “new and improved…”.

Regards,
Ken

4

To followup, I just tested this query and it is now performing well. I must not have run an ANALYZE and had bad statistics.

Regards,
Ken