We are fetching historic tasks with filters(process variables) and it took >2+ secs for response.
We used this rest api to get list of historic tasks and we are doing pagination also(per page 50 tasks).
Is this expected behaviour?
We are having 1 lakh records(active+completed tasks) in history table.
How can we improvise the query?
We enabled slow queries in DB and captured this query.
SELECT DISTINCT
RES.*
FROM
ACT_HI_TASKINST RES
INNER JOIN
ACT_HI_PROCINST HPI ON RES.PROC_INST_ID_ = HPI.ID_
INNER JOIN
ACT_RE_PROCDEF D ON RES.PROC_DEF_ID_ = D.ID_
WHERE
D.KEY_ = 'workflow'
AND RES.ASSIGNEE_ IS NULL
AND RES.END_TIME_ IS NULL
AND HPI.END_TIME_ IS NULL
AND RES.TENANT_ID_ IN ('tenant1')
AND EXISTS( SELECT
ID_
FROM
ACT_HI_VARINST VAR
WHERE
NAME_ = 'customerId'
AND PROC_INST_ID_ = RES.PROC_INST_ID_
AND TASK_ID_ IS NULL
AND ((VAR_TYPE_ IS NOT NULL
AND VAR_TYPE_ = 'string'
AND TEXT_ IS NOT NULL
AND TEXT_ LIKE '%testuser%')))
ORDER BY RES.START_TIME_ DESC
LIMIT 50 OFFSET 0
