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

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:

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