Add trigram index on snippet description
What does this MR do?
Adds a trigram index on snippets.description, as we plan to expand search to cover that column.
This is similar to earlier MR !17806 (merged), indexing content column for text search.
Query plan
SELECT "snippets".* FROM ((SELECT "snippets".* FROM "snippets" WHERE ("snippets"."visibility_level" IN (0, 10, 20) OR "snippets"."author_id" = 1) AND "snippets"."project_id" IS NULL)
UNION
(SELECT "snippets".* FROM "snippets" INNER JOIN "projects" ON "projects"."id" = "snippets"."project_id" INNER JOIN "project_features" ON "project_features"."project_id" = "projects"."id" WHERE ("snippets"."visibility_level" IN (0, 10, 20) OR "snippets"."author_id" = 1) AND (projects.visibility_level IN (0,10,20)) AND "project_features"."snippets_access_level" IN (20, 30))
UNION
(SELECT "snippets".* FROM "snippets" INNER JOIN "projects" ON "projects"."id" = "snippets"."project_id" INNER JOIN "project_features" ON "project_features"."project_id" = "projects"."id" WHERE "project_features"."snippets_access_level" IN (20, 30, 10) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE (project_id = snippets.project_id) AND "project_authorizations"."user_id" = 1)))) snippets WHERE (("snippets"."title" ILIKE '%public%' OR "snippets"."description" ILIKE '%public%') OR "snippets"."file_name" ILIKE '%public%') ORDER BY "snippets"."updated_at" DESC LIMIT 20 OFFSET 0
Limit (cost=56.28..56.30 rows=7 width=1325)
-> Sort (cost=56.28..56.30 rows=7 width=1325)
Sort Key: snippets.updated_at DESC
-> HashAggregate (cost=56.04..56.11 rows=7 width=1325)
Group Key: snippets.id, snippets.title, snippets.content, snippets.author_id, snippets.project_id, snippets.created_at, snippets.updated_at, snippets.file_name, snippets.type, snippets.visibility_level, snippets.title_html, snippets.content_html, snippets.cached_markdown_version, snippets.description, snippets.description_html, snippets.encrypted_secret_token, snippets.encrypted_secret_token_iv, snippets.secret
-> Append (cost=17.03..55.73 rows=7 width=1325)
-> Bitmap Heap Scan on snippets (cost=17.03..21.13 rows=3 width=2646)
Recheck Cond: (((title)::text ~~* '%public%'::text) OR (description ~~* '%public%'::text) OR ((file_name)::text ~~* '%public%'::text))
Filter: ((project_id IS NULL) AND ((visibility_level = ANY ('{0,10,20}'::integer[])) OR (author_id = 1)))
-> BitmapOr (cost=17.03..17.03 rows=4 width=0)
-> Bitmap Index Scan on index_snippets_on_title_trigram (cost=0.00..6.02 rows=2 width=0)
Index Cond: ((title)::text ~~* '%public%'::text)
-> Bitmap Index Scan on index_snippets_on_description_trigram (cost=0.00..5.00 rows=1 width=0)
Index Cond: (description ~~* '%public%'::text)
-> Bitmap Index Scan on index_snippets_on_file_name_trigram (cost=0.00..6.01 rows=2 width=0)
Index Cond: ((file_name)::text ~~* '%public%'::text)
-> Nested Loop (cost=21.86..22.94 rows=3 width=2646)
Join Filter: (snippets_1.project_id = projects.id)
-> Merge Join (cost=21.44..22.45 rows=1 width=2650)
Merge Cond: (project_features.project_id = snippets_1.project_id)
-> Index Scan using index_project_features_on_project_id on project_features (cost=0.29..3130.15 rows=100106 width=4)
Filter: (snippets_access_level = ANY ('{20,30}'::integer[]))
-> Sort (cost=21.15..21.16 rows=3 width=2646)
Sort Key: snippets_1.project_id
-> Bitmap Heap Scan on snippets snippets_1 (cost=17.03..21.13 rows=3 width=2646)
Recheck Cond: (((title)::text ~~* '%public%'::text) OR (description ~~* '%public%'::text) OR ((file_name)::text ~~* '%public%'::text))
Filter: ((visibility_level = ANY ('{0,10,20}'::integer[])) OR (author_id = 1))
-> BitmapOr (cost=17.03..17.03 rows=4 width=0)
-> Bitmap Index Scan on index_snippets_on_title_trigram (cost=0.00..6.02 rows=2 width=0)
Index Cond: ((title)::text ~~* '%public%'::text)
-> Bitmap Index Scan on index_snippets_on_description_trigram (cost=0.00..5.00 rows=1 width=0)
Index Cond: (description ~~* '%public%'::text)
-> Bitmap Index Scan on index_snippets_on_file_name_trigram (cost=0.00..6.01 rows=2 width=0)
Index Cond: ((file_name)::text ~~* '%public%'::text)
-> Index Scan using projects_pkey on projects (cost=0.42..0.47 rows=1 width=4)
Index Cond: (id = project_features.project_id)
Filter: (visibility_level = ANY ('{0,10,20}'::integer[]))
-> Nested Loop (cost=2.81..11.59 rows=1 width=2646)
-> Merge Join (cost=2.51..11.26 rows=1 width=2654)
Merge Cond: (snippets_2.project_id = projects_1.id)
-> Merge Semi Join (cost=2.10..10.10 rows=1 width=2650)
Merge Cond: (snippets_2.project_id = project_authorizations.project_id)
-> Index Scan using index_snippets_on_project_id_and_visibility_level on snippets snippets_2 (cost=0.14..28.49 rows=4 width=2646)
Filter: (((title)::text ~~* '%public%'::text) OR (description ~~* '%public%'::text) OR ((file_name)::text ~~* '%public%'::text))
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.14..2.77 rows=36 width=4)
Index Cond: (user_id = 1)
-> Index Only Scan using projects_pkey on projects projects_1 (cost=0.42..2365.08 rows=100044 width=4)
-> Index Scan using index_project_features_on_project_id on project_features project_features_1 (cost=0.29..0.33 rows=1 width=4)
Index Cond: (project_id = projects_1.id)
Filter: (snippets_access_level = ANY ('{20,30,10}'::integer[]))Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry - [-] Documentation (if required)
-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides - [-] Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Related to #199220 (closed)
Edited by 🤖 GitLab Bot 🤖