Hi Team,
The Job Executor query which fetches available jobs is having this condition: RES.LOCK_OWNER_ is null . It is causing performance issues at scale where many jobs are getting produced. Is there any way where we can do similar kind of configuration same as setting due_date_ not null? We tried adding covering indexes but still it is taking around 200 ms when available records are more than 100k.
Indexes which we have tried:
CREATE INDEX concurrently act_ru_job_due_date_retries_suspension_state_conditional_idx ON public.act_ru_job USING btree (duedate_) WHERE ((retries_ > 0) AND (suspension_state_ = 1));
CREATE INDEX concurrently act_ru_job_due_date_retries_suspension_state_conditional_inverted_idx ON public.act_ru_job USING btree (lock_owner_,duedate_,lock_exp_time_) WHERE ((retries_ > 0) AND (suspension_state_ = 1));
Camunda Version: 7.14
PostgeSQL version: 14.10
select *
from ACT_RU_JOB RES
where (RES.RETRIES_ > 0)
and (RES.DUEDATE_ <= ‘2024-04-05 06:50:00.000’)
and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < ‘2024-04-05 06:50:00.000’)
and RES.SUSPENSION_STATE_ = 1
and (
(
RES.EXCLUSIVE_ = true
and not exists(
select J2.ID_ from ACT_RU_JOB J2
where J2.PROCESS_INSTANCE_ID_ = RES.PROCESS_INSTANCE_ID_ – from the same proc. inst.
and (J2.EXCLUSIVE_ = true) – also exclusive
and (J2.LOCK_OWNER_ is not null and J2.LOCK_EXP_TIME_ >= ‘2024-04-05 06:50:00.000’) – in progress
)
)
or
RES.EXCLUSIVE_ = false
)
LIMIT 1990 OFFSET 0