Geo: `Project.verified_repos.count` query too slow on GitLab.com
Error
Geo was enabled on GitLab.com, and the primary is unable to update its GeoNodeStatus because of a statement timeout: https://sentry.gitlab.net/gitlab/gitlabcom/issues/672801/?query=is:unresolved%20metricsupdateworker
PG::QueryCanceled: ERROR: canceling statement due to statement timeout : SELECT COUNT(*) FROM "projects" INNER JOIN "project_repository_states" ON "project_repository_states"."project_id" = "projects"."id" WHERE ("project_repository_states"."repository_verification_checksum" IS NOT NULL) AND "project_repository_states"."last_repository_verification_failure" IS NULL
Slack conversation: https://gitlab.slack.com/archives/C32LCGC1H/p1551984812119200
Background
Project.verified_repos.count
SELECT COUNT(*)
FROM "projects"
INNER JOIN "project_repository_states"
ON "project_repository_states"."project_id" = "projects"."id"
WHERE ("project_repository_states"."repository_verification_checksum" IS NOT NULL)
AND "project_repository_states"."last_repository_verification_failure" IS NULL;
This query takes a little over 10s on console-01-sv-gprd
, and returns a count over 8M.
Here is the explain: https://explain.depesz.com/s/TdnS
This index appears to be specifically created for this query, and it is being used.
t.index ["project_id", "last_repository_verification_ran_at"], name: "idx_repository_states_on_last_repository_verification_ran_at", where: "((repository_verification_checksum IS NOT NULL) AND (last_repository_verification_failure IS NULL))", using: :btree
Edited by Michael Kozono