Issue Summary

This is reproducible on Data Center: yes

Automation for Jira has a certain type of query repeatedly run on the AO_589059_RULE_STAT as part of the scheduled RuleInsightsUpdateJob which populates the AO_589059_RULE_STAT_ROLLUP_* tables

select sum(`AO_589059_RULE_STAT`.`QUEUED_ITEM_COUNT`)
from `AO_589059_RULE_STAT` `AO_589059_RULE_STAT`
where `AO_589059_RULE_STAT`.`CREATED` >= ?
limit ?

This query can become non-performant over time and result in increased database CPU usage and hence reducing the database performance.

Steps to Reproduce

1. Generate a very high volume of rule executions on a given day.
2. Verify that the rule executions are logged in the AO_589059_RULE_STAT table.
3. Let the periodic RuleInsightsUpdateScheduler to run which causes the above query to be run repeatedly.

Expected Results

The query does not cause increased database cpu usage and performance of other queries are not hampered.

Actual Results

The above query is identified as one of the top CPU consuming queries.

Workaround

A index on the table such as follows is shown to relieve the inefficient query execution and reduce CPU usage

 CREATE NONCLUSTERED INDEX [IDX_SUPP_QUEUED_ITEM] ON [jiraschema].[AO_589059_RULE_STAT] ([CREATED]) INCLUDE ([QUEUED_ITEM_COUNT])