As a Jira Admin, I would like to add an index by ISSUE_ID column on the table moved_issue_key

Summary

1) When moving an issue between projects or bulk moving multiples issues, we can observe that the query below consumes much of the active connection pool and db cpu.

• Report from DB:

  "SELECT OLD_ISSUE_KEY FROM moved_issue_key WHERE ISSUE_ID=:1 ORDER BY ID"

  Id	Operation	Name	Rows	Bytes	Cost (%CPU)	Time
  0	SELECT STATEMENT				84 (100)
  1	SORT ORDER BY		1	21	84 (3)	00:00:01
  2	TABLE ACCESS FULL	MOVED_ISSUE_KEY	1	21	83 (2)	00:00:01

• Query is doing full table scans
• moved_issue_key table structure:

             Table "public.moved_issue_key"
      Column     |          Type          | Modifiers
  ---------------+------------------------+-----------
   id            | numeric(18,0)          | not null
   old_issue_key | character varying(255) |
   issue_id      | numeric(18,0)          |
  Indexes:
      "pk_moved_issue_key" PRIMARY KEY, btree (id)
      "idx_old_issue_key" UNIQUE, btree (old_issue_key)
  

2) Customers facing issue JSWSERVER-20612 Dev.Status can get stuck paging over issue updates from Bitbucket Server, Bamboo or Fecru and cause excessive load on database can minimize the DB impact by adding this index:

CREATE INDEX idx_issue_id ON moved_issue_key (issue_id);

Suggested Solution

Add an index on the issue_id column for the table moved_issue_key

Workaround

Add index manually

CREATE INDEX CONCURRENTLY idx_issue_id ON moved_issue_key (issue_id);