As discussed at: gitlab-com/operations#298 (comment 12134627)
Reasons to remove:
confidential index: The query from
IssuesFinderis pretty complex and it would not use condition along. We would always need to also checkout if the viewer has privilege anyway.
state index: The same reason as confidential.
title index: Do we really need this? We already have
index_issues_on_title_trigram. Or is this for MySQL?
Since we're using
paranoia gem and
of the queries for
"issues" would have an additional condition:
"issues"."deleted_at" IS NULL, therefore indices on
probably also combine with
"issues"."deleted_at". For slow queries
SELECT "issues".* FROM "issues" WHERE "issues"."deleted_at" IS NULL AND "issues"."project_id" = $1 AND ("issues"."state" IN ('closed')) AND (iid < $2) ORDER BY "issues"."id" DESC LIMIT 1
Adding this index would help:
CREATE INDEX index_issues_on_project_id_and_deleted_at ON issues (project_id, deleted_at);
Explain would use it:
[...] QUERY PLAN | Index Cond: ((project_id = 1) AND (deleted_at IS NULL)) [...]
Since we already have a ton of indices on
"issues", we might also want
to check if we could remove some of them or update to combine with
"issues"."deleted_at" as well?
/cc @stanhu @pcarranza @DouweM