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

Details

    • 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.

    Description

      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])

      Attachments

        Issue Links

          is cloned from

          Suggestion - JRASERVER-70002 Add COLUMNLAYOUT index to the columnlayoutitem table

          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. JRASERVER-69446 Removing actor from project role can make Jira unresponsive

          • High - High priority issues
          • Closed
          mentioned in

          Page Loading...

          Page Loading...

          Activity

            People

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

              Dates

                Created:
                Updated: