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 🤖