Allow filtering labels by a single character
What does this MR do?
When we use Gitlab::SQL::Pattern
, this typically relates to a trigram
index. As the 'tri' indicates, we need at least three characters to be
able to use that index.
Labels don't have a trigram index, because we never allow you to search for them globally: it's always in the context of a project or a group. In that context, it's just as fast to search for a single character (in general) because there is already a pretty specific index being used.
For example, https://gitlab.com/groups/reproduction-group/-/labels?utf8=%E2%9C%93&subscribed=&search=%3A%3Ap gives this query:
gitlabhq_production=> EXPLAIN ANALYZE SELECT "labels".* FROM "labels" WHERE "labels"."type" IN ('GroupLabel') AND "labels"."group_id" = 721675 AND ("labels"."title" ILIKE '%::p%' OR "labels"."description" ILIKE '%::p%') ORDER BY "labels"."title" ASC LIMIT 20 OFFSET 0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=12.80..12.80 rows=1 width=79) (actual time=0.371..0.371 rows=1 loops=1)
-> Sort (cost=12.80..12.80 rows=1 width=79) (actual time=0.371..0.371 rows=1 loops=1)
Sort Key: title
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_labels_on_group_id_and_project_id_and_title on labels (cost=0.56..12.79 rows=1 width=79) (actual time=0.152..0.344 rows=1 loops=1)
Index Cond: (group_id = 721675)
Filter: (((type)::text = 'GroupLabel'::text) AND (((title)::text ~~* '%::p%'::text) OR ((description)::text ~~* '%::p%'::text)))
Rows Removed by Filter: 6
Planning time: 4.419 ms
Execution time: 0.398 ms
(10 rows)
Filtering by two characters gives:
gitlabhq_production=> EXPLAIN ANALYZE SELECT "labels".* FROM "labels" WHERE "labels"."type" IN ('GroupLabel') AND "labels"."group_id" = 721675 AND ("labels"."title" ILIKE '%::%' OR "labels"."description" ILIKE '%::%') ORDER BY "labels"."title" ASC LIMIT 20 OFFSET 0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=12.80..12.80 rows=1 width=79) (actual time=0.065..0.065 rows=1 loops=1)
-> Sort (cost=12.80..12.80 rows=1 width=79) (actual time=0.065..0.065 rows=1 loops=1)
Sort Key: title
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_labels_on_group_id_and_project_id_and_title on labels (cost=0.56..12.79 rows=1 width=79) (actual time=0.027..0.059 rows=1 loops=1)
Index Cond: (group_id = 721675)
Filter: (((type)::text = 'GroupLabel'::text) AND (((title)::text ~~* '%::%'::text) OR ((description)::text ~~* '%::%'::text)))
Rows Removed by Filter: 6
Planning time: 2.244 ms
Execution time: 0.094 ms
(10 rows)
What are the relevant issue numbers?
Closes https://gitlab.com/gitlab-org/gitlab-ce/issues/58795.
Does this MR meet the acceptance criteria?
-
Changelog entry added, if necessary -
Tests added for this feature/bug -
Conforms to the database guides -
Security reports checked/validated by reviewer