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

Issue Summary

Method com.atlassian.jira.project.version.VersionCustomFieldStore#getCustomFieldsTypesUsing is slow for Jira instances with a large customfieldvalue table. This is caused by slow and non-optimal SQL executed by the code:

This is reproducible on Data Center: Yes

Steps to Reproduce

1. Large Jira instance. Specific table customfieldvalue has 50M+ rows.
2. Execute the REST API DELETE call- /rest/api/2/version/<version_id>

Expected Results

The execution should be fast

Actual Results

The REST call takes more than a minute as the below constructed SQL is not optimized properly.

atlassian-jira.log:2023-08-07 17:02:04,212+0530 http-nio-8080-exec-19 DEBUG kpalanisamy 1022x1111x1 1iyirps 0:0:0:0:0:0:0:1 /rest/api/2/version/12104 [c.querydsl.sql.AbstractSQLQuery] select distinct CUSTOM_FIELD_VALUE.customfield from (select CUSTOM_FIELD.id from public.customfield CUSTOM_FIELD where CUSTOM_FIELD.customfieldtypekey = ? or CUSTOM_FIELD.customfieldtypekey = ?) as CUSTOM_FIELD left join (select CUSTOM_FIELD_VALUE.customfield from public.customfieldvalue CUSTOM_FIELD_VALUE where CUSTOM_FIELD_VALUE.numbervalue = ?) as CUSTOM_FIELD_VALUE on CUSTOM_FIELD.id = CUSTOM_FIELD_VALUE.customfield  

Workaround

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