Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-70003

Add role_type_param_pid index to the projectroleactor table

XMLWordPrintable

    • 1
    • We collect Jira feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

      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], [PID])

              Unassigned Unassigned
              ayakovlev@atlassian.com Andriy Yakovlev [Atlassian]
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated: