Uploaded image for project: 'Jira Software Server and Data Center'
  1. Jira Software Server and Data Center
  2. JSWSERVER-13163

LexoRank database query performance is slow due to the way the field is constructed

    XMLWordPrintable

    Details

      Description

      Summary

      The Rank field in the LexoRank table is an concatenation of bucket(b) and rank(rrrr) of the form b|rrrrr where bucket is a 0, 1,or 2. During a rebalance the bucket is changed for all rows and so all database statistics are invalid and database optimisation planning is predictably destroyed.

      This presents as performance problems during balancing operations, such as balance taking considerably long, and can also impact the performance of database querying on the LexoRank table, which is impacted when creating issues or any LexoRank operations that access the database rather than the indexes.

      Steps to Reproduce

      Using a considerably large instance (500k+ issues), perform a balance operation.

      Expected Results

      The balance completes in a suitable timeframe.

      Actual Results

      Balances can take several days to complete.

      Workaround

      Reducing the number of Rank fields to 1 is known to provide some performance improvements, as is upgrading to Agile 6.7.10 to fix JSW-11491. Also if using SQL Server, the workaround in JSW-12221 may provide some performance improvements.

      Notes

      For example, the query plan explain analyze SELECT * FROM public."AO_60DB71_LEXORANK" WHERE "FIELD_ID" = 10400 AND "RANK" LIKE '2|%' ORDER BY "RANK" DESC LIMIT 1; looks like:

      "Before vacuum analyse"

      "Limit (cost=137.89..137.89 rows=1 width=83) (actual time=1.661..1.661 rows=0 loops=1)"
      " -> Sort (cost=137.89..137.89 rows=1 width=83) (actual time=1.659..1.659 rows=0 loops=1)"
      " Sort Key: "RANK""
      " Sort Method: quicksort Memory: 25kB"
      " -> Seq Scan on "AO_60DB71_LEXORANK" (cost=0.00..137.88 rows=1 width=83) (actual time=1.623..1.623 rows=0 loops=1)"
      " Filter: ((("RANK")::text ~~ '1|%'::text) AND ("FIELD_ID" = 10400))"
      " Rows Removed by Filter: 4392"
      "Planning time: 0.872 ms"
      "Execution time: 1.706 ms"

      "After vacuum analyse"

      "Limit (cost=0.28..0.42 rows=1 width=83) (actual time=0.018..0.018 rows=1 loops=1)"
      " -> Index Scan Backward using index_ao_60db71_lexorank_rank on "AO_60DB71_LEXORANK" (cost=0.28..606.12 rows=4392 width=83) (actual time=0.017..0.017 rows=1 loops=1)"
      " Filter: ((("RANK")::text ~~ '2|%'::text) AND ("FIELD_ID" = 10400))"
      "Planning time: 0.129 ms"
      "Execution time: 0.040 ms"

      A proposed fix of splitting the rank into two separate columns, bucket and rank, should improve the database performance.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              dcurrie@atlassian.com Dave C
              Votes:
              9 Vote for this issue
              Watchers:
              27 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: