Problem Definition

The index consistency check is used by the /status endpoint to ensure consistency between the local index and the database.

Since the /status is used within the load balancer configuration to determine if user requests should be routed to specific nodes, it's expected the /status to quickly return a response.
Most of the time this is true as it doesn't perform an index consistency check on every run. However, each 60 seconds (default) it will run the index consistency check, which requires checking the number of active issues from the database.

For any supported DBMS, Jira will run the following SQL query as part of this check:

SELECT COUNT(I.ID)
FROM jiraissue I
WHERE (I.ARCHIVED =  'N' ) OR (I.ARCHIVED IS NULL );

As this query may not be optimized for every DBMS, the /status may take a couple of seconds to complete, in contrast to a couple of milliseconds when index consistency check doesn't run.

One of the problems with it may arise on the load balancer configuration.
The response timeout for the health check probes must take into account the possible increase on response time when /status runs the index consistency check.

Suggested Solution

It would be interesting to have the SQL query related to index consistency check optimized to each DBMS, so the /status response is as fast as possible.

MySQL
The below equivalent query takes ~70% less time than the original SQL on a production environment with ~10MM active issues:

select (select count(j.id) from jiraissue j) - count(i.id)
from jiraissue i
WHERE i.ARCHIVED = 'Y';

PostgreSQL
The below equivalent query takes ~58% less time than the original SQL on a production environment with ~5MM active issues:

SELECT COUNT(*)
FROM jiraissue I
WHERE (I.ARCHIVED =  'N' ) OR (I.ARCHIVED IS NULL );

This is because count( * ) is usually a better option than using count(column).

Workaround

An option would be to completely disable the index consistency check using the following JVM property:

-Dcom.atlassian.jira.status.index.check=false

Be mindful that this disables the feature in JRASERVER-66970, meaning the load balancer may route requests to nodes on which the local index isn't healthy.

Another option is to extend the time between /status executions that will trigger the index consistency check, which is set to 60s by default.
As an example, if one would like to change it to each 5 minutes, then the following JVM property should be added:

-Dcom.atlassian.jira.status.index.check.expiration.seconds=300