Summary

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

select CUSTOM_FIELD_VALUE.customfield, CUSTOM_FIELD.cfname, count(distinct CUSTOM_FIELD_VALUE.issue)
from public.customfieldvalue CUSTOM_FIELD_VALUE
left join public.customfield CUSTOM_FIELD
on CUSTOM_FIELD_VALUE.customfield = CUSTOM_FIELD.id
where CUSTOM_FIELD_VALUE.numbervalue = ? and (CUSTOM_FIELD.customfieldtypekey = ? or CUSTOM_FIELD.customfieldtypekey = ?)
group by CUSTOM_FIELD.cfname, CUSTOM_FIELD_VALUE.customfield

Environment

• Large Jira instance. Specific table customfieldvalue has 50M+ rows.

Steps to Reproduce

Execute getCustomFieldsUsing method. One of the ways: trigger /rest/api/2/version/<VerID>/relatedIssueCounts REST API call

Expected Results

Execution is fast: ~ seconds

Actual Results

Execution is slow: 3 minutes

Notes

• Also note, that deleting any version from Release page will take a really long time for large instance. This includes:
  • Deleting version without issues associated to it
  • Deleting version with some issues associated to it

Steps:

1. Go to Releases page of a project
2. Try to delete any version

• Before deleting the version at Releases view, Jira calls /rest/api/2/version/<VerID>/relatedIssueCounts to check if version can be deleted. This call triggers com.atlassian.jira.project.version.VersionCustomFieldStore#getCustomFieldsUsing, which takes a long time to complete.

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 advice testing the impact of this change in a staging environment before applying in production

In newer Jira versions, there exists an optimization for the edge case when there are no version picker custom fields. For more info see JRASERVER-68497