Resolve "Index traversal_ids for group type"
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.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #363730 (closed)
Edited by Alex Pooley