explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zriw

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.155 ↓ 4.0 4 1

Subquery Scan on members (cost=340.99..341.23 rows=1 width=165) (actual time=0.152..0.155 rows=4 loops=1)

  • Filter: ((members.invite_token IS NULL) AND (members.requested_at IS NULL))
  • Rows Removed by Filter: 2
2. 0.003 0.154 ↑ 2.3 6 1

Unique (cost=340.99..341.09 rows=14 width=169) (actual time=0.151..0.154 rows=6 loops=1)

3. 0.008 0.151 ↑ 1.6 9 1

Sort (cost=340.99..341.02 rows=14 width=169) (actual time=0.151..0.151 rows=9 loops=1)

  • Sort Key: members_1.user_id, members_1.invite_email, (CASE WHEN ((members_1.type)::text = 'ProjectMember'::text) THEN 1 WHEN ((members_1.type)::text = 'GroupMember'::text) THEN 2 ELSE 3 END)
  • Sort Method: quicksort Memory: 26kB
4. 0.002 0.143 ↑ 1.6 9 1

Nested Loop Left Join (cost=43.33..340.72 rows=14 width=169) (actual time=0.066..0.143 rows=9 loops=1)

5. 0.009 0.132 ↑ 1.6 9 1

Nested Loop Left Join (cost=43.19..337.71 rows=14 width=169) (actual time=0.063..0.132 rows=9 loops=1)

6. 0.001 0.123 ↑ 1.6 9 1

Append (cost=43.05..330.36 rows=14 width=165) (actual time=0.061..0.123 rows=9 loops=1)

7. 0.004 0.068 ↓ 2.0 4 1

Hash Semi Join (cost=43.05..46.26 rows=2 width=165) (actual time=0.061..0.068 rows=4 loops=1)

  • Hash Cond: (members_1.id = members_2.id)
8. 0.016 0.016 ↓ 2.1 37 1

Seq Scan on members members_1 (cost=0.00..3.14 rows=18 width=165) (actual time=0.006..0.016 rows=37 loops=1)

  • Filter: ((invite_token IS NULL) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 39
9. 0.001 0.048 ↑ 2.5 4 1

Hash (cost=42.92..42.92 rows=10 width=4) (actual time=0.048..0.048 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.000 0.047 ↑ 2.5 4 1

Append (cost=0.14..42.92 rows=10 width=4) (actual time=0.003..0.047 rows=4 loops=1)

11. 0.004 0.004 ↓ 4.0 4 1

Index Scan using index_members_on_source_id_and_source_type on members members_2 (cost=0.14..3.04 rows=1 width=4) (actual time=0.003..0.004 rows=4 loops=1)

  • Index Cond: ((source_id = 60) AND ((source_type)::text = 'Namespace'::text))
  • Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text))
12. 0.004 0.043 ↓ 0.0 0 1

Hash Semi Join (cost=36.33..39.78 rows=9 width=4) (actual time=0.043..0.043 rows=0 loops=1)

  • Hash Cond: (members_3.source_id = namespaces.id)
13. 0.018 0.026 ↓ 2.1 19 1

Seq Scan on members members_3 (cost=5.21..8.54 rows=9 width=8) (actual time=0.013..0.026 rows=19 loops=1)

  • Filter: ((requested_at IS NULL) AND (NOT (hashed SubPlan 1)) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 57
14.          

SubPlan (forSeq Scan)

15. 0.002 0.008 ↓ 4.0 4 1

Nested Loop (cost=0.28..5.21 rows=1 width=4) (actual time=0.004..0.008 rows=4 loops=1)

16. 0.002 0.002 ↓ 4.0 4 1

Index Scan using index_members_on_source_id_and_source_type on members members_6 (cost=0.14..3.04 rows=1 width=4) (actual time=0.001..0.002 rows=4 loops=1)

  • Index Cond: ((source_id = 60) AND ((source_type)::text = 'Namespace'::text))
  • Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text))
17. 0.004 0.004 ↑ 1.0 1 4

Index Only Scan using users_pkey on users users_1 (cost=0.14..2.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=4)

  • Index Cond: (id = members_6.user_id)
  • Heap Fetches: 4
18. 0.001 0.013 ↑ 21.0 1 1

Hash (cost=30.85..30.85 rows=21 width=4) (actual time=0.013..0.013 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.012 0.012 ↑ 21.0 1 1

CTE Scan on base_and_ancestors namespaces (cost=30.22..30.64 rows=21 width=4) (actual time=0.007..0.012 rows=1 loops=1)

20.          

CTE base_and_ancestors

21. 0.003 0.010 ↑ 21.0 1 1

Recursive Union (cost=0.14..30.22 rows=21 width=352) (actual time=0.005..0.010 rows=1 loops=1)

22. 0.002 0.002 ↑ 1.0 1 1

Index Scan using namespaces_pkey on namespaces namespaces_2 (cost=0.14..2.16 rows=1 width=352) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (id = 7)
  • Filter: ((type)::text = 'Group'::text)
23. 0.001 0.005 ↓ 0.0 0 1

Hash Join (cost=0.47..2.76 rows=2 width=352) (actual time=0.005..0.005 rows=0 loops=1)

  • Hash Cond: (namespaces_3.id = base_and_ancestors.parent_id)
24. 0.003 0.003 ↑ 13.0 1 1

Index Scan using index_namespaces_on_type on namespaces namespaces_3 (cost=0.14..2.37 rows=13 width=352) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: ((type)::text = 'Group'::text)
25. 0.001 0.001 ↓ 0.0 0 1

Hash (cost=0.20..0.20 rows=10 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
26. 0.000 0.000 ↑ 10.0 1 1

WorkTable Scan on base_and_ancestors (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=1 loops=1)

27. 0.005 0.052 ↑ 11.0 1 1

Hash Semi Join (cost=277.61..280.92 rows=11 width=165) (actual time=0.047..0.052 rows=1 loops=1)

  • Hash Cond: (members_4.source_id = namespaces_1.id)
28. 0.010 0.010 ↓ 2.1 37 1

Seq Scan on members members_4 (cost=0.00..3.14 rows=18 width=165) (actual time=0.002..0.010 rows=37 loops=1)

  • Filter: (((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 39
29. 0.000 0.037 ↑ 6.0 2 1

Hash (cost=277.46..277.46 rows=12 width=4) (actual time=0.037..0.037 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.037 0.037 ↑ 6.0 2 1

CTE Scan on base_and_ancestors namespaces_1 (cost=36.88..277.34 rows=12 width=4) (actual time=0.019..0.037 rows=2 loops=1)

  • Filter: ((visibility_level = ANY ('{0,10,20}'::integer[])) OR (alternatives: SubPlan 4 or hashed SubPlan 5))
31.          

CTE base_and_ancestors

32. 0.008 0.035 ↑ 10.5 2 1

Recursive Union (cost=2.69..36.88 rows=21 width=352) (actual time=0.018..0.035 rows=2 loops=1)

33. 0.003 0.017 ↑ 1.0 1 1

Hash Join (cost=2.69..8.82 rows=1 width=352) (actual time=0.016..0.017 rows=1 loops=1)

  • Hash Cond: (project_group_links.group_id = namespaces_4.id)
34. 0.002 0.002 ↑ 6.0 1 1

Index Scan using index_project_group_links_on_project_id on project_group_links (cost=0.15..6.26 rows=6 width=4) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (project_id = 18)
35. 0.007 0.012 ↑ 1.0 13 1

Hash (cost=2.37..2.37 rows=13 width=352) (actual time=0.012..0.012 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
36. 0.005 0.005 ↑ 1.0 13 1

Index Scan using index_namespaces_on_type on namespaces namespaces_4 (cost=0.14..2.37 rows=13 width=352) (actual time=0.002..0.005 rows=13 loops=1)

  • Index Cond: ((type)::text = 'Group'::text)
37. 0.004 0.010 ↓ 0.0 0 2

Hash Join (cost=0.47..2.76 rows=2 width=352) (actual time=0.004..0.005 rows=0 loops=2)

  • Hash Cond: (namespaces_5.id = base_and_ancestors_1.parent_id)
38. 0.004 0.004 ↑ 1.0 13 1

Index Scan using index_namespaces_on_type on namespaces namespaces_5 (cost=0.14..2.37 rows=13 width=352) (actual time=0.001..0.004 rows=13 loops=1)

  • Index Cond: ((type)::text = 'Group'::text)
39. 0.000 0.002 ↓ 0.0 0 2

Hash (cost=0.20..0.20 rows=10 width=4) (actual time=0.001..0.001 rows=0 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
40. 0.002 0.002 ↑ 10.0 1 2

WorkTable Scan on base_and_ancestors base_and_ancestors_1 (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.001 rows=1 loops=2)

41.          

SubPlan (forCTE Scan)

42. 0.000 0.000 ↓ 0.0 0

Subquery Scan on namespaces_6 (cost=11.41..11.47 rows=3 width=0) (never executed)

43. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=11.41..11.44 rows=3 width=474) (never executed)

  • Group Key: namespaces_7.id, namespaces_7.name, namespaces_7.path, namespaces_7.owner_id, namespaces_7.created_at, namespaces_7.updated_at, namespaces_7.type, namespaces_7.description, namespaces_7.avatar, namespaces_7.share_with_group_lock, namespaces_7.visibility_level, namespaces_7.request_access_enabled, namespaces_7.description_html, namespaces_7.lfs_enabled, namespaces_7.parent_id, namespaces_7.require_two_factor_authentication, namespaces_7.two_factor_grace_period, namespaces_7.cached_markdown_version, namespaces_7.runners_token, namespaces_7.runners_token_encrypted, namespaces_7.auto_devops_enabled, namespaces_7.project_creation_level, namespaces_7.last_ci_minutes_notification_at, namespaces_7.custom_project_templates_group_id, namespaces_7.file_template_project_id, namespaces_7.ldap_sync_error, namespaces_7.ldap_sync_last_successful_update_at, namespaces_7.ldap_sync_last_sync_at, namespaces_7.ldap_sync_last_update_at, namespaces_7.plan_id, namespaces_7.repository_size_limit, namespaces_7.saml_discovery_token, namespaces_7.shared_runners_minutes_limit, namespaces_7.trial_ends_on, namespaces_7.extra_shared_runners_minutes_limit, namespaces_7.ldap_sync_status, namespaces_7.membership_lock, namespaces_7.last_ci_minutes_usage_notification_level
44. 0.000 0.000 ↓ 0.0 0

Append (cost=0.29..11.12 rows=3 width=474) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.29..5.22 rows=1 width=352) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Index Scan using namespaces_pkey on namespaces namespaces_7 (cost=0.14..2.16 rows=1 width=352) (never executed)

  • Index Cond: (id = namespaces_1.id)
  • Filter: ((type)::text = 'Group'::text)
47. 0.000 0.000 ↓ 0.0 0

Index Scan using index_members_on_source_id_and_source_type on members members_7 (cost=0.14..3.05 rows=1 width=4) (never executed)

  • Index Cond: ((source_id = namespaces_1.id) AND ((source_type)::text = 'Namespace'::text))
  • Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text) AND (user_id = 1))
48. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.52..5.87 rows=2 width=352) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Index Scan using namespaces_pkey on namespaces namespaces_8 (cost=0.14..2.16 rows=1 width=352) (never executed)

  • Index Cond: (id = namespaces_1.id)
  • Filter: ((type)::text = 'Group'::text)
50. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=2.38..3.69 rows=2 width=4) (never executed)

  • Merge Cond: (projects.id = project_authorizations_1.project_id)
51. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.23..2.24 rows=2 width=8) (never executed)

  • Sort Key: projects.id
52. 0.000 0.000 ↓ 0.0 0

Seq Scan on projects (cost=0.00..2.23 rows=2 width=8) (never executed)

  • Filter: (namespace_id = namespaces_1.id)
53. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.14..1.39 rows=14 width=4) (never executed)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
54. 0.000 0.000 ↓ 0.0 0

Subquery Scan on namespaces_9 (cost=12.26..12.34 rows=4 width=4) (never executed)

55. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=12.26..12.30 rows=4 width=474) (never executed)

  • Group Key: namespaces_10.id, namespaces_10.name, namespaces_10.path, namespaces_10.owner_id, namespaces_10.created_at, namespaces_10.updated_at, namespaces_10.type, namespaces_10.description, namespaces_10.avatar, namespaces_10.share_with_group_lock, namespaces_10.visibility_level, namespaces_10.request_access_enabled, namespaces_10.description_html, namespaces_10.lfs_enabled, namespaces_10.parent_id, namespaces_10.require_two_factor_authentication, namespaces_10.two_factor_grace_period, namespaces_10.cached_markdown_version, namespaces_10.runners_token, namespaces_10.runners_token_encrypted, namespaces_10.auto_devops_enabled, namespaces_10.project_creation_level, namespaces_10.last_ci_minutes_notification_at, namespaces_10.custom_project_templates_group_id, namespaces_10.file_template_project_id, namespaces_10.ldap_sync_error, namespaces_10.ldap_sync_last_successful_update_at, namespaces_10.ldap_sync_last_sync_at, namespaces_10.ldap_sync_last_update_at, namespaces_10.plan_id, namespaces_10.repository_size_limit, namespaces_10.saml_discovery_token, namespaces_10.shared_runners_minutes_limit, namespaces_10.trial_ends_on, namespaces_10.extra_shared_runners_minutes_limit, namespaces_10.ldap_sync_status, namespaces_10.membership_lock, namespaces_10.last_ci_minutes_usage_notification_level
56. 0.000 0.000 ↓ 0.0 0

Append (cost=2.53..11.88 rows=4 width=474) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2.53..5.89 rows=1 width=352) (never executed)

  • Hash Cond: (members_8.source_id = namespaces_10.id)
58. 0.000 0.000 ↓ 0.0 0

Seq Scan on members members_8 (cost=0.00..3.33 rows=4 width=4) (never executed)

  • Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text) AND (user_id = 1))
59. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.37..2.37 rows=13 width=352) (never executed)

60. 0.000 0.000 ↓ 0.0 0

Index Scan using index_namespaces_on_type on namespaces namespaces_10 (cost=0.14..2.37 rows=13 width=352) (never executed)

  • Index Cond: ((type)::text = 'Group'::text)
61. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.68..5.95 rows=3 width=352) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2.53..4.82 rows=4 width=356) (never executed)

  • Hash Cond: (projects_1.namespace_id = namespaces_11.id)
63. 0.000 0.000 ↓ 0.0 0

Seq Scan on projects projects_1 (cost=0.00..2.18 rows=18 width=8) (never executed)

64. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.37..2.37 rows=13 width=352) (never executed)

65. 0.000 0.000 ↓ 0.0 0

Index Scan using index_namespaces_on_type on namespaces namespaces_11 (cost=0.14..2.37 rows=13 width=352) (never executed)

  • Index Cond: ((type)::text = 'Group'::text)
66. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.14..0.27 rows=1 width=4) (never executed)

  • Index Cond: ((user_id = 1) AND (project_id = projects_1.id))
  • Heap Fetches: 0
67. 0.002 0.002 ↓ 4.0 4 1

Index Scan using index_members_on_source_id_and_source_type on members members_5 (cost=0.14..3.04 rows=1 width=165) (actual time=0.001..0.002 rows=4 loops=1)

  • Index Cond: ((source_id = 18) AND ((source_type)::text = 'Project'::text))
  • Filter: ((requested_at IS NULL) AND ((type)::text = 'ProjectMember'::text))
68. 0.000 0.000 ↑ 1.0 1 9

Index Only Scan using users_pkey on users (cost=0.14..0.51 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=9)

  • Index Cond: (id = members_1.user_id)
  • Heap Fetches: 7
69. 0.009 0.009 ↑ 1.0 1 9

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.14..0.20 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=9)

  • Index Cond: ((user_id = users.id) AND (project_id = 18))
  • Heap Fetches: 0
Planning time : 2.807 ms