Skip to content

Resolve "Index traversal_ids for group type"

Alex Pooley requested to merge 363730-index-traversal_ids-for-group-type into master

What does this MR do and why?

Add an index on traversal_ids where type = group using btree to improve edge case query optimizations.

$ rails db:migrate:up:main VERSION=20220530082653                                                                                                                                                    main: == 20220530082653 AddTraversalIdTypeGroupIndex: migrating =====================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:namespaces, :traversal_ids, {:using=>:btree, :where=>"type='Group'", :name=>"index_namespaces_on_traversal_ids_for_groups_btree", :algorithm=>:concurrently})
main:    -> 0.0305s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- add_index(:namespaces, :traversal_ids, {:using=>:btree, :where=>"type='Group'", :name=>"index_namespaces_on_traversal_ids_for_groups_btree", :algorithm=>:concurrently})
main:    -> 0.0116s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20220530082653 AddTraversalIdTypeGroupIndex: migrated (0.0663s) ============

$ rails db:migrate:down:main VERSION=20220530082653                                                                                                                                                  main: == 20220530082653 AddTraversalIdTypeGroupIndex: reverting =====================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:namespaces)
main:    -> 0.0324s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- remove_index(:namespaces, {:algorithm=>:concurrently, :name=>"index_namespaces_on_traversal_ids_for_groups_btree"})
main:    -> 0.0056s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20220530082653 AddTraversalIdTypeGroupIndex: reverted (0.0511s) ============

Query plans

Feature.enable(:use_traversal_ids)
Feature.enable(:traversal_ids_btree)
Feature.enable(:use_traversal_ids_for_descendants_scopes)
Feature.enable(:linear_scopes_superset)

Feature.enable(:linear_user_manageable_groups)

user = User.find(1675733) # gitlab-qa-bot
User.can?(:create_fork)

New plan

 Unique  (cost=619173.64..663583.84 rows=370085 width=364) (actual time=0.661..0.665 rows=1 loops=1)
   Buffers: shared hit=85
   I/O Timings: read=0.000 write=0.000
   CTE descendants_base_cte
     ->  Nested Loop  (cost=1.13..12.97 rows=1 width=32) (actual time=0.097..0.100 rows=1 loops=1)
           Buffers: shared hit=10
           I/O Timings: read=0.000 write=0.000
           ->  Index Scan using index_members_on_user_id_source_id_source_type on public.members  (cost=0.56..9.38 rows=1 width=4) (actual time=0.066..0.067 rows=1 loops=1)
                 Index Cond: ((members.user_id = 1675733) AND ((members.source_type)::text = 'Namespace'::text))
                 Filter: ((members.requested_at IS NULL) AND (members.access_level >= 10) AND (members.access_level = ANY ('{40,50}'::integer[])) AND ((members.type)::text = 'GroupMember'::text))
                 Rows Removed by Filter: 0
                 Buffers: shared hit=5
                 I/O Timings: read=0.000 write=0.000
           ->  Index Scan using namespaces_pkey on public.namespaces namespaces_1  (cost=0.56..3.58 rows=1 width=32) (actual time=0.029..0.030 rows=1 loops=1)
                 Index Cond: (namespaces_1.id = members.source_id)
                 Filter: ((namespaces_1.type)::text = 'Group'::text)
                 Rows Removed by Filter: 0
                 Buffers: shared hit=5
                 I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=619160.68..620085.89 rows=370085 width=364) (actual time=0.660..0.661 rows=1 loops=1)
         Sort Key: namespaces.id, namespaces.name, namespaces.path, namespaces.owner_id, namespaces.created_at, namespaces.updated_at, 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.description_html, namespaces.lfs_enabled, 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
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=85
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=0.80..465389.95 rows=370085 width=364) (actual time=0.554..0.556 rows=1 loops=1)
               Buffers: shared hit=57
               I/O Timings: read=0.000 write=0.000
               ->  Nested Loop Anti Join  (cost=0.00..0.07 rows=1 width=32) (actual time=0.103..0.104 rows=1 loops=1)
                     Buffers: shared hit=10
                     I/O Timings: read=0.000 write=0.000
                     ->  CTE Scan on descendants_base_cte d1  (cost=0.00..0.02 rows=1 width=36) (actual time=0.099..0.100 rows=1 loops=1)
                           Buffers: shared hit=10
                           I/O Timings: read=0.000 write=0.000
                     ->  CTE Scan on descendants_base_cte d2  (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.002 rows=1 loops=1)
                           I/O Timings: read=0.000 write=0.000
               ->  Index Scan using index_namespaces_on_traversal_ids_for_groups2 on public.namespaces  (cost=0.80..461689.03 rows=370085 width=364) (actual time=0.050..0.051 rows=1 loops=1)
                     Index Cond: ((namespaces.traversal_ids < next_traversal_ids_sibling(d1.traversal_ids)) AND (namespaces.traversal_ids >= d1.traversal_ids))
                     Buffers: shared hit=5
                     I/O Timings: read=0.000 write=0.000

Time: 2.337 ms  
  - planning: 1.539 ms  
  - execution: 0.798 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 85 (~680.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

Details and visualization: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/10332/commands/36515.

Old plan

Time: 674.442 ms  
  - planning: 6.115 ms  
  - execution: 668.327 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 14800 (~115.60 MiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

Details and visualization: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/10332/commands/36517.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #363730 (closed)

Edited by Alex Pooley

Merge request reports