Performance of REST API query issue editmeta degrades with high number of versions

Issue Summary

Performance of REST API query issue editmeta degrades with a high number of versions. Jira kept spinning and couldn't return results to the REST API

<Jira_base_URL>/rest/api/2/search?expand=editmeta%2Cversions&jql=assignee+in+%28admin%2C+testuser1+%28

This is reproducible on the Data Center: Yes

Steps to Reproduce

1. Create over 1.5 million versions, 650 projects and 75000 users.
2. Create some issue tickets randomly in different projects and assign multiple versions and an assignee to each issue ticket.
3. Run the query below:

   <Jira_base_URL>/rest/api/2/search?expand=editmeta%2Cversions&jql=assignee+in+%28admin%2C+testuser1+%28
   

Expected Results

If 47 records will return when running similar JQL in Jira UI, we expect 47 records will be returned by the REST API request.

Actual Results

If 2-3 records will return when running similar JQL in Jira UI, these records can be returned by the REST API.
But if 47 records will return when running similar JQL in Jira UI, Jira keeps spinning and returns nothing.

Workaround

You may delete non-used versions to help improve the performance.
If you hit a performance issue when deleting the versions, please refer to the workaround provided in this bug ticket:

• JRASERVER-76142: REST API call- /rest/api/2/version/<version_id> to delete the version takes a long time due to the constructed SQL

Add a database index to customfieldvalue.numbervalue, for example:

create index customfieldvalue_number_value_type_key on customfieldvalue (numbervalue)

This should improve the performance of these calls, however, we estimate editing custom fields will take longer as a result of adding these database indexes. As such we strongly advise testing the impact of this change in a staging environment before applying it in production.