-
Bug
-
Resolution: Duplicate
-
Medium
-
None
-
8.13.22, 8.20.11
-
8.13
-
24
-
Severity 3 - Minor
-
2
-
Problem Definition
The deletion of a custom field option may take a long time (and may even timeout) on instances with very large customfieldvalue tables.
When performing the deletion, a SELECT query is performed to identify the affected rows:
SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM public.customfieldvalue WHERE CUSTOMFIELD='<id>' AND STRINGVALUE='<id>' AND VALUETYPE IS NULL;
There is no covering index for this query predicate, resulting in an expensive sequential scan, e.g.:
jira=# explain analyze SELECT ID, ISSUE, CUSTOMFIELD, UPDATED, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM public.customfieldvalue WHERE CUSTOMFIELD='10300' AND STRINGVALUE='10111' AND VALUETYPE IS NULL;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on customfieldvalue (cost=0.00..66574.70 rows=1 width=1218) (actual time=1994.961..1994.961 rows=0 loops=1)
Filter: ((valuetype IS NULL) AND (customfield = '10300'::numeric) AND ((stringvalue)::text = '10111'::text))
Rows Removed by Filter: 2081778
Planning time: 9.137 ms
Execution time: 1994.989 ms
(5 rows)
Suggested Solution
Implement a new composite index on the customfieldvalue table on the customfield, stringvalue, and valuetype columns. As a quick example:
create index cfvalue_customfield_stringvalue_valuetype on customfieldvalue (customfield, stringvalue, valuetype);
Workaround
- If custom field option deletion is unacceptably slow, considering implementing the new index manually. The addition of new indexes can be expensive and should be discussed with your DBAs beforehand.
- In case of PSQL, setup an autovacuum.