explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VkLp

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 522.524 ↑ 100.0 2 1

Aggregate (cost=196,144.56..196,227.31 rows=200 width=12) (actual time=522.523..522.524 rows=2 loops=1)

  • Group Key: namespaces.user_id
  • Buffers: shared hit=260 read=286 dirtied=1
  • I/O Timings: read=518.092
2. 0.052 522.515 ↑ 5,383.0 2 1

Sort (cost=196,144.56..196,171.48 rows=10,766 width=4) (actual time=522.514..522.515 rows=2 loops=1)

  • Sort Key: namespaces.user_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=260 read=286 dirtied=1
  • I/O Timings: read=518.092
3. 0.003 522.463 ↑ 5,383.0 2 1

Subquery Scan on namespaces (cost=195,208.23..195,423.55 rows=10,766 width=4) (actual time=522.389..522.463 rows=2 loops=1)

  • Buffers: shared hit=257 read=286 dirtied=1
  • I/O Timings: read=518.092
4. 0.159 522.460 ↑ 5,383.0 2 1

HashAggregate (cost=195,208.23..195,315.89 rows=10,766 width=2,950) (actual time=522.388..522.460 rows=2 loops=1)

  • Group Key: namespaces_1.id, namespaces_1.name, namespaces_1.path, namespaces_1.owner_id, namespaces_1.created_at, namespaces_1.updated_at, namespaces_1.type, namespaces_1.description, namespaces_1.avatar, namespaces_1.membership_lock, namespaces_1.share_with_group_lock, namespaces_1.visibility_level, namespaces_1.request_access_enabled, namespaces_1.ldap_sync_status, namespaces_1.ldap_sync_error, namespaces_1.ldap_sync_last_update_at, namespaces_1.ldap_sync_last_successful_update_at, namespaces_1.ldap_sync_last_sync_at, namespaces_1.lfs_enabled, namespaces_1.description_html, namespaces_1.parent_id, namespaces_1.shared_runners_minutes_limit, namespaces_1.repository_size_limit, namespaces_1.require_two_factor_authentication, namespaces_1.two_factor_grace_period, namespaces_1.cached_markdown_version, namespaces_1.project_creation_level, namespaces_1.runners_token, namespaces_1.file_template_project_id, namespaces_1.saml_discovery_token, namespaces_1.runners_token_encrypted, namespaces_1.custom_project_templates_group_id, namespaces_1.auto_devops_enabled, namespaces_1.extra_shared_runners_minutes_limit, namespaces_1.last_ci_minutes_notification_at, namespaces_1.last_ci_minutes_usage_notification_level, namespaces_1.subgroup_creation_level, namespaces_1.emails_disabled, namespaces_1.max_pages_size, namespaces_1.max_artifacts_size, namespaces_1.mentions_disabled, namespaces_1.default_branch_protection, namespaces_1.unlock_membership_to_ldap, namespaces_1.max_personal_access_token_lifetime, namespaces_1.push_rule_id, namespaces_1.shared_runners_enabled, namespaces_1.allow_descendants_override_disabled_shared_runners, namespaces_1.traversal_ids, namespaces_1.delayed_project_removal, members.user_id
  • Buffers: shared hit=257 read=286 dirtied=1
  • I/O Timings: read=518.092
5. 0.009 522.301 ↑ 2,153.2 5 1

Append (cost=0.86..193,862.48 rows=10,766 width=2,950) (actual time=35.205..522.301 rows=5 loops=1)

  • Buffers: shared hit=257 read=286 dirtied=1
  • I/O Timings: read=518.092
6. 0.011 136.742 ↑ 3.0 1 1

Nested Loop (cost=0.86..452.80 rows=3 width=350) (actual time=35.203..136.742 rows=1 loops=1)

  • Buffers: shared hit=25 read=82
  • I/O Timings: read=135.816
7. 132.079 132.079 ↑ 13.0 1 1

Index Scan using index_members_on_user_id on public.members (cost=0.44..407.98 rows=13 width=8) (actual time=30.542..132.079 rows=1 loops=1)

  • Index Cond: (members.user_id = ANY ('{200,401,1003,40098,432190,4456462,135664,3432534,3231111,35656,21212,435436,325452,1234132,5654767,5647634,56266,324325,254356,6578356}'::integer[]))
  • Filter: ((members.requested_at IS NULL) AND (members.access_level <> 5) AND (members.access_level >= 10) AND ((members.source_type)::text = 'Namespace'::text) AND ((members.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 43
  • Buffers: shared hit=25 read=78
  • I/O Timings: read=131.222
8. 4.652 4.652 ↑ 1.0 1 1

Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=346) (actual time=4.652..4.652 rows=1 loops=1)

  • Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = members.source_id))
  • Buffers: shared read=4
  • I/O Timings: read=4.595
9. 0.251 385.550 ↑ 2,690.8 4 1

Nested Loop (cost=1.44..193,248.19 rows=10,763 width=350) (actual time=93.659..385.550 rows=4 loops=1)

  • Buffers: shared hit=232 read=204 dirtied=1
  • I/O Timings: read=382.276
10. 0.227 342.197 ↑ 1,210.6 46 1

Nested Loop (cost=1.01..166,942.30 rows=55,686 width=8) (actual time=15.247..342.197 rows=46 loops=1)

  • Buffers: shared hit=118 read=176 dirtied=1
  • I/O Timings: read=340.053
11. 119.698 119.698 ↑ 1,210.6 46 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations (cost=0.57..8,790.38 rows=55,686 width=8) (actual time=7.803..119.698 rows=46 loops=1)

  • Index Cond: (project_authorizations.user_id = ANY ('{200,401,1003,40098,432190,4456462,135664,3432534,3231111,35656,21212,435436,325452,1234132,5654767,5647634,56266,324325,254356,6578356}'::integer[]))
  • Heap Fetches: 7
  • Buffers: shared hit=49 read=61 dirtied=1
  • I/O Timings: read=118.967
12. 222.272 222.272 ↑ 1.0 1 46

Index Scan using projects_pkey on public.projects (cost=0.43..2.84 rows=1 width=8) (actual time=4.832..4.832 rows=1 loops=46)

  • Index Cond: (projects.id = project_authorizations.project_id)
  • Buffers: shared hit=69 read=115
  • I/O Timings: read=221.086
13. 43.102 43.102 ↓ 0.0 0 46

Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_2 (cost=0.43..0.47 rows=1 width=346) (actual time=0.937..0.937 rows=0 loops=46)

  • Index Cond: (((namespaces_2.type)::text = 'Group'::text) AND (namespaces_2.id = projects.namespace_id))
  • Buffers: shared hit=114 read=28
  • I/O Timings: read=42.223