Uploaded image for project: 'Jira Server and Data Center'
  1. Jira Server and Data Center
  2. JRASERVER-70678

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

    XMLWordPrintable

    Details

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

      Description

      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 and scanning more than 4 lac rows for every execution.
      • 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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              imurakami@atlassian.com Isabel Murakami
              Votes:
              6 Vote for this issue
              Watchers:
              10 Start watching this issue

                Dates

                Created:
                Updated: