Skip to content

Allow filtering labels by a single character

Sean McGivern requested to merge allow-filtering-labels-by-a-single-character into master

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?

Merge request reports