explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1fTG

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 20.486 ↓ 6.0 12 1

Unique (cost=4.13..4.38 rows=2 width=2,946) (actual time=20.461..20.486 rows=12 loops=1)

  • CTE base_and_ancestors11..2.003 rows=8 loops=1) (cost=0.87..369.50 rows=21 width=345) (actual time=0.2--More--
  • CTE base_and_descendants.026..17.336 rows=665 loops=1)t=0.87..1445.85 rows=161 width=345) (actual time=0--More--
  • Sort Key: namespaces.id, namespaces.name, namespaces.path, namespaces.owner_id, namespaces.created_at, namespaces.updated_at, namespaces.type, namespaces.description, namespaces.avatar, namespaces.membership_lock, namespaces.share_with_group_lock, namespaces.visibility_level, namespaces.request_access_enabled, namespaces.ldap_sync_status, namespaces.ldap_sync_error, namespaces.ldap_sync_last_update_at, namespaces.ldap_sync_last_successful_update_at, namespaces.ldap_sync_last_sync_at, namespaces.lfs_enabled, namespaces.description_html, namespaces.parent_id, namespaces.shared_runners_minutes_limit, namespaces.repository_size_limit, namespaces.require_two_factor_authentication, namespaces.two_factor_grace_period, namespaces.cached_markdown_version, namespaces.project_creation_level, namespaces.runners_token, namespaces.file_template_project_id, namespaces.saml_discovery_token, namespaces.runners_token_encrypted, namespaces.custom_project_templates_group_id, namespaces.auto_devops_enabled, namespaces.extra_shared_runners_minutes_limit, namespaces.last_ci_minutes_notification_at, namespaces.last_ci_minutes_usage_notification_level, namespaces.subgroup_creation_level, namespaces.emails_disabled, namespaces.max_pages_size, namespaces.max_artifacts_size, namespaces.mentions_disabled, namespaces.default_branch_protection, namespaces.unlock_membership_to_ldap, namespaces.max_personal_access_token_lifetime, namespaces.push_rule_id, namespaces.shared_runners_enabled, namespaces.allow_descendants_override_disabled_shared_runners, namespaces.traversal_ids, namespaces.delayed_project_removal
  • Sort Method: quicksort Memory: 33kB
2. 0.010 1.896 ↓ 7.0 7 1

Nested Loop (cost=0.87..21.83 rows=1 width=345) (actual time=0.203..1.896 rows=7 loops=1)

3. 1.473 1.473 ↓ 7.0 7 1

Index Scan using index_members_on_user_id on members (cost=0.44..18.38 rows=1 width=4) (actual time=0.124..1.473 rows=7 loops=1)

  • Index Cond: (user_id = 2,982,208)
  • Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 6
4. 0.413 0.413 ↑ 1.0 1 7

Index Scan using namespaces_pkey on namespaces namespaces_2 (cost=0.43..3.45 rows=1 width=345) (actual time=0.059..0.059 rows=1 loops=7)

  • Index Cond: (id = members.source_id)
  • Filter: ((type)::text = 'Group'::text)
5. 0.010 0.054 ↑ 1.0 2 2

Nested Loop (cost=0.43..34.73 rows=2 width=345) (actual time=0.012..0.027 rows=2 loops=2)

6. 0.004 0.004 ↑ 2.5 4 2

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

7. 0.040 0.040 ↓ 0.0 0 8

Index Scan using namespaces_pkey on namespaces namespaces_3 (cost=0.43..3.45 rows=1 width=345) (actual time=0.005..0.005 rows=0 loops=8)

  • Index Cond: (id = base_and_ancestors.parent_id)
  • Filter: ((type)::text = 'Group'::text)
8. 0.032 0.060 ↓ 7.0 7 1

Nested Loop (cost=0.87..21.83 rows=1 width=345) (actual time=0.023..0.060 rows=7 loops=1)

  • -> Index Scan using index_members_on_user_id on members members_1 (cost=0.44..18.38 rows=1 width=4) (actual time=0.010..0.026 rows=7 loops=1) Index Cond: (user_id = 2,982,208)
  • Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 6
9. 0.028 0.028 ↑ 1.0 1 7

Index Scan using namespaces_pkey on namespaces namespaces_4 (cost=0.43..3.45 rows=1 width=345) (actual time=0.004..0.004 rows=1 loops=7)

  • Index Cond: (id = members_1.source_id)
  • Filter: ((type)::text = 'Group'::text)
10. 0.374 15.741 ↓ 4.6 73 9

Nested Loop (cost=0.56..142.08 rows=16 width=345) (actual time=0.045..1.749 rows=73 loops=9)

11. 0.072 0.072 ↓ 7.4 74 9

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.008 rows=74 loops=9)

12. 15.295 15.295 ↑ 2.0 1 665

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_5 (cost=0.56..14.17 rows=2 width=345) (actual time=0.012..0.023 rows=1 loops=665)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)s=14 loops=1)(cost=4.13..4.14 rows=2 width=2,946) (actual time=20.461..20.462 row--More--
13. 0.003 20.326 ↓ 7.0 14 1

Append (cost=0.00..4.12 rows=2 width=2,946) (actual time=0.611..20.326 rows=14 loops=1)

14. 2.016 2.016 ↓ 2.0 2 1

CTE Scan on base_and_ancestors namespaces (cost=0.00..0.47 rows=1 width=2,946) (actual time=0.611..2.016 rows=2 loops=1)

  • Filter: (require_two_factor_authentication AND ((type)::text = 'Group'::text))
  • Rows Removed by Filter: 6
15. 18.307 18.307 ↓ 12.0 12 1

CTE Scan on base_and_descendants namespaces_1 (cost=0.00..3.62 rows=1 width=2,946) (actual time=0.061..18.307 rows=12 loops=1)

  • Filter: (require_two_factor_authentication AND ((type)::text = 'Group'::text))
  • Rows Removed by Filter: 653
Planning time : 6.948 ms
Execution time : 21.263 ms