explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TH6R

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 37,961.512 ↑ 1.0 1 1

Limit (cost=8,690.34..8,690.38 rows=1 width=1,656) (actual time=37,961.511..37,961.512 rows=1 loops=1)

2. 0.002 37,961.510 ↑ 1.0 1 1

Unique (cost=8,690.34..8,690.38 rows=1 width=1,656) (actual time=37,961.509..37,961.510 rows=1 loops=1)

3. 0.034 37,961.508 ↑ 1.0 1 1

Sort (cost=8,690.34..8,690.35 rows=1 width=1,656) (actual time=37,961.508..37,961.508 rows=1 loops=1)

  • Sort Key: (change_tz(am.planned_completion_date, 'UTC'::character varying, tz.time_zone)), am.id, am.client_entity_seq_id, am.name, am.status_id, ct.id, ct.name, rn.id, rn.name, tz.id, (array_to_string(array_agg(DISTINCT lef.function_id), ', '::text)), (array_to_string(array_agg(DISTINCT les.service_id), ', '::text))
  • Sort Method: quicksort Memory: 25kB
4. 0.389 37,961.474 ↑ 1.0 1 1

Group (cost=8,690.06..8,690.33 rows=1 width=1,656) (actual time=37,961.473..37,961.474 rows=1 loops=1)

  • Group Key: am.id, tz.id, ct.id, rn.id, (array_to_string(array_agg(DISTINCT lef.function_id), ', '::text)), (array_to_string(array_agg(DISTINCT les.service_id), ', '::text))
5. 0.016 37,961.085 ↑ 1.0 1 1

Sort (cost=8,690.06..8,690.07 rows=1 width=1,922) (actual time=37,961.085..37,961.085 rows=1 loops=1)

  • Sort Key: am.id, tz.id, ct.id, rn.id, (array_to_string(array_agg(DISTINCT lef.function_id), ', '::text)), (array_to_string(array_agg(DISTINCT les.service_id), ', '::text))
  • Sort Method: quicksort Memory: 25kB
6. 116.909 37,961.069 ↑ 1.0 1 1

Nested Loop Left Join (cost=4,758.10..8,690.05 rows=1 width=1,922) (actual time=29,417.100..37,961.069 rows=1 loops=1)

  • Filter: (((du.user_id = 2649) OR ('{2649}'::integer[] <@ ug.user_ids)) AND (((du.entity_id = am.id) AND (du.role_group_id = 2059) AND (am.status_id = ANY ('{7435,7342,2236,7437,7436,7225,7284,7260,2,4,2238,2237}'::integer[])) AND ((du.user_id = 2649) OR ('{2649}'::integer[] <@ ug.user_ids)) AND (du.entity_id = am.id) AND (wftc.task_id = ANY (am.next_manual_task_ids))) OR ((du.entity_id = am.id) AND (du.role_group_id = 2059) AND (am.status_id = ANY ('{7435,7342,2236,7437,7436,7225,7284,7260,2,4,2238,2237}'::integer[])) AND ((du.user_id = 2649) OR ('{2649}'::integer[] <@ ug.user_ids)) AND (du.entity_id = am.id) AND (wftc.task_id = ANY (am.next_manual_task_ids))) OR ((du.entity_id = am.entity_id) AND (hashed SubPlan 2) AND (du.role_group_id = 2299) AND (am.status_id = ANY ('{}'::integer[]))) OR ((du.entity_id = am.entity_id) AND (hashed SubPlan 3) AND (du.role_group_id = 2299) AND (am.status_id = ANY ('{}'::integer[])))))
  • Rows Removed by Filter: 190078
7. 243.112 37,844.160 ↓ 4,420.4 190,079 1

Nested Loop Left Join (cost=4,741.37..8,661.91 rows=43 width=1,982) (actual time=27.151..37,844.160 rows=190,079 loops=1)

  • Join Filter: (du.role_group_id = ANY (wftc.task_owners))
  • Rows Removed by Join Filter: 161377
8. 77.438 37,220.890 ↓ 4,420.4 190,079 1

Nested Loop Left Join (cost=4,740.95..7,026.71 rows=43 width=1,982) (actual time=27.118..37,220.890 rows=190,079 loops=1)

9. 152.177 36,807.772 ↓ 4,196.0 4,196 1

Nested Loop Left Join (cost=4,721.23..4,929.37 rows=1 width=1,982) (actual time=27.080..36,807.772 rows=4,196 loops=1)

  • Join Filter: (wf.id = am.work_flow_id)
  • Rows Removed by Join Filter: 914693
10. 276.776 36,160.467 ↓ 4,196.0 4,196 1

Nested Loop (cost=4,720.95..4,921.06 rows=1 width=1,982) (actual time=26.833..36,160.467 rows=4,196 loops=1)

11. 169.175 34,913.259 ↓ 485,216.0 485,216 1

Nested Loop (cost=4,716.40..4,869.88 rows=1 width=1,970) (actual time=26.413..34,913.259 rows=485,216 loops=1)

12. 27.419 34,020.372 ↓ 4,112.0 4,112 1

Nested Loop Left Join (cost=4,636.91..4,765.25 rows=1 width=1,966) (actual time=21.587..34,020.372 rows=4,112 loops=1)

  • Join Filter: (tz.id = am.time_zone_id)
  • Rows Removed by Join Filter: 27388
13. 1,423.725 33,964.169 ↓ 4,112.0 4,112 1

Nested Loop Left Join (cost=4,636.91..4,763.33 rows=1 width=1,692) (actual time=21.563..33,964.169 rows=4,112 loops=1)

  • Join Filter: (les.entity_id = am.id)
  • Rows Removed by Join Filter: 9806759
14. 1,432.872 14,862.956 ↓ 4,112.0 4,112 1

Nested Loop Left Join (cost=1,337.74..1,442.16 rows=1 width=1,660) (actual time=9.160..14,862.956 rows=4,112 loops=1)

  • Join Filter: (lef.entity_id = am.id)
  • Rows Removed by Join Filter: 9806759
15. 11.786 61.972 ↓ 4,112.0 4,112 1

Nested Loop Left Join (cost=21.62..113.23 rows=1 width=1,628) (actual time=0.358..61.972 rows=4,112 loops=1)

16. 14.506 29.626 ↓ 4,112.0 4,112 1

Nested Loop Left Join (cost=21.34..104.92 rows=1 width=1,112) (actual time=0.334..29.626 rows=4,112 loops=1)

17. 10.744 11.008 ↓ 4,112.0 4,112 1

Bitmap Heap Scan on action_item_mgmt am (cost=21.06..96.62 rows=1 width=596) (actual time=0.328..11.008 rows=4,112 loops=1)

  • Recheck Cond: (client_id = 1005)
  • Filter: ((NOT deleted) AND ((status_id = ANY ('{7435,7342,2236,7437,7436,7225,7284,7260,2,4,2238,2237}'::integer[])) OR (status_id = ANY ('{7435,7342,2236,7437,7436,7225,7284,7260,2,4,2238,2237}'::integer[])) OR ((hashed SubPlan 2) AND (status_id = ANY ('{}'::integer[]))) OR ((hashed SubPlan 3) AND (status_id = ANY ('{}'::integer[])))))
  • Rows Removed by Filter: 355
  • Heap Blocks: exact=263
18. 0.232 0.232 ↓ 178.7 4,467 1

Bitmap Index Scan on action_item_mgmt_client_id_idx (cost=0.00..4.47 rows=25 width=0) (actual time=0.232..0.232 rows=4,467 loops=1)

  • Index Cond: (client_id = 1005)
19.          

SubPlan (for Bitmap Heap Scan)

20. 0.027 0.027 ↑ 1.0 1 1

Index Scan using pk_role_group on role_group (cost=0.28..8.29 rows=1 width=4) (actual time=0.026..0.027 rows=1 loops=1)

  • Index Cond: (id = 2299)
21. 0.005 0.005 ↑ 1.0 1 1

