explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UZjq : 11

Settings
# exclusive inclusive rows x rows loops node
1. 0.291 76.221 ↓ 3.6 50 1

Sort (cost=3,049.73..3,049.76 rows=14 width=1,027) (actual time=76.214..76.221 rows=50 loops=1)

  • Output: sfuser.full_name, sfuser.email, sfuser.id, sfuser.surrogate_id, sfuser.is_super_user, sfuser.status, sfuser.username, sfuser.version, (string_agg(DISTINCT (user_license.license_type)::text, ','::text))
  • Sort Key: sfuser.full_name, sfuser.username
  • Sort Method: quicksort Memory: 32kB
2. 0.099 75.930 ↓ 3.6 50 1

HashAggregate (cost=3,049.32..3,049.46 rows=14 width=1,027) (actual time=75.904..75.930 rows=50 loops=1)

  • Output: sfuser.full_name, sfuser.email, sfuser.id, sfuser.surrogate_id, sfuser.is_super_user, sfuser.status, sfuser.username, sfuser.version, (string_agg(DISTINCT (user_license.license_type)::text, ','::text))
  • Group Key: sfuser.full_name, sfuser.email, sfuser.id, sfuser.surrogate_id, sfuser.is_super_user, sfuser.status, sfuser.username, sfuser.version, (string_agg(DISTINCT (user_license.license_type)::text, ','::text))
3. 0.012 75.831 ↓ 3.6 50 1

Append (cost=100.43..3,049.00 rows=14 width=1,027) (actual time=75.671..75.831 rows=50 loops=1)

4. 0.001 7.047 ↓ 0.0 0 1

GroupAggregate (cost=100.43..100.45 rows=1 width=100) (actual time=7.047..7.047 rows=0 loops=1)

  • Output: sfuser.full_name, sfuser.email, sfuser.id, sfuser.surrogate_id, sfuser.is_super_user, sfuser.status, sfuser.username, sfuser.version, string_agg(DISTINCT (user_license.license_type)::text, ','::text)
  • Group Key: sfuser.surrogate_id
5. 0.036 7.046 ↓ 0.0 0 1

Sort (cost=100.43..100.44 rows=1 width=72) (actual time=7.046..7.046 rows=0 loops=1)

  • Output: sfuser.surrogate_id, sfuser.full_name, sfuser.email, sfuser.id, sfuser.is_super_user, sfuser.status, sfuser.username, sfuser.version, user_license.license_type
  • Sort Key: sfuser.surrogate_id
  • Sort Method: quicksort Memory: 25kB
6. 0.001 7.010 ↓ 0.0 0 1

Nested Loop (cost=1.97..100.42 rows=1 width=72) (actual time=7.010..7.010 rows=0 loops=1)

  • Output: sfuser.surrogate_id, sfuser.full_name, sfuser.email, sfuser.id, sfuser.is_super_user, sfuser.status, sfuser.username, sfuser.version, user_license.license_type
7. 0.001 7.009 ↓ 0.0 0 1

Nested Loop (cost=1.68..100.08 rows=1 width=68) (actual time=7.009..7.009 rows=0 loops=1)

  • Output: sfuser.full_name, sfuser.email, sfuser.id, sfuser.surrogate_id, sfuser.is_super_user, sfuser.status, sfuser.username, sfuser.version
  • Inner Unique: true
8. 0.000 7.008 ↓ 0.0 0 1

Nested Loop (cost=1.40..99.77 rows=1 width=86) (actual time=7.008..7.008 rows=0 loops=1)

  • Output: sfuser.full_name, sfuser.email, sfuser.id, sfuser.surrogate_id, sfuser.is_super_user, sfuser.status, sfuser.username, sfuser.version, role_user.role_id, role_operation.role_id
  • Inner Unique: true
9. 0.004 7.008 ↓ 0.0 0 1

Nested Loop (cost=1.11..99.47 rows=1 width=27) (actual time=7.008..7.008 rows=0 loops=1)

  • Output: role_user.role_id, role_user.user_id, role_operation.role_id
10. 0.029 0.029 ↑ 1.0 1 1

Index Scan using project_path on public.project (cost=0.28..8.29 rows=1 width=9) (actual time=0.028..0.029 rows=1 loops=1)

  • Output: project.id, project.path, project.title, project.description, project.status, project.quota, project.version, project.date_created, project.date_last_modified, project.is_deleted, project.created_by_id, project.last_modified_by_id, project.parent_project_id, project.root_folder_id, project.home_page, project.is_locked, project.is_preservehtmlhead, project.project_access_level, project.template_id
  • Index Cond: ((project.path)::text = 'projects.psr-pf-pub-33'::text)
