L3 - Default The Mybatis query cockpit.history.selectCalledHistoricProcessInstances (part of cockpit ee plugins) does not perform well on Postgres.
Example scenario:
- camunda 7.6.0-ee on Tomcat
- Postgres 9.4
- cardinality ACT_HI_PROCINST: 43.570
- cardinality ACT_HI_ACTINST: 455.794
- cardinality ACT_RE_PROCDEF: 121
- cardinality ACT_RU_AUTHORIZATION: 3.214
- Query has execution time of several minutes
Attachments:
- query.txt - the query
- actual-query-plan.txt - the plan of the query in the above scenario
- local-query-plan.txt - the plan of the query for a fresh Camunda distro
- historic-instances-query.txt - the query that displays the list of historic process instances
- historic-instances-query-plan.txt - the plan for this query in the above scenario
Assessment:
- According to actual-query-plan.txt, the costs of checking the authorizations for every process definition simply adds up
- The plan for the regular historic instances query is much cheaper; perhaps we can change the called instances query to resemble the regular query so that it produces a similar plan