Details
-
Bug Report
-
Resolution: Fixed
-
L3 - Default
-
None
-
None
Description
Scenario
- Postgresql database
- 685 111 historic task instances
- Perform historic task query:
historyService.createHistoricTaskInstanceQuery().taskUnassigned().withoutCandidateGroups().unfinished().count()
Observed:
Executing the query takes too long and often finishes with timeout
Execution plan:
Aggregate (cost=71618007.93..71618007.94 rows=1 width=8) (actual time=412438.951..412438.951 rows=1 loops=1) -> Unique (cost=71617921.29..71617946.04 rows=4951 width=37) (actual time=412438.480..412438.837 rows=517 loops=1) -> Sort (cost=71617921.29..71617933.67 rows=4951 width=37) (actual time=412438.479..412438.585 rows=517 loops=1) Sort Key: res.id_ Sort Method: quicksort Memory: 65kB -> Nested Loop Left Join (cost=1000.29..71617617.46 rows=4951 width=37) (actual time=8724.729..412436.174 rows=517 loops=1) Join Filter: (((a.resource_id_)::text = (res.proc_def_key_)::text) OR ((a.resource_id_)::text = '*'::text)) Filter: (((res.execution_id_ IS NOT NULL) AND (a.resource_id_ IS NOT NULL)) OR (res.execution_id_ IS NULL)) -> Gather (cost=1000.29..71617125.39 rows=4951 width=85) (actual time=8724.708..412428.707 rows=517 loops=1) Workers Planned: 1 Workers Launched: 1 -> Parallel Index Scan using act_idx_hi_task_inst_end on act_hi_taskinst res (cost=0.29..71615630.29 rows=2912 width=85) (actual time=5326.334..406883.498 rows=258 loops=2) Index Cond: (end_time_ IS NULL) Filter: ((assignee_ IS NULL) AND (NOT (SubPlan 1))) Rows Removed by Filter: 5500 SubPlan 1 -> Materialize (cost=0.00..21180.98 rows=164236 width=37) (actual time=0.008..50.873 rows=52452 loops=10492) -> Seq Scan on act_hi_identitylink hil (cost=0.00..19075.80 rows=164236 width=37) (actual time=0.011..64.155 rows=57519 loops=2981) Filter: ((group_id_ IS NOT NULL) AND ((type_)::text = 'candidate'::text)) Rows Removed by Filter: 3416 -> Materialize (cost=0.00..405.43 rows=1 width=36) (actual time=0.001..0.003 rows=1 loops=517) -> Seq Scan on act_ru_authorization a (cost=0.00..405.43 rows=1 width=36) (actual time=0.012..1.235 rows=1 loops=1) Filter: ((type_ < 2) AND (resource_type_ = 6) AND (((user_id_)::text = ANY ('{demo,*}'::text[])) OR ((group_id_)::text = 'camunda-admin'::text)) AND ((perms_ & 4096) = 4096)) Rows Removed by Filter: 7860 Planning time: 0.692 ms Execution time: 412440.513 ms
Expected:
The query to performs better and no timeouts.
Solution Idea:
- rewrite NOT IN part to NOT EXISTS, similar to solution of
CAM-11179
Hints:
There's a known issue on Postgresql with NOT IN [1]
Similar issue: CAM-11179
The performance with NOT EXISTS instead -
Aggregate (cost=13050.46..13050.47 rows=1 width=8) (actual time=832.411..832.411 rows=1 loops=1) -> HashAggregate (cost=12916.14..12975.84 rows=5970 width=37) (actual time=832.101..832.293 rows=517 loops=1) Group Key: res.id_ -> Nested Loop Left Join (cost=1000.71..12901.21 rows=5970 width=37) (actual time=50.539..831.489 rows=517 loops=1) Join Filter: (((a.resource_id_)::text = (res.proc_def_key_)::text) OR ((a.resource_id_)::text = '*'::text)) Filter: (((res.execution_id_ IS NOT NULL) AND (a.resource_id_ IS NOT NULL)) OR (res.execution_id_ IS NULL)) -> Gather (cost=1000.71..12391.31 rows=5970 width=85) (actual time=50.509..829.315 rows=517 loops=1) Workers Planned: 1 Workers Launched: 1 -> Nested Loop Anti Join (cost=0.71..10794.31 rows=3512 width=85) (actual time=28.345..823.089 rows=258 loops=2) -> Parallel Index Scan using act_idx_hi_task_inst_end on act_hi_taskinst res (cost=0.29..1456.38 rows=5825 width=85) (actual time=0.011..5.919 rows=5244 loops=2) Index Cond: (end_time_ IS NULL) Filter: (assignee_ IS NULL) Rows Removed by Filter: 512 -> Index Scan using act_idx_hi_ident_link_task on act_hi_identitylink hil (cost=0.42..9.43 rows=10 width=37) (actual time=0.155..0.155 rows=1 loops=10487) Index Cond: ((task_id_)::text = (res.id_)::text) Filter: ((group_id_ IS NOT NULL) AND ((type_)::text = 'candidate'::text)) Rows Removed by Filter: 0 -> Materialize (cost=0.00..405.43 rows=1 width=36) (actual time=0.000..0.003 rows=1 loops=517) -> Seq Scan on act_ru_authorization a (cost=0.00..405.43 rows=1 width=36) (actual time=0.016..1.272 rows=1 loops=1) Filter: ((type_ < 2) AND (resource_type_ = 6) AND (((user_id_)::text = ANY ('{demo,*}'::text[])) OR ((group_id_)::text = 'camunda-admin'::text)) AND ((perms_ & 4096) = 4096)) Rows Removed by Filter: 7859 Planning time: 11.131 ms Execution time: 832.685 ms
[1] https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_NOT_IN
mgm-controller-panel
This is the controller panel for Smart Panels app
Attachments
Issue Links
- is related to
-
CAM-11179 Task query 'withoutCandidateGroups' and 'withoutCandidateUsers' slow on Postgres
-
- Closed
-