Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-12938

Slow performance of historic task instance query

    XMLWordPrintable

Details

    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

            Activity

              People

                tassilo.weidner Tassilo Weidner
                yana.vasileva Yana Vasileva
                Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  Salesforce