explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SCef

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 59,025.264 ↑ 1.0 1 1

Limit (cost=10,335.60..10,335.64 rows=1 width=1,656) (actual time=59,025.263..59,025.264 rows=1 loops=1)

2. 0.002 59,025.262 ↑ 1.0 1 1

Unique (cost=10,335.60..10,335.64 rows=1 width=1,656) (actual time=59,025.261..59,025.262 rows=1 loops=1)

3. 0.010 59,025.260 ↑ 1.0 1 1

Sort (cost=10,335.60..10,335.61 rows=1 width=1,656) (actual time=59,025.260..59,025.260 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.093 59,025.250 ↑ 1.0 1 1

Group (cost=10,335.32..10,335.59 rows=1 width=1,656) (actual time=59,025.249..59,025.250 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.009 59,025.157 ↑ 1.0 1 1

Sort (cost=10,335.32..10,335.33 rows=1 width=1,922) (actual time=59,025.157..59,025.157 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. 114.616 59,025.148 ↑ 1.0 1 1

Nested Loop Left Join (cost=4,692.34..10,335.31 rows=1 width=1,922) (actual time=48,563.418..59,025.148 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 1) AND (du.role_group_id = 2299) AND (am.status_id = ANY ('{}'::integer[]))) OR ((du.entity_id = am.entity_id) AND (hashed SubPlan 2) AND (du.role_group_id = 2299) AND (am.status_id = ANY ('{}'::integer[])))))
  • Rows Removed by Filter: 190078
7. 188.416 58,910.532 ↓ 2,184.8 190,079 1

Nested Loop Left Join (cost=4,675.61..10,295.34 rows=87 width=1,982) (actual time=37.355..58,910.532 rows=190,079 loops=1)

  • Join Filter: (du.role_group_id = ANY (wftc.task_owners))
  • Rows Removed by Join Filter: 161377
8. 12,926.882 58,341.958 ↓ 2,184.8 190,079 1

Nested Loop Left Join (cost=4,675.19..6,986.92 rows=87 width=1,982) (actual time=37.337..58,341.958 rows=190,079 loops=1)

  • Join Filter: (wf.id = am.work_flow_id)
  • Rows Removed by Join Filter: 86889509
9. 11.860 34,270.500 ↓ 2,098.0 4,196 1

Nested Loop (cost=4,655.19..4,879.89 rows=2 width=1,982) (actual time=13.052..34,270.500 rows=4,196 loops=1)

10. 18.419 34,233.464 ↓ 2,098.0 4,196 1

Nested Loop (cost=4,654.91..4,879.29 rows=2 width=1,982) (actual time=13.035..34,233.464 rows=4,196 loops=1)

11. 27.345 34,099.909 ↓ 4,112.0 4,112 1

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

  • Join Filter: (tz.id = am.time_zone_id)
  • Rows Removed by Join Filter: 27388
12. 1,399.377 34,043.780 ↓ 4,112.0 4,112 1

Nested Loop Left Join (cost=4,636.91..4,763.33 rows=1 width=1,692) (actual time=12.974..34,043.780 rows=4,112 loops=1)

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

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

  • Join Filter: (lef.entity_id = am.id)
  • Rows Removed by Join Filter: 9806759
14. 9.146 67.599 ↓ 4,112.0 4,112 1

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

15. 14.405 29.669 ↓ 4,112.0 4,112 1

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

16. 10.944 11.152 ↓ 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.257..11.152 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 1) AND (status_id = ANY ('{}'::integer[]))) OR ((hashed SubPlan 2) AND (status_id = ANY ('{}'::integer[])))))
  • Rows Removed by Filter: 355
  • Heap Blocks: exact=263
17. 0.193 0.193 ↓ 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.193..0.193 rows=4,467 loops=1)

  • Index Cond: (client_id = 1005)
18.          

SubPlan (for Bitmap Heap Scan)

19. 0.011 0.011 ↑ 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.010..0.011 rows=1 loops=1)

  • Index Cond: (id = 2299)
20. 0.004 0.004 ↑ 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.003..0.004 rows=1 loops=1)

  • Index Cond: (id = 2299)
21. 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)
22. 28.784 28.784 ↑ 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.007..0.007 rows=1 loops=4,112)

  • Index Cond: (id = am.relation_id)
23. 11,920.688 13,479.136 ↓ 12.2 2,386 4,112

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

  • Group Key: lef.entity_id
24. 1,555.996 1,558.448 ↓ 3.5 2,580 4,112

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

  • Sort Key: lef.entity_id
  • Sort Method: quicksort Memory: 445kB
