Skip to content

Add partial index in namespaces

Mansoor Khan requested to merge 214755-add-partial-index-on-groups-path-and-id into master

What does this MR do and why?

Adds a new partial index to improve performance of the API endpoint introduced in #214755 (closed)

Database Review

During the database review, it was discovered that the API is taking too long to return results for default API hit. Specifically, the API call to fetch all groups for admin users was taking > 2 minutes. Details below:

Admin Query without search param 
SELECT "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"."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" 
FROM 
"namespaces" 
WHERE "namespaces"."type" = 'Group' 
ORDER BY "namespaces"."path" ASC, "namespaces"."id" ASC 
LIMIT 20 OFFSET 100;
Query Plan 
 Limit  (cost=1400760.05..1400762.38 rows=20 width=366) (actual time=166120.001..166303.270 rows=20 loops=1)
   Buffers: shared hit=2268375 read=1496418 dirtied=13934 written=658
   I/O Timings: read=474219.230 write=54.157
   ->  Gather Merge  (cost=1400748.38..1783723.65 rows=3282416 width=366) (actual time=166119.853..166303.236 rows=120 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=2268375 read=1496418 dirtied=13934 written=658
         I/O Timings: read=474219.230 write=54.157
         ->  Sort  (cost=1399748.35..1403851.37 rows=1641208 width=366) (actual time=166104.840..166104.852 rows=92 loops=3)
               Sort Key: namespaces.path, namespaces.id
               Sort Method: top-N heapsort  Memory: 95kB
               Buffers: shared hit=2268375 read=1496418 dirtied=13934 written=658
               I/O Timings: read=474219.230 write=54.157
               ->  Parallel Index Scan using index_groups_on_parent_id_id on public.namespaces  (cost=0.43..1334864.09 rows=1641208 width=366) (actual time=1.518..163858.219 rows=1322468 loops=3)
                     Buffers: shared hit=2268297 read=1496418 dirtied=13934 written=658
                     I/O Timings: read=474219.230 write=54.157
Statistics 
Time: 2.772 min
  - planning: 3.697 ms
  - execution: 2.772 min
    - I/O read: 7.904 min
    - I/O write: 54.157 ms

Shared buffers:
  - hits: 2268375 (~17.30 GiB) from the buffer pool
  - reads: 1496418 (~11.40 GiB) from the OS file cache, including disk I/O
  - dirtied: 13934 (~108.90 MiB)
  - writes: 658 (~5.10 MiB)

To overcome this performance hit, it was decided to create a partial index to speed up the results. Details below:

New query plan with partial index added 
Limit  (cost=38.12..45.66 rows=20 width=366) (actual time=7.344..8.242 rows=20 loops=1)
   Buffers: shared hit=1 read=118 dirtied=3
   I/O Timings: read=7.801 write=0.000
   ->  Index Scan using index_groups_on_path_and_id on public.namespaces  (cost=0.43..1510456.48 rows=4007057 width=366) (actual time=0.300..8.225 rows=120 loops=1)
         Buffers: shared hit=1 read=118 dirtied=3
         I/O Timings: read=7.801 write=0.000
Statistics 
Time: 11.765 ms
  - planning: 3.454 ms
  - execution: 8.311 ms
    - I/O read: 7.801 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 118 (~944.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 3 (~24.00 KiB)
  - writes: 0
Migration task outputs
rake db:migrate:up 
main: == 20230109103100 AddPartialIndexOnGroupPathId: migrating =====================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0884s
main: -- index_exists?(:namespaces, [:path, :id], {:where=>"type = 'Group'", :name=>:index_groups_on_path_and_id, :algorithm=>:concurrently})
main:    -> 0.0428s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:namespaces, [:path, :id], {:where=>"type = 'Group'", :name=>:index_groups_on_path_and_id, :algorithm=>:concurrently})
main:    -> 0.0024s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20230109103100 AddPartialIndexOnGroupPathId: migrated (0.1468s) ============
rake db:migrate:down 
main: == 20230109103100 AddPartialIndexOnGroupPathId: reverting =====================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0814s
main: -- indexes(:namespaces)
main:    -> 0.0227s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0009s
main: -- remove_index(:namespaces, {:algorithm=>:concurrently, :name=>:index_groups_on_path_and_id})
main:    -> 0.0018s
main: -- execute("RESET statement_timeout")

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 #214755 (closed)

Merge request reports