Index Scan using pk_role_group on role_group role_group_1 (cost=0.28..8.29 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (id = 2299)
22. 4.112 4.112 ↓ 0.0 0 4,112

Index Scan using pk_contract on contract ct (cost=0.28..8.30 rows=1 width=520) (actual time=0.001..0.001 rows=0 loops=4,112)

  • Index Cond: (id = am.contract_id)
23. 20.560 20.560 ↑ 1.0 1 4,112

Index Scan using pk_relation on relation rn (cost=0.28..8.29 rows=1 width=520) (actual time=0.005..0.005 rows=1 loops=4,112)

  • Index Cond: (id = am.relation_id)
24. 11,793.216 13,368.112 ↓ 12.2 2,386 4,112

GroupAggregate (cost=1,316.12..1,324.54 rows=195 width=36) (actual time=0.010..3.251 rows=2,386 loops=4,112)

  • Group Key: lef.entity_id
25. 1,568.109 1,574.896 ↓ 3.5 2,580 4,112

Sort (cost=1,316.12..1,317.95 rows=733 width=8) (actual time=0.003..0.383 rows=2,580 loops=4,112)

  • Sort Key: lef.entity_id
  • Sort Method: quicksort Memory: 445kB
26. 4.027 6.787 ↓ 7.4 5,395 1

Bitmap Heap Scan on link_entity_function lef (cost=18.10..1,281.23 rows=733 width=8) (actual time=2.831..6.787 rows=5,395 loops=1)

  • Recheck Cond: (entity_type_id = 18)
  • Heap Blocks: exact=444
27. 2.760 2.760 ↓ 7.4 5,412 1

Bitmap Index Scan on idx_link_entity_function_4 (cost=0.00..17.92 rows=733 width=0) (actual time=2.759..2.760 rows=5,412 loops=1)

  • Index Cond: (entity_type_id = 18)
28. 15,082.816 17,677.488 ↓ 11.9 2,386 4,112

GroupAggregate (cost=3,299.17..3,316.68 rows=200 width=36) (actual time=0.011..4.299 rows=2,386 loops=4,112)

  • Group Key: les.entity_id
29. 2,585.601 2,594.672 ↓ 2.2 4,255 4,112

Sort (cost=3,299.17..3,304.01 rows=1,934 width=8) (actual time=0.003..0.631 rows=4,255 loops=4,112)

  • Sort Key: les.entity_id
  • Sort Method: quicksort Memory: 794kB
30. 5.576 9.071 ↓ 4.5 8,739 1

Bitmap Heap Scan on link_entity_service les (cost=39.41..3,193.60 rows=1,934 width=8) (actual time=3.565..9.071 rows=8,739 loops=1)

  • Recheck Cond: (entity_type_id = 18)
  • Heap Blocks: exact=614
31. 3.495 3.495 ↓ 4.5 8,758 1

Bitmap Index Scan on idx_link_entity_service_5 (cost=0.00..38.93 rows=1,934 width=0) (actual time=3.494..3.495 rows=8,758 loops=1)

  • Index Cond: (entity_type_id = 18)
32. 28.784 28.784 ↑ 5.1 8 4,112

Seq Scan on time_zone tz (cost=0.00..1.41 rows=41 width=278) (actual time=0.006..0.007 rows=8 loops=4,112)

33. 718.925 723.712 ↓ 118.0 118 4,112

Seq Scan on role_group rg (cost=79.50..104.61 rows=1 width=4) (actual time=0.017..0.176 rows=118 loops=4,112)

  • Filter: ((client_id = 1005) AND ((hashed SubPlan 1) OR (entity_type_id = 18)))
  • Rows Removed by Filter: 460
34.          

SubPlan (for Seq Scan)

35. 0.579 4.787 ↓ 1.1 14 1

Unique (cost=79.40..79.46 rows=13 width=4) (actual time=3.702..4.787 rows=14 loops=1)

36. 1.381 4.208 ↓ 323.2 4,201 1

Sort (cost=79.40..79.43 rows=13 width=4) (actual time=3.700..4.208 rows=4,201 loops=1)

  • Sort Key: am2.entity_type_id
  • Sort Method: quicksort Memory: 389kB
37. 2.633 2.827 ↓ 323.2 4,201 1

Bitmap Heap Scan on action_item_mgmt am2 (cost=4.47..79.16 rows=13 width=4) (actual time=0.225..2.827 rows=4,201 loops=1)

  • Recheck Cond: (client_id = 1005)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 266
  • Heap Blocks: exact=263
38. 0.194 0.194 ↓ 178.7 4,467 1

Bitmap Index Scan on action_item_mgmt_client_id_idx (cost=0.00..4.47 rows=25 width=0) (actual time=0.194..0.194 rows=4,467 loops=1)

  • Index Cond: (client_id = 1005)
39. 485.216 970.432 ↓ 0.0 0 485,216

Bitmap Heap Scan on domain_user du (cost=4.54..51.17 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=485,216)

  • Recheck Cond: ((entity_id = am.id) AND (role_group_id = rg.id))
  • Filter: ((role_group_id = 2059) OR (role_group_id = 2059) OR (role_group_id = 2299) OR (role_group_id = 2299))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=35958
40. 485.216 485.216 ↓ 0.0 0 485,216

Bitmap Index Scan on idx_domain_user_entity_id_role_group_id (cost=0.00..4.54 rows=12 width=0) (actual time=0.001..0.001 rows=0 loops=485,216)

  • Index Cond: ((entity_id = am.id) AND (role_group_id = rg.id))
41. 495.128 495.128 ↓ 219.0 219 4,196

Index Scan using idx_work_flow_entyid_clid_relid on work_flow wf (cost=0.28..8.30 rows=1 width=4) (actual time=0.011..0.118 rows=219 loops=4,196)

  • Index Cond: ((entity_type_id = 18) AND (client_id = 1005))
42. 109.096 335.680 ↑ 20.9 45 4,196

Bitmap Heap Scan on work_flow_task wft (cost=19.72..2,087.92 rows=942 width=8) (actual time=0.058..0.080 rows=45 loops=4,196)

  • Recheck Cond: (work_flow_id = wf.id)
  • Heap Blocks: exact=49713
43. 226.584 226.584 ↑ 20.9 45 4,196

Bitmap Index Scan on work_flow_task_wfid_idx (cost=0.00..19.48 rows=942 width=0) (actual time=0.054..0.054 rows=45 loops=4,196)

  • Index Cond: (work_flow_id = wf.id)
44. 380.158 380.158 ↑ 935.0 1 190,079

Index Scan using idx_work_flow_task_configuration_2 on work_flow_task_configuration wftc (cost=0.42..16.99 rows=935 width=36) (actual time=0.002..0.002 rows=1 loops=190,079)

  • Index Cond: (wft.id = task_id)
45. 0.000 0.000 ↓ 0.0 0 190,079

Index Scan using pk_user_group on user_group ug (cost=0.14..0.16 rows=1 width=36) (actual time=0.000..0.000 rows=0 loops=190,079)

  • Index Cond: (id = du.user_group_id)
Planning time : 12.000 ms
Execution time : 37,962.803 ms