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