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.
Using a considerably large instance (500k+ issues), perform a balance operation.
The balance completes in a suitable timeframe.
Balances can take several days to complete.
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.
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:
A proposed fix of splitting the rank into two separate columns, bucket and rank, should improve the database performance.