explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x4fA

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

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

  • CTE base_and_ancestors59..0.216 rows=8 loops=1) (cost=0.87..369.50 rows=21 width=345) (actual time=0.0--More--
  • CTE base_and_descendants.021..6.005 rows=665 loops=1)st=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.003 0.148 ↓ 7.0 7 1

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

3. 0.061 0.061 ↓ 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.026..0.061 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.084 0.084 ↑ 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.012..0.012 rows=1 loops=7)

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

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

6. 0.002 0.002 ↑ 2.5 4 2

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

7. 0.016 0.016 ↓ 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.002..0.002 rows=0 loops=8)

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

Nested Loop (cost=0.87..21.83 rows=1 width=345) (actual time=0.019..0.043 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.008..0.015 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.021 0.021 ↑ 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.003..0.003 rows=1 loops=7)

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

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

11. 0.063 0.063 ↓ 7.4 74 9

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

12. 3.990 3.990 ↑ 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.004..0.006 rows=1 loops=665)

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

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

14. 0.232 0.232 ↓ 2.0 2 1

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

  • Filter: (require_two_factor_authentication AND ((type)::text = 'Group'::text))
  • Rows Removed by Filter: 6
15. 6.994 6.994 ↓ 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.048..6.994 rows=12 loops=1)

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