Issue creation is slow because it tries to update project counter cache

Summary

Issue creation has become very slow and even fails with a statement timeout (ActiveRecord::QueryCanceled exception) for large projects.

The reason is that the update_project_counter_caches callback executes an expensive query to count issues of specific types.

The query is:

SELECT COUNT(*) AS count_all, "issues"."confidential" AS issues_confidential FROM "issues" WHERE ("issues"."state_id" IN (1)) AND "issues"."issue_type" IN (0, 1) AND "issues"."project_id" = $1 GROUP BY "issues"."confidential";

Indexes on Issues table:
    "issues_pkey" PRIMARY KEY, btree (id)
    "idx_issues_on_health_status_not_null" btree (health_status) WHERE health_status IS NOT NULL
    "idx_issues_on_project_id_and_created_at_and_id_and_state_id" btree (project_id, created_at, id, state_id)
    "idx_issues_on_project_id_and_due_date_and_id_and_state_id" btree (project_id, due_date, id, state_id) WHERE due_date IS NOT NULL
    "idx_issues_on_project_id_and_rel_position_and_id_and_state_id" btree (project_id, relative_position, id, state_id)
    "idx_issues_on_project_id_and_updated_at_and_id_and_state_id" btree (project_id, updated_at, id, state_id)
    "idx_issues_on_state_id" btree (state_id)
    "index_issue_on_project_id_state_id_and_blocking_issues_count" btree (project_id, state_id, blocking_issues_count)
    "index_issues_on_author_id" btree (author_id)
    "index_issues_on_author_id_and_id_and_created_at" btree (author_id, id, created_at)
    "index_issues_on_closed_by_id" btree (closed_by_id)
    "index_issues_on_confidential" btree (confidential)
    "index_issues_on_description_trigram" gin (description gin_trgm_ops)
    "index_issues_on_duplicated_to_id" btree (duplicated_to_id) WHERE duplicated_to_id IS NOT NULL
    "index_issues_on_id_and_weight" btree (id, weight)
    "index_issues_on_incident_issue_type" btree (issue_type) WHERE issue_type = 1
    "index_issues_on_last_edited_by_id" btree (last_edited_by_id)
    "index_issues_on_milestone_id" btree (milestone_id)
    "index_issues_on_moved_to_id" btree (moved_to_id) WHERE moved_to_id IS NOT NULL
    "index_issues_on_project_id_and_closed_at" btree (project_id, closed_at)
    "index_issues_on_project_id_and_created_at_issue_type_incident" btree (project_id, created_at) WHERE issue_type = 1
    "index_issues_on_project_id_and_external_key" UNIQUE, btree (project_id, external_key) WHERE external_key IS NOT NULL
    "index_issues_on_project_id_and_iid" UNIQUE, btree (project_id, iid)
    "index_issues_on_project_id_and_state_id_and_created_at_and_id" btree (project_id, state_id, created_at, id)
    "index_issues_on_project_id_and_upvotes_count" btree (project_id, upvotes_count)
    "index_issues_on_promoted_to_epic_id" btree (promoted_to_epic_id) WHERE promoted_to_epic_id IS NOT NULL
    "index_issues_on_sprint_id" btree (sprint_id)
    "index_issues_on_title_trigram" gin (title gin_trgm_ops)
    "index_issues_on_updated_at" btree (updated_at)
    "index_issues_on_updated_by_id" btree (updated_by_id) WHERE updated_by_id IS NOT NULL
    "index_on_issues_closed_incidents_by_project_id_and_closed_at" btree (project_id, closed_at) WHERE issue_type = 1 AND state_id = 2
    "tmp_index_issues_on_issue_type_and_id" btree (issue_type, id)

See gitlab-com/gl-infra/production#7142 (closed)

Extra data

This affects all issue creation vectors; UI, API and Email. However email-related errors are visible in this Kibana chart:

https://log.gprd.gitlab.net/goto/14455380-dd07-11ec-8741-ad075583b944

Possible fixes

• Add an index for issue type or use an existing index that improves performance, or
• Make updates to the project issue counter cache async (though these will likely also fail)