Jobs query could be nonoptimal


Our environment: Camunda BPM with version 7.12.0 with Camunda Spring Boot Starter of version 3.4.1 and Postgres as DB.

Problem: we can see some degradation of our app performance when there is a lot of incidents when we are trying to get jobs to execute them. As we can see from here this is happening because in the SQL query exists this part:

and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < 'some date')

So, problem is that it is not convenient for us to have this null value because we actually can’t create a suitable index for it (a lot of entities RES.LOCK_OWNER_ with null value).

Our tryings/ideas:

  1. Could we improve the Job structure a little? For example, create a new field that will be used as a boolean flag if there is LOCK_OWNER_ exists.
  2. We could do something similar to the DUEDATE_ field. I mean create a configuration parameter and if it is set - just not checking for null or something.