JQL using {not in} clause triggers a lucene search that makes the operation to take a long time

Summary

In a large Jira Software (Agile) instance (more than 4000000 issues) using the the not in clause to avoid querying one or a few projects among thousands will trigger a Lucene query that will search for an Agile custom field in openSprints() making the query to take a long time to complete.

If the JQL is used as a filter in a board, it will cause the board to time out.

Enabling DEBUG logging for Lucene queries will show a JQL similar to:

{FIRST PART OF THE JQL} AND {project not in ("PROJECT NAME")} AND {status in (A VERY LONG LIST HERE)} AND ( {CUSTOM FIELD in openSprints()} OR NOT {SECOND PART OF THE JQL} order by Rank ASC

Environment

• Jira Software 7.2.13

• Jira instance with a large number of issues and comments (more than 4000000 issues)

Expected Results

The JQL returns the results in an acceptable time not making boards to time out.

Actual Results

The JQL takes a long time and causes boards to time out.

Workaround

Avoid using the not in clause.