Skip to content

Optimize indices for issues table

username-removed-423915 requested to merge godfat/gitlab-ce:improve-issues-table-performance into master

As discussed at: gitlab-com/operations#298 (comment 12134627)

Reasons to remove:

  • confidential index: The query from IssuesFinder is 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 acts_as_paranoid on Issue, most of the queries for "issues" would have an additional condition: "issues"."deleted_at" IS NULL, therefore indices on "issues" should probably also combine with "issues"."deleted_at". For slow queries like:

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

Merge request reports