Deletion of custom field options can be slow on large instances due to lack of covering index

XMLWordPrintable

    • 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.

            Assignee:
            Unassigned
            Reporter:
            TJ Royall
            Votes:
            16 Vote for this issue
            Watchers:
            27 Start watching this issue

              Created:
              Updated:
              Resolved: