explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BDDG

Settings
# exclusive inclusive rows x rows loops node
1. 0.150 16,800.646 ↑ 25.0 8 1

Aggregate (cost=203,066.92..203,148.44 rows=200 width=12) (actual time=16,800.612..16,800.646 rows=8 loops=1)

  • Group Key: namespaces.user_id
  • Buffers: shared hit=28,582 read=11,172 dirtied=918
  • I/O Timings: read=16,366.391
2. 0.384 16,800.496 ↑ 14.1 752 1

Sort (cost=203,066.92..203,093.43 rows=10,603 width=4) (actual time=16,800.432..16,800.496 rows=752 loops=1)

  • Sort Key: namespaces.user_id
  • Sort Method: quicksort Memory: 60kB
  • Buffers: shared hit=28,582 read=11,172 dirtied=918
  • I/O Timings: read=16,366.391
3. 0.203 16,800.112 ↑ 14.1 752 1

Subquery Scan on namespaces (cost=202,145.93..202,357.99 rows=10,603 width=4) (actual time=16,798.089..16,800.112 rows=752 loops=1)

  • Buffers: shared hit=28,579 read=11,172 dirtied=918
  • I/O Timings: read=16,366.391
4. 60.187 16,799.909 ↑ 14.1 752 1

HashAggregate (cost=202,145.93..202,251.96 rows=10,603 width=2,950) (actual time=16,798.087..16,799.909 rows=752 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=28,579 read=11,172 dirtied=918
  • I/O Timings: read=16,366.391
5. 3.718 16,739.722 ↑ 2.4 4,399 1

Append (cost=0.86..200,820.56 rows=10,603 width=2,950) (actual time=13.347..16,739.722 rows=4,399 loops=1)

  • Buffers: shared hit=28,579 read=11,172 dirtied=918
  • I/O Timings: read=16,366.391
6. 0.835 885.884 ↓ 41.3 124 1

Nested Loop (cost=0.86..452.59 rows=3 width=350) (actual time=13.346..885.884 rows=124 loops=1)

  • Buffers: shared hit=356 read=519 dirtied=10
  • I/O Timings: read=874.787
7. 507.900 507.900 ↓ 10.1 131 1

Index Scan using index_members_on_user_id on public.members (cost=0.44..407.77 rows=13 width=8) (actual time=5.934..507.900 rows=131 loops=1)

  • Index Cond: (members.user_id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}'::integer[]))
  • Filter: ((members.requested_at IS NULL) AND (members.access_level >= 10) AND ((members.type)::text = 'GroupMember'::text) AND ((members.source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 184
  • Buffers: shared hit=70 read=288 dirtied=8
  • I/O Timings: read=504.899
8. 377.149 377.149 ↑ 1.0 1 131

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=2.879..2.879 rows=1 loops=131)

  • Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = members.source_id))
  • Buffers: shared hit=286 read=231 dirtied=2
  • I/O Timings: read=369.888
9. 0.001 2.846 ↓ 0.0 0 1

Nested Loop (cost=44.70..49.27 rows=1 width=350) (actual time=2.846..2.846 rows=0 loops=1)

  • Buffers: shared hit=60 read=3
  • I/O Timings: read=2.594
10. 0.004 2.845 ↓ 0.0 0 1

Bitmap Heap Scan on public.members members_1 (cost=44.28..45.82 rows=1 width=8) (actual time=2.845..2.845 rows=0 loops=1)

  • Filter: (((members_1.type)::text = 'GroupMember'::text) AND ((members_1.source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=60 read=3
  • I/O Timings: read=2.594
11. 0.010 2.841 ↓ 0.0 0 1

BitmapAnd (cost=44.28..44.28 rows=1 width=0) (actual time=2.841..2.841 rows=0 loops=1)

  • Buffers: shared hit=60 read=3
  • I/O Timings: read=2.594
12. 2.694 2.694 ↓ 1.7 113 1

Bitmap Index Scan using index_members_on_access_level (cost=0.00..2.42 rows=65 width=0) (actual time=2.694..2.694 rows=113 loops=1)

  • Index Cond: (members_1.access_level = 5)
  • Buffers: shared read=3
  • I/O Timings: read=2.594
13. 0.137 0.137 ↑ 1.2 315 1

Bitmap Index Scan using index_members_on_user_id (cost=0.00..41.60 rows=382 width=0) (actual time=0.137..0.137 rows=315 loops=1)

  • Index Cond: (members_1.user_id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}'::integer[]))
  • Buffers: shared hit=60
14. 0.000 0.000 ↓ 0.0 0 0

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

  • Index Cond: (((namespaces_2.type)::text = 'Group'::text) AND (namespaces_2.id = members_1.source_id))
15. 27.511 15,847.274 ↑ 2.5 4,275 1

Nested Loop (cost=1.44..200,159.66 rows=10,599 width=350) (actual time=48.397..15,847.274 rows=4,275 loops=1)

  • Buffers: shared hit=28,163 read=10,650 dirtied=908
  • I/O Timings: read=15,489.010
16. 20.496 14,083.294 ↑ 12.2 4,487 1

Nested Loop (cost=1.01..174,333.01 rows=54,708 width=8) (actual time=13.077..14,083.294 rows=4,487 loops=1)

  • Buffers: shared hit=11,604 read=9,462 dirtied=904
  • I/O Timings: read=13,876.231
17. 2,800.428 2,800.428 ↑ 12.2 4,487 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations (cost=0.57..18,657.64 rows=54,708 width=8) (actual time=5.215..2,800.428 rows=4,487 loops=1)

  • Index Cond: (project_authorizations.user_id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}'::integer[]))
  • Heap Fetches: 1,444
  • Buffers: shared hit=1,723 read=1,389 dirtied=642
  • I/O Timings: read=2,768.639
18. 11,262.370 11,262.370 ↑ 1.0 1 4,487

Index Scan using projects_pkey on public.projects (cost=0.43..2.85 rows=1 width=8) (actual time=2.510..2.510 rows=1 loops=4,487)

  • Index Cond: (projects.id = project_authorizations.project_id)
  • Buffers: shared hit=9,881 read=8,073 dirtied=262
  • I/O Timings: read=11,107.592
19. 1,736.469 1,736.469 ↑ 1.0 1 4,487

Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_3 (cost=0.43..0.47 rows=1 width=346) (actual time=0.387..0.387 rows=1 loops=4,487)

  • Index Cond: (((namespaces_3.type)::text = 'Group'::text) AND (namespaces_3.id = projects.namespace_id))
  • Buffers: shared hit=16,559 read=1,188 dirtied=4
  • I/O Timings: read=1,612.779