[JRASERVER-70003] Add role_type_param_pid index to the projectroleactor table

Type: Suggestion
Resolution: Unresolved
Fix Version/s: None
Component/s: Data Center - Database
Labels:

UIS:
1
Feedback Policy:

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'"

Workaround

Add index manually.

Example for MSSQL

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

is cloned from

JRASERVER-70002 Add COLUMNLAYOUT index to the columnlayoutitem table

Closed

relates to

JRASERVER-69446 Removing actor from project role can make Jira unresponsive

Closed

mentioned in: Page Failed to load; Page Failed to load

Kaili Gu made changes - 2 days ago

Description

Original: h3. Problem Definition
Table {{projectroleactor}} is used for storing project level permissions, example:
{noformat}
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
...
{noformat}

Table config:
{noformat}
\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)
{noformat}

* 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:
{noformat}
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'"
{noformat}

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

h3. Workaround
Add index manually.
* Example for MSSQL
{code:sql|borderStyle=solid}
CREATE NONCLUSTERED INDEX [idx_role_type_param_pid] ON [dbo].[projectroleactor] ([ROLETYPEPARAMETER], [ROLETYPE], [PID])
{code}

New: h3. Problem Definition
Table {{projectroleactor}} is used for storing project level permissions, example:
{noformat}
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
...
{noformat}

Table config:
{noformat}
\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)
{noformat}

* 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:
{noformat}
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'"
{noformat}

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

h3. Workaround
Add index manually.
* Example for MSSQL
{code:sql|borderStyle=solid}
CREATE NONCLUSTERED INDEX [idx_role_type_param_pid] ON [dbo].[projectroleactor] ([ROLETYPEPARAMETER], [ROLETYPE], [ID])
{code}

SET Analytics Bot made changes - 05/Apr/2024 2:35 AM

UIS

Original: 0

New: 1

SET Analytics Bot made changes - 04/Apr/2024 2:31 AM

UIS

Original: 1

New: 0

SET Analytics Bot made changes - 25/Oct/2023 2:10 AM

UIS

Original: 2

New: 1

SET Analytics Bot made changes - 14/Oct/2023 2:31 AM

UIS

Original: 1

New: 2

Andrzej Kotas made changes - 11/Apr/2022 1:57 PM

Remote Link

New: This issue links to "Page (Confluence)" [ 633505 ]

Andriy Yakovlev [Atlassian] made changes - 07/Oct/2021 4:20 PM

Remote Link

New: This issue links to "Page (Confluence)" [ 589492 ]

Andriy Yakovlev [Atlassian] made changes - 20/Nov/2020 4:30 PM

Labels

Original: database pse-request

New: database database-index pse-request

Alex Stegani made changes - 30/Oct/2020 8:26 PM

Link

New: This issue relates to ~~JRASERVER-69446~~ [ ~~JRASERVER-69446~~ ]

SET Analytics Bot made changes - 25/Dec/2019 12:34 AM

UIS

Original: 2

New: 1

Assignee:: Unassigned

Reporter:: Andriy Yakovlev [Atlassian]

Votes:: 2 Vote for this issue

Watchers:: 3 Start watching this issue

Created:: 23/Sep/2019 4:48 PM

Updated:: 2 days ago 3:46 AM

Jira Data Center

Details

Description

Problem Definition

Suggested Solution

Workaround

Attachments

Issue Links

Forms

Activity

People

Dates