25. 2.196 2.452 ↓ 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=0.296..2.452 rows=5,395 loops=1)

  • Recheck Cond: (entity_type_id = 18)
  • Heap Blocks: exact=444
26. 0.256 0.256 ↓ 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=0.256..0.256 rows=5,412 loops=1)

  • Index Cond: (entity_type_id = 18)
27. 15,177.392 17,706.272 ↓ 11.9 2,386 4,112

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

  • Group Key: les.entity_id
28. 2,524.946 2,528.880 ↓ 2.2 4,255 4,112

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

  • Sort Key: les.entity_id
  • Sort Method: quicksort Memory: 794kB
29. 3.351 3.934 ↓ 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=0.658..3.934 rows=8,739 loops=1)

  • Recheck Cond: (entity_type_id = 18)
  • Heap Blocks: exact=614
30. 0.583 0.583 ↓ 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=0.583..0.583 rows=8,758 loops=1)

  • Index Cond: (entity_type_id = 18)
31. 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)

32. 24.672 115.136 ↑ 49.0 1 4,112

Bitmap Heap Scan on domain_user du (cost=18.00..113.54 rows=49 width=16) (actual time=0.027..0.028 rows=1 loops=4,112)

  • Recheck Cond: (((entity_id = am.id) AND (entity_id = am.id) AND (role_group_id = 2059)) OR ((entity_id = am.id) AND (entity_id = am.id) AND (role_group_id = 2059)) OR ((entity_id = am.entity_id) AND (role_group_id = 2299)) OR ((entity_id = am.entity_id) AND (role_group_id = 2299)))
  • Filter: (am.id = entity_id)
  • Heap Blocks: exact=3751
33. 16.448 90.464 ↓ 0.0 0 4,112

BitmapOr (cost=18.00..18.00 rows=25 width=0) (actual time=0.022..0.022 rows=0 loops=4,112)

34. 45.232 45.232 ↑ 1.0 1 4,112

Bitmap Index Scan on idx_domain_user_entity_id_role_group_id (cost=0.00..4.44 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=4,112)

  • Index Cond: ((entity_id = am.id) AND (entity_id = am.id) AND (role_group_id = 2059))
35. 8.224 8.224 ↑ 1.0 1 4,112

Bitmap Index Scan on idx_domain_user_entity_id_role_group_id (cost=0.00..4.44 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=4,112)

  • Index Cond: ((entity_id = am.id) AND (entity_id = am.id) AND (role_group_id = 2059))
36. 16.448 16.448 ↓ 0.0 0 4,112

Bitmap Index Scan on idx_domain_user_entity_id_role_group_id (cost=0.00..4.54 rows=12 width=0) (actual time=0.004..0.004 rows=0 loops=4,112)

  • Index Cond: ((entity_id = am.entity_id) AND (role_group_id = 2299))
37. 4.112 4.112 ↓ 0.0 0 4,112

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=4,112)

  • Index Cond: ((entity_id = am.entity_id) AND (role_group_id = 2299))
38. 25.176 25.176 ↑ 1.0 1 4,196

Index Only Scan using pk_role_group on role_group rg (cost=0.28..0.30 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=4,196)

  • Index Cond: (id = du.role_group_id)
  • Heap Fetches: 4196
39. 11,128.084 11,144.576 ↓ 482.6 20,753 4,196

Materialize (cost=20.00..2,105.86 rows=43 width=8) (actual time=0.001..2.656 rows=20,753 loops=4,196)

40. 5.853 16.492 ↓ 482.6 20,753 1

Nested Loop Left Join (cost=20.00..2,105.64 rows=43 width=8) (actual time=0.050..16.492 rows=20,753 loops=1)

41. 0.265 0.265 ↓ 273.0 273 1

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.023..0.265 rows=273 loops=1)

  • Index Cond: ((entity_type_id = 18) AND (client_id = 1005))
42. 8.736 10.374 ↑ 12.4 76 273

Bitmap Heap Scan on work_flow_task wft (cost=19.72..2,087.92 rows=942 width=8) (actual time=0.009..0.038 rows=76 loops=273)

  • Recheck Cond: (work_flow_id = wf.id)
  • Heap Blocks: exact=3330
43. 1.638 1.638 ↑ 12.4 76 273

Bitmap Index Scan on work_flow_task_wfid_idx (cost=0.00..19.48 rows=942 width=0) (actual time=0.006..0.006 rows=76 loops=273)

  • 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 : 5.072 ms
Execution time : 59,025.959 ms