L3 - Default Given:
Have a lot of running activities stored.
When:
The HistoricActivityInstance#selectRunningHistoricActivityPage query is executed.
Then:
The query execution is slow.
RunningActivityInstanceFetcher - Fetched [10000] running activity instances which started after set timestamp with page size [10000] within [22975] ms
Expected:
Improved performance (similar to The HistoricActivityInstance#selectCompletedHistoricActivityPage query).
CompletedActivityInstanceFetcher - Fetched [10000] running activity instances which started after set timestamp with page size [10000] within [405] ms
Hints (optional):
There is a composite index missing on ACT_HI_ACTINST for START_TIME_ and END_TIME_ .
As the query filters by END_TIME_ and sorts by START_TIME_.
Manual creation of that index on a postgres DB reduced the response time from >20s to <1s.
create index ACT_IDX_HI_ACT_INST_START_END on ACT_HI_ACTINST(START_TIME_, END_TIME_);