explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SPGX

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 23,111.504 ↑ 1.0 50 1

Limit (cost=2,621,318.82..2,621,320.07 rows=50 width=105) (actual time=23,111.437..23,111.504 rows=50 loops=1)

2. 0.045 23,111.490 ↑ 1.7 50 1

Unique (cost=2,621,318.82..2,621,320.92 rows=84 width=105) (actual time=23,111.435..23,111.490 rows=50 loops=1)

3. 16.637 23,111.445 ↑ 1.7 50 1

Sort (cost=2,621,318.82..2,621,319.03 rows=84 width=105) (actual time=23,111.432..23,111.445 rows=50 loops=1)

  • Sort Key: tasks.start_time_, (('activiti$'::text || (tasks.id_)::text)), (('workspace://SpacesStore/'::text || (docnode.uuid)::text)), initiatorlink.user_id_, tasks.priority_, tasks.description_, (bool_or(COALESCE(((lmr_viewshistory.text_)::t (...)
  • Sort Method: quicksort Memory: 5926kB
4. 19.277 23,094.808 ↓ 231.2 19,418 1

WindowAgg (cost=2,621,313.40..2,621,316.13 rows=84 width=105) (actual time=23,087.633..23,094.808 rows=19,418 loops=1)

5. 99.603 23,075.531 ↓ 231.2 19,418 1

HashAggregate (cost=2,621,313.40..2,621,314.66 rows=84 width=105) (actual time=23,062.638..23,075.531 rows=19,418 loops=1)

6. 55.417 22,975.928 ↓ 231.6 19,451 1

Nested Loop Left Join (cost=1,574,655.99..2,621,311.51 rows=84 width=105) (actual time=14,633.552..22,975.928 rows=19,451 loops=1)

7. 29.832 21,114.637 ↓ 231.2 19,418 1

Nested Loop (cost=1,574,655.42..1,753,633.25 rows=84 width=92) (actual time=14,633.413..21,114.637 rows=19,418 loops=1)

8. 37.296 20,715.863 ↓ 65.2 19,418 1

Nested Loop (cost=1,574,654.98..1,751,815.65 rows=298 width=63) (actual time=14,633.385..20,715.863 rows=19,418 loops=1)

9. 64.545 19,863.011 ↓ 69.6 19,418 1

Nested Loop (cost=1,574,654.42..1,746,777.78 rows=279 width=63) (actual time=14,633.294..19,863.011 rows=19,418 loops=1)

10. 66.115 18,497.460 ↓ 14.3 19,418 1

Hash Join (cost=1,574,653.85..1,738,063.51 rows=1,361 width=75) (actual time=14,633.133..18,497.460 rows=19,418 loops=1)

  • Hash Cond: ((tasks.proc_inst_id_)::text = (bpmpackage.proc_inst_id_)::text)
11. 1,907.237 4,840.979 ↓ 7.2 19,418 1

Hash Join (cost=307,426.67..450,600.46 rows=2,699 width=64) (actual time=1,041.995..4,840.979 rows=19,418 loops=1)

  • Hash Cond: ((initiatorlink.task_id_)::text = (tasks.id_)::text)
12. 1,893.277 1,893.277 ↓ 1.0 2,342,310 1

Seq Scan on act_ru_identitylink initiatorlink (cost=0.00..114,988.31 rows=2,252,679 width=19) (actual time=0.008..1,893.277 rows=2,342,310 loops=1)

  • Filter: ((type_)::text = 'starter'::text)
  • Rows Removed by Filter: 2935004
13. 10.620 1,040.465 ↓ 1.2 19,418 1

Hash (cost=307,224.70..307,224.70 rows=16,157 width=54) (actual time=1,040.465..1,040.465 rows=19,418 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1857kB
14. 124.750 1,029.845 ↓ 1.2 19,418 1

Bitmap Heap Scan on act_hi_taskinst tasks (cost=155,520.36..307,224.70 rows=16,157 width=54) (actual time=939.943..1,029.845 rows=19,418 loops=1)

  • Recheck Cond: ((end_time_ IS NULL) AND (upper((form_key_)::text) = 'LMRIDWF:ACTIVITIREVIEWTASK'::text))
  • Filter: ((upper((assignee_)::text) = 'DOVBETA.DMYTRO'::text) OR ((hashed SubPlan 1) AND (assignee_ IS NULL)) OR ((hashed SubPlan 2) AND (assignee_ IS NULL)))
  • Rows Removed by Filter: 14499
15. 12.672 12.672 ↑ 3.5 34,090 1

Bitmap Index Scan on idx_act_hi_taskinst_end_time_up_form_key_up_assignee (cost=0.00..5,168.70 rows=117,614 width=0) (actual time=12.672..12.672 rows=34,090 loops=1)

  • Index Cond: ((end_time_ IS NULL) AND (upper((form_key_)::text) = 'LMRIDWF:ACTIVITIREVIEWTASK'::text))
16.          

SubPlan (forBitmap Heap Scan)

17. 0.911 1.154 ↑ 16.4 14 1

Bitmap Heap Scan on act_ru_identitylink (cost=41.15..5,818.87 rows=230 width=9) (actual time=0.293..1.154 rows=14 loops=1)

  • Recheck Cond: ((user_id_)::text = 'dovbeta.dmytro'::text)
  • Filter: ((type_)::text = 'candidate'::text)
  • Rows Removed by Filter: 384
18. 0.243 0.243 ↑ 3.5 476 1

Bitmap Index Scan on act_idx_ident_lnk_user (cost=0.00..41.09 rows=1,688 width=0) (actual time=0.243..0.243 rows=476 loops=1)

  • Index Cond: ((user_id_)::text = 'dovbeta.dmytro'::text)
19. 129.896 891.269 ↓ 4,060.1 48,721 1

Hash Join (cost=24,369.97..144,528.15 rows=12 width=9) (actual time=8.411..891.269 rows=48,721 loops=1)

  • Hash Cond: ((link.group_id_)::text = (groups.string_value)::text)
20. 752.999 752.999 ↓ 1.1 725,235 1

Seq Scan on act_ru_identitylink link (cost=0.00..114,988.31 rows=689,300 width=25) (actual time=0.007..752.999 rows=725,235 loops=1)

  • Filter: ((type_)::text = 'candidate'::text)
  • Rows Removed by Filter: 4552079
21. 0.011 8.374 ↓ 2.8 11 1

Hash (cost=24,369.92..24,369.92 rows=4 width=21) (actual time=8.374..8.374 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
22. 0.025 8.363 ↓ 2.8 11 1

Nested Loop (cost=1.14..24,369.92 rows=4 width=21) (actual time=3.637..8.363 rows=11 loops=1)

23. 7.876 7.876 ↓ 11.0 11 1

Index Scan using fk_alf_cass_tqn on alf_child_assoc groupsassocs (cost=0.44..24,355.78 rows=1 width=8) (actual time=3.544..7.876 rows=11 loops=1)

  • Index Cond: (type_qname_id = 95)
  • Filter: ((qname_localname)::text = 'dovbeta.dmytro'::text)
  • Rows Removed by Filter: 8160
24. 0.462 0.462 ↑ 4.0 1 11

Index Scan using alf_node_properties_pkey on alf_node_properties groups (cost=0.70..14.09 rows=4 width=29) (actual time=0.042..0.042 rows=1 loops=11)

  • Index Cond: ((node_id = groupsassocs.parent_node_id) AND (qname_id = 92))
25. 927.809 13,590.366 ↓ 1.0 1,403,081 1

Hash (cost=1,250,225.10..1,250,225.10 rows=1,360,167 width=29) (actual time=13,590.366..13,590.366 rows=1,403,081 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 140856kB
26. 12,662.557 12,662.557 ↓ 1.0 1,403,081 1

Seq Scan on act_ru_variable bpmpackage (cost=0.00..1,250,225.10 rows=1,360,167 width=29) (actual time=0.012..12,662.557 rows=1,403,081 loops=1)

  • Filter: ((name_)::text = 'bpm_package'::text)
  • Rows Removed by Filter: 44373215
27. 1,301.006 1,301.006 ↑ 1.0 1 19,418

Index Scan using store_id on alf_node bpmpackagenode (cost=0.56..6.39 rows=1 width=45) (actual time=0.066..0.067 rows=1 loops=19,418)

  • Index Cond: ((store_id = 6) AND ((uuid)::text = "substring"((bpmpackage.text_)::text, 25)))
28. 815.556 815.556 ↑ 387.0 1 19,418

Index Only Scan using idx_alf_cass_pri on alf_child_assoc docnodeassoc (cost=0.56..14.19 rows=387 width=16) (actual time=0.041..0.042 rows=1 loops=19,418)

  • Index Cond: (parent_node_id = bpmpackagenode.id)
  • Heap Fetches: 369
29. 368.942 368.942 ↑ 1.0 1 19,418

Index Scan using alf_node_pkey on alf_node docnode (cost=0.44..6.09 rows=1 width=45) (actual time=0.018..0.019 rows=1 loops=19,418)

  • Index Cond: (id = docnodeassoc.child_node_id)
  • Filter: (store_id = 6)
30. 1,805.874 1,805.874 ↓ 0.0 0 19,418

Index Scan using act_idx_hi_detail_task_id on act_hi_detail lmr_viewshistory (cost=0.57..10,328.19 rows=131 width=22) (actual time=0.093..0.093 rows=0 loops=19,418)

  • Index Cond: ((task_id_)::text = (tasks.id_)::text)
  • Filter: ((name_)::text = 'lmr_viewsHistory'::text)
  • Rows Removed by Filter: 9