11. 0.172 6.975 ↓ 0.0 0 1

Nested Loop (cost=0.84..91.07 rows=10 width=36) (actual time=6.975..6.975 rows=0 loops=1)

  • Output: role_user.role_id, role_user.project_id, role_user.user_id, role_operation.role_id
12. 0.363 0.363 ↓ 23.0 230 1

Index Scan using role_oper_fields_idx on public.role_operation (cost=0.42..42.37 rows=10 width=9) (actual time=0.046..0.363 rows=230 loops=1)

  • Output: role_operation.id, role_operation.role_id, role_operation.operation_name, role_operation.operation_category, role_operation.object_type_id, role_operation.resource_name, role_operation.resource_value, role_operation.cluster_id
  • Index Cond: (((role_operation.operation_name)::text = 'edit'::text) AND ((role_operation.operation_category)::text = 'edit'::text) AND ((role_operation.object_type_id)::text = 'Tracker.Artifact'::text))
  • Filter: ((((role_operation.resource_name)::text = 'folder'::text) AND ((role_operation.resource_value)::text = 'tracker.psr_flex_tracker1'::text)) OR ((role_operation.resource_name)::text = '*'::text))
13. 6.440 6.440 ↓ 0.0 0 230

Index Only Scan using role_user_proj_idx on public.role_user (cost=0.42..4.86 rows=1 width=27) (actual time=0.028..0.028 rows=0 loops=230)

  • Output: role_user.role_id, role_user.user_id, role_user.project_id
  • Index Cond: ((role_user.role_id = (role_operation.role_id)::text) AND (role_user.project_id = (project.id)::text))
  • Heap Fetches: 0
14. 0.000 0.000 ↓ 0.0 0

Index Scan using sfuser_id on public.sfuser (cost=0.28..0.30 rows=1 width=68) (never executed)

  • Output: sfuser.id, sfuser.username, sfuser.email, sfuser.full_name, sfuser.is_super_user, sfuser.is_restricted_user, sfuser.status, sfuser.is_eula_accepted, sfuser.locale, sfuser.time_zone, sfuser.activation_code, sfuser.email_verification_code, sfuser.version, sfuser.date_created, sfuser.date_last_modified, sfuser.created_by_id, sfuser.last_modified_by_id, sfuser.last_password_change, sfuser.detail, sfuser.profile_file_id, sfuser.company_name, sfuser.alternate_email_1, sfuser.alternate_email_2, sfuser.alternate_email_3, sfuser.password, sfuser.surrogate_id, sfuser.is_special_user, sfuser.ldap_dn, sfuser.is_local_user, sfuser.is_default_user, sfuser.ldap_server_url, sfuser.ldap_username
  • Index Cond: ((sfuser.id)::text = (role_user.user_id)::text)
15. 0.000 0.000 ↓ 0.0 0

Index Only Scan using role_pk on public.role (cost=0.29..0.30 rows=1 width=9) (never executed)

  • Output: role.id
  • Index Cond: (role.id = (role_user.role_id)::text)
  • Heap Fetches: 0
16. 0.000 0.000 ↓ 0.0 0

Index Scan using user_license_usr_surr_id_idx on public.user_license (cost=0.28..0.33 rows=1 width=8) (never executed)

  • Output: user_license.surrogate_id, user_license.user_surrogate_id, user_license.license_type
  • Index Cond: (user_license.user_surrogate_id = sfuser.surrogate_id)
17. 0.001 0.166 ↓ 0.0 0 1

GroupAggregate (cost=528.24..528.27 rows=1 width=100) (actual time=0.166..0.166 rows=0 loops=1)

  • Output: sfuser_1.full_name, sfuser_1.email, sfuser_1.id, sfuser_1.surrogate_id, sfuser_1.is_super_user, sfuser_1.status, sfuser_1.username, sfuser_1.version, string_agg(DISTINCT (user_license_1.license_type)::text, ','::text)
  • Group Key: sfuser_1.surrogate_id
18. 0.006 0.165 ↓ 0.0 0 1

Sort (cost=528.24..528.25 rows=1 width=72) (actual time=0.165..0.165 rows=0 loops=1)

  • Output: sfuser_1.surrogate_id, sfuser_1.full_name, sfuser_1.email, sfuser_1.id, sfuser_1.is_super_user, sfuser_1.status, sfuser_1.username, sfuser_1.version, user_license_1.license_type
  • Sort Key: sfuser_1.surrogate_id
  • Sort Method: quicksort Memory: 25kB
19. 0.001 0.159 ↓ 0.0 0 1

