Issue Summary

Inefficient SQL query causing long time to fetch deleted worklogs via REST interface. This is present when an instance has bigger amount of deleted worklogs.

Steps to Reproduce

1. Make sure there are many deleted work logs in Jira (hundreds of thousands)
2. Execute the following REST request to fetch deleted worklogs:

   curl -v -u <username>:<password> <base_URL>/rest/api/2/worklog/deleted

Expected Results

The request returns results within reasonable amount of time.

Actual Results

The request returns results after some longer period of time (xx minutes up to couple of hours, depending on the size of the instance).

At the same time, the database is processing the query for a long time and it locks one CPU core while doing so.

Workaround

Currently there is no known workaround for this behavior. A workaround will be added here when available

Notes

• Reproduced using PostgreSQL database
• SQL query that gets executed:

  select cast(CHANGE_ITEM.oldvalue as int8), max(CHANGE_GROUP.created)
  from public.changeitem CHANGE_ITEM
  left join public.changegroup CHANGE_GROUP
  on CHANGE_ITEM.groupid = CHANGE_GROUP.id
  where CHANGE_ITEM.field = 'WorklogId' and (CHANGE_GROUP.created >= '2019-01-01 00:00:00.000000' and CHANGE_GROUP.created <= '2021-01-01 00:00:00.000000') and cast(CHANGE_ITEM.oldvalue as int8) not in (select WORKLOG.id
  from public.worklog WORKLOG)
  group by cast(CHANGE_ITEM.oldvalue as int8)
  order by max(CHANGE_GROUP.created) asc
  limit 1000;"
  

• Present with default indexes created (PostgreSQL example):

   tablename  |        indexname        |                                         indexdef
  -------------+-------------------------+------------------------------------------------------------------------------------------
   changegroup | chggroup_author_created | CREATE INDEX chggroup_author_created ON public.changegroup USING btree (author, created)
   changegroup | chggroup_issue_id       | CREATE INDEX chggroup_issue_id ON public.changegroup USING btree (issueid, id)
   changegroup | pk_changegroup          | CREATE UNIQUE INDEX pk_changegroup ON public.changegroup USING btree (id)
   changeitem  | chgitem_group_field     | CREATE INDEX chgitem_group_field ON public.changeitem USING btree (groupid, field)
   changeitem  | pk_changeitem           | CREATE UNIQUE INDEX pk_changeitem ON public.changeitem USING btree (id)