Improve AutocompleteController#users.json performance

What does this MR do?

Using exact matching pattern when query string is shorter than 3 chars to use index.

Using partial matching when query string is longer than or equal to 3 chars (Current current search functionality) .

This improves AutoCompleteController#users.json performance.

Are there points in the code the reviewer needs to double check?

• This MR changes current search functionality only if query string is shorter than 3 chars.
• SQL performance

Why was this MR needed?

It seems that trigram indexes is not used when query string is shorter than 3 chars.

Followings are query plans of User#search

Partial pattern matching with 2 chars

[1] pry(main)> User.search('ad').explain User Load (22.0ms) SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE '%ad%' OR "users"."email" ILIKE '%ad%') OR "users"."username" ILIKE '%ad%') ORDER BY CASE WHEN users.name = 'ad' THEN 0 WHEN users.username = 'ad' THEN 1 WHEN users.email = 'ad' THEN 2 ELSE 3 END , "users"."name" ASC => EXPLAIN for: SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE '%ad%' OR "users"."email" ILIKE '%ad%') OR "users"."username" ILIKE '%ad%') ORDER BY CASE WHEN users.name = 'ad' THEN 0 WHEN users.username = 'ad' THEN 1 WHEN users.email = 'ad' THEN 2 ELSE 3 END , "users"."name" ASC QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=189.37..191.01 rows=656 width=871) Sort Key: (CASE WHEN ((name)::text = 'ad'::text) THEN 0 WHEN ((username)::text = 'ad'::text) THEN 1 WHEN ((email)::text = 'ad'::text) THEN 2 ELSE 3 END), name -> Seq Scan on users (cost=0.00..158.68 rows=656 width=871) Filter: (((name)::text ~~* '%ad%'::text) OR ((email)::text ~~* '%ad%'::text) OR ((username)::text ~~* '%ad%'::text)) (4 rows)

Partial pattern matching with 3 chars

[5] pry(main)> User.search('adm').explain User Load (1.3ms) SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE '%adm%' OR "users"."email" ILIKE '%adm%') OR "users"."username" ILIKE '%adm%') ORDER BY CASE WHEN users.name = 'adm' THEN 0 WHEN users.username = 'adm' THEN 1 WHEN users.email = 'adm' THEN 2 ELSE 3 END , "users"."name" ASC => EXPLAIN for: SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE '%adm%' OR "users"."email" ILIKE '%adm%') OR "users"."username" ILIKE '%adm%') ORDER BY CASE WHEN users.name = 'adm' THEN 0 WHEN users.username = 'adm' THEN 1 WHEN users.email = 'adm' THEN 2 ELSE 3 END , "users"."name" ASC QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=52.04..52.05 rows=1 width=871) Sort Key: (CASE WHEN ((name)::text = 'adm'::text) THEN 0 WHEN ((username)::text = 'adm'::text) THEN 1 WHEN ((email)::text = 'adm'::text) THEN 2 ELSE 3 END), name -> Bitmap Heap Scan on users (cost=48.01..52.03 rows=1 width=871) Recheck Cond: (((name)::text ~~* '%adm%'::text) OR ((email)::text ~~* '%adm%'::text) OR ((username)::text ~~* '%adm%'::text)) -> BitmapOr (cost=48.01..48.01 rows=1 width=0) -> Bitmap Index Scan on index_users_on_name_trigram (cost=0.00..16.00 rows=1 width=0) Index Cond: ((name)::text ~~* '%adm%'::text) -> Bitmap Index Scan on index_users_on_email_trigram (cost=0.00..16.00 rows=1 width=0) Index Cond: ((email)::text ~~* '%adm%'::text) -> Bitmap Index Scan on index_users_on_username_trigram (cost=0.00..16.00 rows=1 width=0) Index Cond: ((username)::text ~~* '%adm%'::text) (11 rows)

Begin with pattern matching with 2 chars

[2] pry(main)> User.search('ad').explain User Load (1.0ms) SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE 'ad%' OR "users"."email" ILIKE 'ad%') OR "users"."username" ILIKE 'ad%') ORDER BY CASE WHEN users.name = 'ad' THEN 0 WHEN users.username = 'ad' THEN 1 WHEN users.email = 'ad' THEN 2 ELSE 3 END , "users"."name" ASC => EXPLAIN for: SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE 'ad%' OR "users"."email" ILIKE 'ad%') OR "users"."username" ILIKE 'ad%') ORDER BY CASE WHEN users.name = 'ad' THEN 0 WHEN users.username = 'ad' THEN 1 WHEN users.email = 'ad' THEN 2 ELSE 3 END , "users"."name" ASC QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=76.04..76.05 rows=1 width=871) Sort Key: (CASE WHEN ((name)::text = 'ad'::text) THEN 0 WHEN ((username)::text = 'ad'::text) THEN 1 WHEN ((email)::text = 'ad'::text) THEN 2 ELSE 3 END), name -> Bitmap Heap Scan on users (cost=72.01..76.03 rows=1 width=871) Recheck Cond: (((name)::text ~~* 'ad%'::text) OR ((email)::text ~~* 'ad%'::text) OR ((username)::text ~~* 'ad%'::text)) -> BitmapOr (cost=72.01..72.01 rows=1 width=0) -> Bitmap Index Scan on index_users_on_name_trigram (cost=0.00..24.00 rows=1 width=0) Index Cond: ((name)::text ~~* 'ad%'::text) -> Bitmap Index Scan on index_users_on_email_trigram (cost=0.00..24.00 rows=1 width=0) Index Cond: ((email)::text ~~* 'ad%'::text) -> Bitmap Index Scan on index_users_on_username_trigram (cost=0.00..24.00 rows=1 width=0) Index Cond: ((username)::text ~~* 'ad%'::text) (11 rows)

Exact matching pattern with 2 chars

explain analyze SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE 'ad' OR "users"."email" ILIKE 'ad') OR "users"."username" ILIKE 'ad') ORDER BY CASE WHEN users.name = 'ad' THEN 0 WHEN users.username = 'ad' THEN 1 WHEN users.email = 'ad' THEN 2 ELSE 3 END , "users"."name" ASC;

                                                                        QUERY PLAN                                                                            

---

Sort (cost=1393.21..1394.70 rows=597 width=833) (actual time=16.059..16.063 rows=22 loops=1) Sort Key: (CASE WHEN ((name)::text = 'ad'::text) THEN 0 WHEN ((username)::text = 'ad'::text) THEN 1 WHEN ((email)::text = 'ad'::text) THEN 2 ELSE 3 END), name Sort Method: quicksort Memory: 36kB -> Bitmap Heap Scan on users (cost=194.93..1365.68 rows=597 width=833) (actual time=13.983..15.995 rows=22 loops=1) Recheck Cond: (((name)::text ~~* 'ad'::text) OR ((email)::text ~~* 'ad'::text) OR ((username)::text ~~* 'ad'::text)) Rows Removed by Index Recheck: 457 Heap Blocks: exact=477 -> BitmapOr (cost=194.93..194.93 rows=597 width=0) (actual time=13.716..13.716 rows=0 loops=1) -> Bitmap Index Scan on index_users_on_name_trigram (cost=0.00..57.45 rows=193 width=0) (actual time=5.120..5.120 rows=192 loops=1) Index Cond: ((name)::text ~~* 'ad'::text) -> Bitmap Index Scan on index_users_on_email_trigram (cost=0.00..85.51 rows=202 width=0) (actual time=5.176..5.176 rows=269 loops=1) Index Cond: ((email)::text ~~* 'ad'::text) -> Bitmap Index Scan on index_users_on_username_trigram (cost=0.00..51.51 rows=202 width=0) (actual time=3.417..3.417 rows=123 loops=1) Index Cond: ((username)::text ~~* 'ad'::text) Planning time: 0.526 ms Execution time: 16.170 ms

Does this MR meet the acceptance criteria?

• Changelog entry added, if necessary
• Documentation created/updated
• API support added
• Tests added for this feature/bug
• Review
  • Has been reviewed by UX
  • Has been reviewed by Frontend
  • Has been reviewed by Backend
  • Has been reviewed by Database
• Conform by the merge request performance guides
• Conform by the style guides
• Squashed related commits together

What are the relevant issue numbers?

Refs #34535 (closed)

Closes #36879 (closed)