Nested Loop (cost=457.38..528.23 rows=1 width=72) (actual time=0.159..0.159 rows=0 loops=1)

  • Output: sfuser_1.surrogate_id, sfuser_1.full_name, sfuser_1.email, sfuser_1.id, sfuser_1.is_super_user, sfuser_1.status, sfuser_1.username, sfuser_1.version, user_license_1.license_type
20. 0.000 0.158 ↓ 0.0 0 1

Nested Loop (cost=457.10..527.89 rows=1 width=68) (actual time=0.158..0.158 rows=0 loops=1)

  • Output: sfuser_1.full_name, sfuser_1.email, sfuser_1.id, sfuser_1.surrogate_id, sfuser_1.is_super_user, sfuser_1.status, sfuser_1.username, sfuser_1.version
  • Inner Unique: true
21. 0.004 0.158 ↓ 0.0 0 1

Merge Join (cost=456.82..459.01 rows=16 width=18) (actual time=0.158..0.158 rows=0 loops=1)

  • Output: groupmembership.member_id, relationship.origin_id
  • Inner Unique: true
  • Merge Cond: ((groupmembership.member_id)::text = (relationship.origin_id)::text)
22. 0.000 0.154 ↓ 0.0 0 1

Unique (cost=24.43..24.93 rows=100 width=9) (actual time=0.154..0.154 rows=0 loops=1)

  • Output: groupmembership.member_id
23. 0.012 0.154 ↓ 0.0 0 1

Sort (cost=24.43..24.68 rows=100 width=9) (actual time=0.154..0.154 rows=0 loops=1)

  • Output: groupmembership.member_id
  • Sort Key: groupmembership.member_id
  • Sort Method: quicksort Memory: 25kB
24. 0.001 0.142 ↓ 0.0 0 1

Nested Loop (cost=19.08..21.11 rows=100 width=9) (actual time=0.142..0.142 rows=0 loops=1)

  • Output: groupmembership.member_id
25. 0.001 0.141 ↓ 0.0 0 1

HashAggregate (cost=18.81..18.82 rows=1 width=10) (actual time=0.141..0.141 rows=0 loops=1)

  • Output: role_group.group_id
  • Group Key: (role_group.group_id)::text
26. 0.001 0.140 ↓ 0.0 0 1

Nested Loop (cost=9.01..18.80 rows=1 width=10) (actual time=0.140..0.140 rows=0 loops=1)

  • Output: role_group.group_id, role_group.group_id
27. 0.000 0.139 ↓ 0.0 0 1

Nested Loop (cost=8.59..16.96 rows=1 width=29) (actual time=0.139..0.139 rows=0 loops=1)

  • Output: role_group.role_id, role_group.group_id, role_1.id
  • Inner Unique: true
28. 0.065 0.139 ↓ 0.0 0 1

Hash Join (cost=8.31..12.30 rows=1 width=20) (actual time=0.139..0.139 rows=0 loops=1)

  • Output: role_group.role_id, role_group.group_id
  • Inner Unique: true
  • Hash Cond: ((role_group.project_id)::text = (project_1.id)::text)
29. 0.032 0.032 ↑ 1.0 158 1

Seq Scan on public.role_group (cost=0.00..3.58 rows=158 width=29) (actual time=0.009..0.032 rows=158 loops=1)

  • Output: role_group.id, role_group.role_id, role_group.group_id, role_group.project_id
30. 0.010 0.042 ↑ 1.0 1 1

Hash (cost=8.29..8.29 rows=1 width=9) (actual time=0.041..0.042 rows=1 loops=1)

  • Output: project_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.032 0.032 ↑ 1.0 1 1

Index Scan using project_path on public.project project_1 (cost=0.28..8.29 rows=1 width=9) (actual time=0.032..0.032 rows=1 loops=1)

  • Output: project_1.id
  • Index Cond: ((project_1.path)::text = 'projects.psr-pf-pub-33'::text)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using role_pk on public.role role_1 (cost=0.29..4.66 rows=1 width=9) (never executed)

  • Output: role_1.id, role_1.title, role_1.version, role_1.date_created, role_1.date_last_modified, role_1.created_by_id, role_1.last_modified_by_id, role_1.is_deleted, role_1.description, role_1.status, role_1.created_in_project, role_1.quick_role_user_id, role_1.block_recurse, role_1.type, role_1.requestable
  • Index Cond: ((role_1.id)::text = (role_group.role_id)::text)
  • Filter: (NOT role_1.is_deleted)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using role_oper_role_id on public.role_operation role_operation_1 (cost=0.42..1.83 rows=1 width=9) (never executed)

  • Output: role_operation_1.id, role_operation_1.role_id, role_operation_1.operation_name, role_operation_1.operation_category, role_operation_1.object_type_id, role_operation_1.resource_name, role_operation_1.resource_value, role_operation_1.cluster_id
  • Index Cond: ((role_operation_1.role_id)::text = (role_1.id)::text)
  • Filter: (((role_operation_1.operation_name)::text = 'edit'::text) AND ((role_operation_1.operation_category)::text = 'edit'::text) AND ((role_operation_1.object_type_id)::text = 'Tracker.Artifact'::text) AND ((((role_operation_1.resource_name)::text = 'folder'::text) AND ((role_operation_1.resource_value)::text = 'tracker.psr_flex_tracker1'::text)) OR ((role_operation_1.resource_name)::text = '*'::text)))
34. 0.000 0.000 ↓ 0.0 0

Index Only Scan using gpmb_groupmember on public.groupmembership (cost=0.28..2.05 rows=25 width=19) (never executed)

  • Output: groupmembership.group_id, groupmembership.member_id
  • Index Cond: (groupmembership.group_id = (role_group.group_id)::text)
  • Heap Fetches: 0
35. 0.000 0.000 ↓ 0.0 0

Unique (cost=432.38..432.46 rows=16 width=9) (never executed)

  • Output: relationship.origin_id
36. 0.000 0.000 ↓ 0.0 0

Sort (cost=432.38..432.42 rows=16 width=9) (never executed)

  • Output: relationship.origin_id
  • Sort Key: relationship.origin_id
37. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.07..432.06 rows=16 width=9) (never executed)

  • Output: relationship.origin_id
38. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.item (cost=4.64..114.16 rows=28 width=10) (never executed)

  • Output: item.id, item.name, item.title, item.version, item.date_created, item.date_last_modified, item.is_deleted, item.type_id, item.folder_id, item.created_by_id, item.last_modified_by_id, item.planning_folder_id, item.team_id
  • Recheck Cond: ((item.folder_id)::text = 'tracker5574'::text)
39. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on item_folder (cost=0.00..4.64 rows=28 width=0) (never executed)

  • Index Cond: ((item.folder_id)::text = 'tracker5574'::text)
40. 0.000 0.000 ↓ 0.0 0

Index Scan using relation_target on public.relationship (cost=0.42..11.33 rows=2 width=19) (never executed)

  • Output: relationship.id, relationship.origin_id, relationship.relationship_type_name, relationship.date_created, relationship.date_last_modified, relationship.created_by_id, relationship.last_modified_by_id, relationship.forward_description, relationship.back_description, relationship.version, relationship.is_deleted, relationship.origin_type_id, relationship.target_type_id, relationship.target_id, relationship.origin_title, relationship.target_title
  • Index Cond: ((relationship.target_id)::text = (item.id)::text)
41. 0.000 0.000 ↓ 0.0 0

Index Scan using sfuser_id on public.sfuser sfuser_1 (cost=0.28..4.30 rows=1 width=68) (never executed)

  • Output: sfuser_1.id, sfuser_1.username, sfuser_1.email, sfuser_1.full_name, sfuser_1.is_super_user, sfuser_1.is_restricted_user, sfuser_1.status, sfuser_1.is_eula_accepted, sfuser_1.locale, sfuser_1.time_zone, sfuser_1.activation_code, sfuser_1.email_verification_code, sfuser_1.version, sfuser_1.date_created, sfuser_1.date_last_modified, sfuser_1.created_by_id, sfuser_1.last_modified_by_id, sfuser_1.last_password_change, sfuser_1.detail, sfuser_1.profile_file_id, sfuser_1.company_name, sfuser_1.alternate_email_1, sfuser_1.alternate_email_2, sfuser_1.alternate_email_3, sfuser_1.password, sfuser_1.surrogate_id, sfuser_1.is_special_user, sfuser_1.ldap_dn, sfuser_1.is_local_user, sfuser_1.is_default_user, sfuser_1.ldap_server_url, sfuser_1.ldap_username
  • Index Cond: ((sfuser_1.id)::text = (groupmembership.member_id)::text)
  • Filter: (((sfuser_1.username)::text <> 'nobody'::text) AND ((sfuser_1.username)::text <> 'guest'::text))
42. 0.000 0.000 ↓ 0.0 0

Index Scan using user_license_usr_surr_id_idx on public.user_license user_license_1 (cost=0.28..0.33 rows=1 width=8) (never executed)

  • Output: user_license_1.surrogate_id, user_license_1.user_surrogate_id, user_license_1.license_type
  • Index Cond: (user_license_1.user_surrogate_id = sfuser_1.surrogate_id)
43. 0.001 1.386 ↓ 0.0 0 1

