Add role_type_param_pid index to the projectroleactor table

XMLWordPrintable

    • 1
    • 11

      Problem Definition

      Table projectroleactor is used for storing project level permissions, example:

      select * from projectroleactor;
  id   |  pid  | projectroleid |          roletype          |  roletypeparameter
-------+-------+---------------+----------------------------+---------------------
 10000 |       |         10000 | atlassian-group-role-actor | jira-users
 10001 |       |         10001 | atlassian-group-role-actor | jira-developers
 10002 |       |         10002 | atlassian-group-role-actor | jira-administrators
 10106 | 10001 |         10000 | atlassian-group-role-actor | jira-users
 10107 | 10001 |         10002 | atlassian-group-role-actor | jira-administrators
 11300 | 10800 |         10002 | atlassian-group-role-actor | jira-administrators
 11301 | 10800 |         10001 | atlassian-group-role-actor | jira-developers
 11303 | 10800 |         10000 | atlassian-group-role-actor | jira-administrators
 11703 | 11000 |         10002 | atlassian-user-role-actor  | admin
 ...

      Table config:

      \d projectroleactor;
            Table "public.projectroleactor"
      Column       |          Type          | Modifiers
-------------------+------------------------+-----------
 id                | numeric(18,0)          | not null
 pid               | numeric(18,0)          |
 projectroleid     | numeric(18,0)          |
 roletype          | character varying(255) |
 roletypeparameter | character varying(255) |
Indexes:
    "pk_projectroleactor" PRIMARY KEY, btree (id)
    "role_pid_idx" btree (pid)
    "role_player_idx" btree (projectroleid, pid)
      • Columns ROLETYPE and ROLETYPEPARAMETER is being used during data loading in getProjectIdsContainingRoleActorByKeyAndType
      • which is used in API getProjectIdsContainingRoleActorByNameAndType
      • and it's being used in ViewUserProjectRoles page (URL: secure/admin/user/ViewUserProjectRoles!default.jspa)

      Produced SQL:

      2019-09-23 19:37:19,973 http-nio-8080-exec-5 admin 1176x480x1 vphgel /secure/admin/user/ViewUserProjectRoles!default.jspa 2ms "SELECT DISTINCT PID FROM public.projectroleactor WHERE ROLETYPE='atlassian-user-role-actor' AND ROLETYPEPARAMETER='admin'"

      Suggested Solution

      Add an extra cover index (ROLETYPEPARAMETER, ROLETYPE, ID) (in that order since ROLETYPE has only 2 values) to the table to reduce DB load and improve loading time.

      Workaround

      Add index manually.

      • Example for MSSQL 
        CREATE NONCLUSTERED INDEX [idx_role_type_param_pid] ON [dbo].[projectroleactor] ([ROLETYPEPARAMETER], [ROLETYPE], [ID])

            Assignee:
            Unassigned
            Reporter:
            Andriy Yakovlev [Atlassian]
            Votes:
            2 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated: