explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wjmj

Settings
# exclusive inclusive rows x rows loops node
1. 111.798 111.798 ↑ 8.7 47 1

CTE Scan on base_and_ancestors namespaces (cost=135,118.88..135,127.08 rows=410 width=2,946) (actual time=111.231..111.798 rows=47 loops=1)

  • Buffers: shared hit=49,882
2.          

CTE base_and_ancestors

3. 0.216 111.736 ↑ 8.7 47 1

Recursive Union (cost=128,165.28..135,118.88 rows=410 width=2,946) (actual time=111.228..111.736 rows=47 loops=1)

  • Buffers: shared hit=49,882
4. 0.005 111.248 ↑ 1.0 20 1

Limit (cost=128,165.28..128,165.68 rows=20 width=2,946) (actual time=111.219..111.248 rows=20 loops=1)

  • Buffers: shared hit=49,714
5.          

CTE base_and_ancestors

6. 4.064 96.101 ↑ 11.4 631 1

Recursive Union (cost=7,325.92..126,552.53 rows=7,219 width=2,946) (actual time=87.867..96.101 rows=631 loops=1)

  • Buffers: shared hit=45,886
7. 23.175 88.732 ↓ 1.7 577 1

HashAggregate (cost=7,325.92..7,329.41 rows=349 width=2,946) (actual time=87.860..88.732 rows=577 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
  • Buffers: shared hit=43,470
8. 1.023 65.557 ↓ 14.4 5,029 1

Append (cost=0.87..7,283.16 rows=349 width=2,946) (actual time=0.083..65.557 rows=5,029 loops=1)

  • Buffers: shared hit=43,470
9. 0.032 0.723 ↓ 10.0 10 1

Nested Loop (cost=0.87..21.76 rows=1 width=346) (actual time=0.082..0.723 rows=10 loops=1)

  • Buffers: shared hit=147
10. 0.551 0.551 ↓ 10.0 10 1

Index Scan using index_members_on_user_id on public.members (cost=0.44..18.30 rows=1 width=4) (actual time=0.037..0.551 rows=10 loops=1)

  • Index Cond: (members.user_id = 590,748)
  • Filter: ((members.requested_at IS NULL) AND ((members.type)::text = 'GroupMember'::text) AND ((members.source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 95
  • Buffers: shared hit=107
11. 0.140 0.140 ↑ 1.0 1 10

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

  • Index Cond: (namespaces_1.id = members.source_id)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=40
12. 5.399 63.811 ↓ 14.4 5,019 1

Nested Loop (cost=1.44..7,256.17 rows=348 width=346) (actual time=0.068..63.811 rows=5,019 loops=1)

  • Buffers: shared hit=43,323
13. 3.683 38.056 ↓ 2.9 5,089 1

Nested Loop (cost=1.01..6,407.47 rows=1,768 width=4) (actual time=0.053..38.056 rows=5,089 loops=1)

  • Buffers: shared hit=22,967
14. 3.839 3.839 ↓ 2.9 5,089 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations (cost=0.57..333.45 rows=1,768 width=4) (actual time=0.028..3.839 rows=5,089 loops=1)

  • Index Cond: (project_authorizations.user_id = 590,748)
  • Heap Fetches: 824
  • Buffers: shared hit=2,609
15. 30.534 30.534 ↑ 1.0 1 5,089

Index Scan using projects_pkey on public.projects (cost=0.43..3.44 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=5,089)

  • Index Cond: (projects.id = project_authorizations.project_id)
  • Buffers: shared hit=20,358
16. 20.356 20.356 ↑ 1.0 1 5,089

Index Scan using namespaces_pkey on public.namespaces namespaces_2 (cost=0.43..0.48 rows=1 width=346) (actual time=0.004..0.004 rows=1 loops=5,089)

  • Index Cond: (namespaces_2.id = projects.namespace_id)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=20,356
17. 0.681 3.305 ↑ 5.7 121 5

Nested Loop (cost=0.43..11,907.53 rows=687 width=346) (actual time=0.010..0.661 rows=121 loops=5)

  • Buffers: shared hit=2,416
18. 0.100 0.100 ↑ 27.7 126 5

WorkTable Scan on base_and_ancestors (cost=0.00..69.80 rows=3,490 width=4) (actual time=0.001..0.020 rows=126 loops=5)

19. 2.524 2.524 ↑ 1.0 1 631

Index Scan using namespaces_pkey on public.namespaces namespaces_3 (cost=0.43..3.39 rows=1 width=346) (actual time=0.004..0.004 rows=1 loops=631)

  • Index Cond: (namespaces_3.id = base_and_ancestors.parent_id)
  • Filter: ((namespaces_3.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2,416
20.          

CTE base_and_descendants

21. 2.184 7.725 ↓ 4.9 740 1

Recursive Union (cost=0.87..1,441.84 rows=151 width=346) (actual time=0.050..7.725 rows=740 loops=1)

  • Buffers: shared hit=3,828
22. 0.024 0.245 ↓ 10.0 10 1

Nested Loop (cost=0.87..21.76 rows=1 width=346) (actual time=0.042..0.245 rows=10 loops=1)

  • Buffers: shared hit=147
23. 0.161 0.161 ↓ 10.0 10 1

Index Scan using index_members_on_user_id on public.members members_1 (cost=0.44..18.30 rows=1 width=4) (actual time=0.027..0.161 rows=10 loops=1)

  • Index Cond: (members_1.user_id = 590,748)
  • Filter: ((members_1.requested_at IS NULL) AND ((members_1.type)::text = 'GroupMember'::text) AND ((members_1.source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 95
  • Buffers: shared hit=107
24. 0.060 0.060 ↑ 1.0 1 10

Index Scan using namespaces_pkey on public.namespaces namespaces_4 (cost=0.43..3.45 rows=1 width=346) (actual time=0.006..0.006 rows=1 loops=10)

  • Index Cond: (namespaces_4.id = members_1.source_id)
  • Filter: ((namespaces_4.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=40
25. 0.736 5.296 ↓ 6.1 91 8

Nested Loop (cost=0.56..141.71 rows=15 width=346) (actual time=0.025..0.662 rows=91 loops=8)

  • Buffers: shared hit=3,681
26. 0.120 0.120 ↓ 9.2 92 8

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.015 rows=92 loops=8)

27. 4.440 4.440 ↑ 2.0 1 740

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_5 (cost=0.56..14.13 rows=2 width=346) (actual time=0.004..0.006 rows=1 loops=740)

  • Index Cond: (namespaces_5.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_5.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=3,681
28. 4.848 111.243 ↑ 1.9 20 1

HashAggregate (cost=170.91..171.28 rows=37 width=2,946) (actual time=111.218..111.243 rows=20 loops=1)

  • Group Key: namespaces_6.id, namespaces_6.name, namespaces_6.path, namespaces_6.owner_id, namespaces_6.created_at, namespaces_6.updated_at, namespaces_6.type, namespaces_6.description, namespaces_6.avatar, namespaces_6.membership_lock, namespaces_6.share_with_group_lock, namespaces_6.visibility_level, namespaces_6.request_access_enabled, namespaces_6.ldap_sync_status, namespaces_6.ldap_sync_error, namespaces_6.ldap_sync_last_update_at, namespaces_6.ldap_sync_last_successful_update_at, namespaces_6.ldap_sync_last_sync_at, namespaces_6.lfs_enabled, namespaces_6.description_html, namespaces_6.parent_id, namespaces_6.shared_runners_minutes_limit, namespaces_6.repository_size_limit, namespaces_6.require_two_factor_authentication, namespaces_6.two_factor_grace_period, namespaces_6.cached_markdown_version, namespaces_6.project_creation_level, namespaces_6.runners_token, namespaces_6.file_template_project_id, namespaces_6.saml_discovery_token, namespaces_6.runners_token_encrypted, namespaces_6.custom_project_templates_group_id, namespaces_6.auto_devops_enabled, namespaces_6.extra_shared_runners_minutes_limit, namespaces_6.last_ci_minutes_notification_at, namespaces_6.last_ci_minutes_usage_notification_level, namespaces_6.subgroup_creation_level, namespaces_6.emails_disabled, namespaces_6.max_pages_size, namespaces_6.max_artifacts_size, namespaces_6.mentions_disabled, namespaces_6.default_branch_protection, namespaces_6.unlock_membership_to_ldap, namespaces_6.max_personal_access_token_lifetime, namespaces_6.push_rule_id, namespaces_6.shared_runners_enabled, namespaces_6.allow_descendants_override_disabled_shared_runners, namespaces_6.traversal_ids, namespaces_6.delayed_project_removal
  • Buffers: shared hit=49,714
29. 0.252 106.395 ↓ 37.1 1,371 1

Append (cost=0.00..166.38 rows=37 width=2,946) (actual time=87.873..106.395 rows=1,371 loops=1)

  • Buffers: shared hit=49,714
30. 97.190 97.190 ↓ 17.5 631 1

CTE Scan on base_and_ancestors namespaces_6 (cost=0.00..162.43 rows=36 width=2,946) (actual time=87.871..97.190 rows=631 loops=1)

  • Filter: ((namespaces_6.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=45,886
31. 8.953 8.953 ↓ 740.0 740 1

CTE Scan on base_and_descendants namespaces_7 (cost=0.00..3.40 rows=1 width=2,946) (actual time=0.053..8.953 rows=740 loops=1)

  • Filter: ((namespaces_7.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=3,828
32. 0.025 0.272 ↑ 3.9 10 4

Nested Loop (cost=0.43..694.50 rows=39 width=346) (actual time=0.011..0.068 rows=10 loops=4)

  • Buffers: shared hit=168
33. 0.012 0.012 ↑ 16.7 12 4

WorkTable Scan on base_and_ancestors base_and_ancestors_1 (cost=0.00..4.00 rows=200 width=4) (actual time=0.001..0.003 rows=12 loops=4)

34. 0.235 0.235 ↑ 1.0 1 47

Index Scan using namespaces_pkey on public.namespaces namespaces_8 (cost=0.43..3.45 rows=1 width=346) (actual time=0.005..0.005 rows=1 loops=47)

  • Index Cond: (namespaces_8.id = base_and_ancestors_1.parent_id)
  • Filter: ((namespaces_8.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=168