GroupAggregate (cost=1,851.54..1,851.76 rows=11 width=100) (actual time=1.386..1.386 rows=0 loops=1)

  • Output: sfuser_2.full_name, sfuser_2.email, sfuser_2.id, sfuser_2.surrogate_id, sfuser_2.is_super_user, sfuser_2.status, sfuser_2.username, sfuser_2.version, string_agg(DISTINCT (user_license_2.license_type)::text, ','::text)
  • Group Key: sfuser_2.surrogate_id
44. 0.010 1.385 ↓ 0.0 0 1

Sort (cost=1,851.54..1,851.57 rows=11 width=72) (actual time=1.385..1.385 rows=0 loops=1)

  • Output: sfuser_2.surrogate_id, sfuser_2.full_name, sfuser_2.email, sfuser_2.id, sfuser_2.is_super_user, sfuser_2.status, sfuser_2.username, sfuser_2.version, user_license_2.license_type
  • Sort Key: sfuser_2.surrogate_id
  • Sort Method: quicksort Memory: 25kB
45. 0.000 1.375 ↓ 0.0 0 1

Nested Loop (cost=737.38..1,851.35 rows=11 width=72) (actual time=1.375..1.375 rows=0 loops=1)

  • Output: sfuser_2.surrogate_id, sfuser_2.full_name, sfuser_2.email, sfuser_2.id, sfuser_2.is_super_user, sfuser_2.status, sfuser_2.username, sfuser_2.version, user_license_2.license_type
46. 0.001 1.375 ↓ 0.0 0 1

Nested Loop (cost=737.10..1,847.55 rows=11 width=68) (actual time=1.375..1.375 rows=0 loops=1)

  • Output: sfuser_2.full_name, sfuser_2.email, sfuser_2.id, sfuser_2.surrogate_id, sfuser_2.is_super_user, sfuser_2.status, sfuser_2.username, sfuser_2.version
  • Inner Unique: true
47. 0.018 1.374 ↓ 0.0 0 1

Hash Join (cost=736.82..1,844.15 rows=11 width=9) (actual time=1.374..1.374 rows=0 loops=1)

  • Output: role_user_1.user_id
  • Inner Unique: true
  • Hash Cond: ((role_user_1.project_id)::text = (project2.id)::text)
  • Join Filter: (((role_user_1.project_id)::text = (ancestor_project.ancestor_project_id)::text) OR (((role_user_1.project_id)::text <> (ancestor_project.ancestor_project_id)::text) AND ((project2.path)::text = '.'::text)))
48. 0.001 0.038 ↓ 0.0 0 1

Nested Loop (cost=647.54..1,725.96 rows=11,013 width=27) (actual time=0.038..0.038 rows=0 loops=1)

  • Output: ancestor_project.ancestor_project_id, role_user_1.project_id, role_user_1.user_id
49. 0.000 0.037 ↓ 0.0 0 1

Nested Loop (cost=0.56..12.60 rows=1 width=9) (actual time=0.037..0.037 rows=0 loops=1)

  • Output: ancestor_project.ancestor_project_id
50. 0.037 0.037 ↓ 0.0 0 1

Index Scan using project_path on public.project project_2 (cost=0.28..8.30 rows=1 width=9) (actual time=0.036..0.037 rows=0 loops=1)

  • Output: project_2.id, project_2.path, project_2.title, project_2.description, project_2.status, project_2.quota, project_2.version, project_2.date_created, project_2.date_last_modified, project_2.is_deleted, project_2.created_by_id, project_2.last_modified_by_id, project_2.parent_project_id, project_2.root_folder_id, project_2.home_page, project_2.is_locked, project_2.is_preservehtmlhead, project_2.project_access_level, project_2.template_id
  • Index Cond: ((project_2.path)::text = 'projects.psr-pf-pub-33'::text)
  • Filter: (project_2.project_access_level = ANY ('{1,2,3}'::integer[]))
  • Rows Removed by Filter: 1
51. 0.000 0.000 ↓ 0.0 0

Index Only Scan using project_ancestor_project_idx on public.ancestor_project (cost=0.28..4.29 rows=1 width=18) (never executed)

  • Output: ancestor_project.project_id, ancestor_project.ancestor_project_id, ancestor_project.parent_type
  • Index Cond: (ancestor_project.project_id = (project_2.id)::text)
  • Heap Fetches: 0
52. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=646.99..1,609.53 rows=10,383 width=18) (never executed)

  • Output: role_user_1.project_id, role_user_1.user_id
53. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=646.57..1,240.02 rows=192 width=18) (never executed)

  • Output: role_operation_2.role_id, role_2.id
  • Inner Unique: true
  • Hash Cond: ((role_operation_2.role_id)::text = (role_2.id)::text)
54. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.role_operation role_operation_2 (cost=122.51..715.42 rows=207 width=9) (never executed)

  • Output: role_operation_2.id, role_operation_2.role_id, role_operation_2.operation_name, role_operation_2.operation_category, role_operation_2.object_type_id, role_operation_2.resource_name, role_operation_2.resource_value, role_operation_2.cluster_id
  • Recheck Cond: (((role_operation_2.operation_name)::text = 'edit'::text) AND ((role_operation_2.object_type_id)::text = 'Tracker.Artifact'::text))
  • Filter: (((role_operation_2.resource_name)::text = '*'::text) AND ((role_operation_2.resource_value)::text = '*'::text))
55. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on role_oper_fields_idx (cost=0.00..122.46 rows=207 width=0) (never executed)

  • Index Cond: (((role_operation_2.operation_name)::text = 'edit'::text) AND ((role_operation_2.object_type_id)::text = 'Tracker.Artifact'::text))
56. 0.000 0.000 ↓ 0.0 0

Hash (cost=374.37..374.37 rows=11,975 width=9) (never executed)

  • Output: role_2.id
57. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.role role_2 (cost=0.00..374.37 rows=11,975 width=9) (never executed)

  • Output: role_2.id
  • Filter: ((NOT role_2.is_deleted) AND role_2.block_recurse)
58. 0.000 0.000 ↓ 0.0 0

Index Only Scan using role_user_proj_idx on public.role_user role_user_1 (cost=0.42..1.49 rows=43 width=27) (never executed)

  • Output: role_user_1.role_id, role_user_1.user_id, role_user_1.project_id
  • Index Cond: (role_user_1.role_id = (role_2.id)::text)
  • Heap Fetches: 0
59. 0.769 1.318 ↑ 1.0 1,879 1

Hash (cost=65.79..65.79 rows=1,879 width=30) (actual time=1.318..1.318 rows=1,879 loops=1)

  • Output: project2.id, project2.path
  • Buckets: 2048 Batches: 1 Memory Usage: 132kB
60. 0.549 0.549 ↑ 1.0 1,879 1

Seq Scan on public.project project2 (cost=0.00..65.79 rows=1,879 width=30) (actual time=0.005..0.549 rows=1,879 loops=1)

  • Output: project2.id, project2.path
61. 0.000 0.000 ↓ 0.0 0

Index Scan using sfuser_id on public.sfuser sfuser_2 (cost=0.28..0.31 rows=1 width=68) (never executed)

  • Output: sfuser_2.id, sfuser_2.username, sfuser_2.email, sfuser_2.full_name, sfuser_2.is_super_user, sfuser_2.is_restricted_user, sfuser_2.status, sfuser_2.is_eula_accepted, sfuser_2.locale, sfuser_2.time_zone, sfuser_2.activation_code, sfuser_2.email_verification_code, sfuser_2.version, sfuser_2.date_created, sfuser_2.date_last_modified, sfuser_2.created_by_id, sfuser_2.last_modified_by_id, sfuser_2.last_password_change, sfuser_2.detail, sfuser_2.profile_file_id, sfuser_2.company_name, sfuser_2.alternate_email_1, sfuser_2.alternate_email_2, sfuser_2.alternate_email_3, sfuser_2.password, sfuser_2.surrogate_id, sfuser_2.is_special_user, sfuser_2.ldap_dn, sfuser_2.is_local_user, sfuser_2.is_default_user, sfuser_2.ldap_server_url, sfuser_2.ldap_username
  • Index Cond: ((sfuser_2.id)::text = (role_user_1.user_id)::text)
  • Filter: (((sfuser_2.username)::text <> 'nobody'::text) AND ((sfuser_2.username)::text <> ' guest'::text))
62. 0.000 0.000 ↓ 0.0 0

Index Scan using user_license_usr_surr_id_idx on public.user_license user_license_2 (cost=0.28..0.33 rows=1 width=8) (never executed)

  • Output: user_license_2.surrogate_id, user_license_2.user_surrogate_id, user_license_2.license_type
  • Index Cond: (user_license_2.user_surrogate_id = sfuser_2.surrogate_id)
63. 0.195 67.220 ↓ 50.0 50 1

GroupAggregate (cost=568.29..568.32 rows=1 width=100) (actual time=67.068..67.220 rows=50 loops=1)

  • Output: sfuser_3.full_name, sfuser_3.email, sfuser_3.id, sfuser_3.surrogate_id, sfuser_3.is_super_user, sfuser_3.status, sfuser_3.username, sfuser_3.version, string_agg(DISTINCT (user_license_3.license_type)::text, ','::text)
  • Group Key: sfuser_3.surrogate_id
64. 0.136 67.025 ↓ 60.0 60 1

Sort (cost=568.29..568.30 rows=1 width=72) (actual time=67.017..67.025 rows=60 loops=1)

  • Output: sfuser_3.surrogate_id, sfuser_3.full_name, sfuser_3.email, sfuser_3.id, sfuser_3.is_super_user, sfuser_3.status, sfuser_3.username, sfuser_3.version, user_license_3.license_type
  • Sort Key: sfuser_3.surrogate_id
  • Sort Method: quicksort Memory: 33kB
65. 0.044 66.889 ↓ 60.0 60 1

Nested Loop (cost=2.23..568.28 rows=1 width=72) (actual time=11.046..66.889 rows=60 loops=1)

  • Output: sfuser_3.surrogate_id, sfuser_3.full_name, sfuser_3.email, sfuser_3.id, sfuser_3.is_super_user, sfuser_3.status, sfuser_3.username, sfuser_3.version, user_license_3.license_type
66. 0.116 66.605 ↓ 60.0 60 1

Nested Loop (cost=1.95..567.94 rows=1 width=68) (actual time=11.032..66.605 rows=60 loops=1)

  • Output: sfuser_3.full_name, sfuser_3.email, sfuser_3.id, sfuser_3.surrogate_id, sfuser_3.is_super_user, sfuser_3.status, sfuser_3.username, sfuser_3.version
  • Join Filter: ((role_3.id)::text = (role_operation_3.role_id)::text)
67. 0.094 45.781 ↓ 62.0 62 1

Nested Loop (cost=1.53..566.88 rows=1 width=86) (actual time=10.891..45.781 rows=62 loops=1)

  • Output: role_user_2.role_id, sfuser_3.full_name, sfuser_3.email, sfuser_3.id, sfuser_3.surrogate_id, sfuser_3.is_super_user, sfuser_3.status, sfuser_3.username, sfuser_3.version, role_3.id
  • Inner Unique: true
68. 1.311 44.881 ↓ 62.0 62 1

Nested Loop (cost=1.25..566.57 rows=1 width=27) (actual time=10.863..44.881 rows=62 loops=1)

  • Output: role_user_2.role_id, role_user_2.user_id, role_3.id
  • Join Filter: (((role_user_2.project_id)::text = (ancestor_project_1.ancestor_project_id)::text) OR (((role_user_2.project_id)::text <> (ancestor_project_1.ancestor_project_id)::text) AND ((project_3.path)::text = '.'::text)))
  • Rows Removed by Join Filter: 3432
69. 0.005 0.432 ↓ 2.0 2 1

Nested Loop (cost=0.56..12.60 rows=1 width=9) (actual time=0.426..0.432 rows=2 loops=1)

  • Output: ancestor_project_1.ancestor_project_id
70. 0.027 0.027 ↑ 1.0 1 1

Index Scan using project_path on public.project project2_1 (cost=0.28..8.29 rows=1 width=9) (actual time=0.026..0.027 rows=1 loops=1)

  • Output: project2_1.id, project2_1.path, project2_1.title, project2_1.description, project2_1.status, project2_1.quota, project2_1.version, project2_1.date_created, project2_1.date_last_modified, project2_1.is_deleted, project2_1.created_by_id, project2_1.last_modified_by_id, project2_1.parent_project_id, project2_1.root_folder_id, project2_1.home_page, project2_1.is_locked, project2_1.is_preservehtmlhead, project2_1.project_access_level, project2_1.template_id
  • Index Cond: ((project2_1.path)::text = 'projects.psr-pf-pub-33'::text)
71. 0.400 0.400 ↓ 2.0 2 1

Index Only Scan using project_ancestor_project_idx on public.ancestor_project ancestor_project_1 (cost=0.28..4.29 rows=1 width=18) (actual time=0.397..0.400 rows=2 loops=1)

  • Output: ancestor_project_1.project_id, ancestor_project_1.ancestor_project_id, ancestor_project_1.parent_type
  • Index Cond: (ancestor_project_1.project_id = (project2_1.id)::text)
  • Heap Fetches: 0
72. 4.246 43.138 ↓ 7.5 1,747 2

Nested Loop (cost=0.70..549.89 rows=233 width=57) (actual time=0.234..21.569 rows=1,747 loops=2)

  • Output: role_user_2.role_id, role_user_2.project_id, role_user_2.user_id, project_3.path, role_3.id
  • Inner Unique: true
73. 0.972 10.940 ↓ 7.5 1,747 2

Nested Loop (cost=0.42..480.82 rows=233 width=36) (actual time=0.204..5.470 rows=1,747 loops=2)

  • Output: role_user_2.role_id, role_user_2.project_id, role_user_2.user_id, role_3.id
74. 7.700 7.700 ↑ 1.1 18 2

Seq Scan on public.role role_3 (cost=0.00..374.37 rows=19 width=9) (actual time=0.026..3.850 rows=18 loops=2)

  • Output: role_3.id, role_3.title, role_3.version, role_3.date_created, role_3.date_last_modified, role_3.created_by_id, role_3.last_modified_by_id, role_3.is_deleted, role_3.description, role_3.status, role_3.created_in_project, role_3.quick_role_user_id, role_3.block_recurse, role_3.type, role_3.requestable
  • Filter: ((NOT role_3.is_deleted) AND (NOT role_3.block_recurse))
  • Rows Removed by Filter: 12919
75. 2.268 2.268 ↓ 2.3 97 36

Index Only Scan using role_user_proj_idx on public.role_user role_user_2 (cost=0.42..5.17 rows=43 width=27) (actual time=0.027..0.063 rows=97 loops=36)

  • Output: role_user_2.role_id, role_user_2.user_id, role_user_2.project_id
  • Index Cond: (role_user_2.role_id = (role_3.id)::text)
  • Heap Fetches: 0
76. 27.952 27.952 ↑ 1.0 1 3,494

Index Scan using project_pk on public.project project_3 (cost=0.28..0.30 rows=1 width=30) (actual time=0.008..0.008 rows=1 loops=3,494)

  • Output: project_3.id, project_3.path, project_3.title, project_3.description, project_3.status, project_3.quota, project_3.version, project_3.date_created, project_3.date_last_modified, project_3.is_deleted, project_3.created_by_id, project_3.last_modified_by_id, project_3.parent_project_id, project_3.root_folder_id, project_3.home_page, project_3.is_locked, project_3.is_preservehtmlhead, project_3.project_access_level, project_3.template_id
  • Index Cond: ((project_3.id)::text = (role_user_2.project_id)::text)
77. 0.806 0.806 ↑ 1.0 1 62

Index Scan using sfuser_id on public.sfuser sfuser_3 (cost=0.28..0.31 rows=1 width=68) (actual time=0.013..0.013 rows=1 loops=62)

  • Output: sfuser_3.id, sfuser_3.username, sfuser_3.email, sfuser_3.full_name, sfuser_3.is_super_user, sfuser_3.is_restricted_user, sfuser_3.status, sfuser_3.is_eula_accepted, sfuser_3.locale, sfuser_3.time_zone, sfuser_3.activation_code, sfuser_3.email_verification_code, sfuser_3.version, sfuser_3.date_created, sfuser_3.date_last_modified, sfuser_3.created_by_id, sfuser_3.last_modified_by_id, sfuser_3.last_password_change, sfuser_3.detail, sfuser_3.profile_file_id, sfuser_3.company_name, sfuser_3.alternate_email_1, sfuser_3.alternate_email_2, sfuser_3.alternate_email_3, sfuser_3.password, sfuser_3.surrogate_id, sfuser_3.is_special_user, sfuser_3.ldap_dn, sfuser_3.is_local_user, sfuser_3.is_default_user, sfuser_3.ldap_server_url, sfuser_3.ldap_username
  • Index Cond: ((sfuser_3.id)::text = (role_user_2.user_id)::text)
  • Filter: (((sfuser_3.username)::text <> 'nobody'::text) AND ((sfuser_3.username)::text <> 'guest'::text))
78. 20.708 20.708 ↑ 1.0 1 62

Index Scan using role_oper_role_id on public.role_operation role_operation_3 (cost=0.42..1.05 rows=1 width=9) (actual time=0.083..0.334 rows=1 loops=62)

  • Output: role_operation_3.id, role_operation_3.role_id, role_operation_3.operation_name, role_operation_3.operation_category, role_operation_3.object_type_id, role_operation_3.resource_name, role_operation_3.resource_value, role_operation_3.cluster_id
  • Index Cond: ((role_operation_3.role_id)::text = (role_user_2.role_id)::text)
  • Filter: (((role_operation_3.resource_name)::text = '*'::text) AND ((role_operation_3.resource_value)::text = '*'::text) AND ((role_operation_3.operation_name)::text = 'edit'::text) AND ((role_operation_3.object_type_id)::text = 'Tracker.Artifact'::text))
  • Rows Removed by Filter: 476
79. 0.240 0.240 ↑ 1.0 1 60

Index Scan using user_license_usr_surr_id_idx on public.user_license user_license_3 (cost=0.28..0.33 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=60)

  • Output: user_license_3.surrogate_id, user_license_3.user_surrogate_id, user_license_3.license_type
  • Index Cond: (user_license_3.user_surrogate_id = sfuser_3.surrogate_id)