Skip to content

Fix performance of the environments search

What does this MR do and why?

Historical context

  1. 1dd0d56f introduces Protected Environments. It has a feature which allows you to easily select unprotected environment from the list of environments. To do this it adds a ProtectedEnvironments::SearchService which simply uses like query. It also adds index: CREATE INDEX index_environments_on_name_varchar_pattern_ops ON environments USING btree (name varchar_pattern_ops); Which is strange because index is not scoped per project. But maybe at the time when there were not too many environments, it worked fine.
  2. e8d9df83 adds a search API for environments. It also uses LIKE. It doesn't add a new index, but again maybe the global index worked fine at the time.
  3. a4f87c71 makes this search case insensitive using ILIKE. Even if the previous index was used before, it can't be used after because you can't use ILIKE with btree index.

Problem

We have queries like this:

explain SELECT environments.* FROM environments WHERE environments.project_id = 278964 AND environments.name ILIKE review%
database lab
Time: 6.209 s
  - planning: 4.505 ms
  - execution: 6.205 s
    - I/O read: 5.920 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 534 (~4.20 MiB) from the buffer pool
  - reads: 3791 (~29.60 MiB) from the OS file cache, including disk I/O
  - dirtied: 481 (~3.80 MiB)
  - writes: 0

Follow up issue: Investigate if the index_environments_on_name_v... (#372533 - closed)

Solution

We actually want search to be case insensitive, but can't use LIKE for this. So this MR:

  1. adds an index on project_id, LOWER(name) varchar_pattern_ops
  2. modifies the queries to be where LOWER(name) LIKE 'review%'
explain SELECT "environments".* FROM "environments" WHERE "environments"."project_id" = 278964 AND (LOWER(environments.name) LIKE LOWER('prod') || '%')
database lab
Time: 9.403 ms
  - planning: 0.243 ms
  - execution: 9.160 ms
    - I/O read: 9.058 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2 (~16.00 KiB) from the buffer pool
  - reads: 2 (~16.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Migration

up/down logs
➜  gitlab git:(10754-search-for-environment-name-in-the-environments-overview-page-2) ✗ ./bin/rails db:migrate
main: == 20220901131828 AddEnvironmentsProjectNameLowerPatternOpsIndex: migrating ===
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:environments, "project_id, lower(name) varchar_pattern_ops", {:name=>"index_environments_on_project_name_varchar_pattern_ops", :algorithm=>:concurrently})
main:    -> 0.0104s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:environments, "project_id, lower(name) varchar_pattern_ops", {:name=>"index_environments_on_project_name_varchar_pattern_ops", :algorithm=>:concurrently})
main:    -> 0.0033s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20220901131828 AddEnvironmentsProjectNameLowerPatternOpsIndex: migrated (0.0221s) 

ci: == 20220901131828 AddEnvironmentsProjectNameLowerPatternOpsIndex: migrating ===
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:environments, "project_id, lower(name) varchar_pattern_ops", {:name=>"index_environments_on_project_name_varchar_pattern_ops", :algorithm=>:concurrently})
ci:    -> 0.0054s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0001s
ci: -- add_index(:environments, "project_id, lower(name) varchar_pattern_ops", {:name=>"index_environments_on_project_name_varchar_pattern_ops", :algorithm=>:concurrently})
ci:    -> 0.0074s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
➜  gitlab git:(10754-search-for-environment-name-in-the-environments-overview-page-2) ✗ ./bin/rails db:rollback
rails aborted!
You're using a multiple database application. To use `db:rollback` you must run the namespaced task with a VERSION. Available tasks are db:rollback:main and db:rollback:ci.

Tasks: TOP => db:rollback
(See full trace by running task with --trace)
main

main: == 20220901131828 AddEnvironmentsProjectNameLowerPatternOpsIndex: reverting ===
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:environments)
main:    -> 0.0076s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:environments, {:algorithm=>:concurrently, :name=>"index_environments_on_project_name_varchar_pattern_ops"})
main:    -> 0.0022s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20220901131828 AddEnvironmentsProjectNameLowerPatternOpsIndex: reverted (0.0164s) 

How to set up and validate locally

  1. Go to Deployments -> Environments
  2. Create a few environments for search
  3. Go to Settings -> CI/CD -> Protected Environments and validate that search works
  4. Go to Deployments -> Feature Flags -> New Feature flag and validate that search works there

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

Edited by Vladimir Shushlin

Merge request reports

Loading