Queries to improve/cache to reduce LWLock contention

Created as a deliverable of #427770 (closed)

Queries to improve/remove/cache:

fingerprint	query	table_name	call_rate (p/m)
19e02409cffcde6e	SELECT "ci_pipelines"."id" FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = $1 AND "ci_pipelines"."created_at" > $2 ORDER BY "ci_pipelines"."status" ASC, "ci_pipelines"."created_at" ASC LIMIT $4 OFFSET $3 /*application:test,correlation_id:01HJ3VH2FC47M66DZ4CX9ZMP1B,endpoint_id:Ci::CancelRedundantPipelinesWorker,db_config_name:ci,line:/app/services/ci/pipeline_creation/cancel_redundant_pipelines_service.rb:45:in `block (2 levels) in paginator'*/	ci_pipelines	192.55
3973d07508d34a9f	WITH RECURSIVE "base_and_descendants" AS ( ( SELECT "ci_pipelines"."id", "ci_pipelines"."ref", "ci_pipelines"."sha", "ci_pipelines"."before_sha", "ci_pipelines"."created_at", "ci_pipelines"."updated_at", "ci_pipelines"."tag", "ci_pipelines"."yaml_errors", "ci_pipelines"."committed_at", "ci_pipelines"."project_id", "ci_pipelines"."status", "ci_pipelines"."started_at", "ci_pipelines"."finished_at", "ci_pipelines"."duration", "ci_pipelines"."user_id", "ci_pipelines"."lock_version", "ci_pipelines"."pipeline_schedule_id", "ci_pipelines"."source", "ci_pipelines"."config_source", "ci_pipelines"."protected", "ci_pipelines"."failure_reason", "ci_pipelines"."iid", "ci_pipelines"."merge_request_id", "ci_pipelines"."source_sha", "ci_pipelines"."target_sha", "ci_pipelines"."external_pull_request_id", "ci_pipelines"."ci_ref_id", "ci_pipelines"."locked", "ci_pipelines"."partition_id", "ci_pipelines"."auto_canceled_by_id" FROM "ci_pipelines" ... /application:test,correlation_id:01HJ3VH2FC47M66DZ4CX9ZMP1B,endpoint_id:Ci::CancelRedundantPipelinesWorker,db_config_name:ci,line:/app/services/ci/pipeline_creation/cancel_redundant_pipelines_service.rb:26:in `block (2 levels) in execute'/	ci_pipelines	123.48
a794dae2d79fe36c	SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."source_project_id" = $1 AND "merge_requests"."source_branch" = $2 AND (EXISTS (SELECT $3 FROM "merge_request_diffs" INNER JOIN "merge_request_diff_commits" ON "merge_request_diff_commits"."merge_request_diff_id" = "merge_request_diffs"."id" WHERE (merge_requests.latest_merge_request_diff_id = merge_request_diffs.id) AND "merge_request_diff_commits"."sha" = $4)) /*application:test,correlation_id:7cb2ff681408755fb38fb3a95c6a5ad4,db_config_name:main,line:/app/models/ci/pipeline.rb:315:in `block (3 levels) in '*/	merge_requests	241.59
a6a29dcb0a2b0700	SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."source_project_id" = $1 AND "merge_requests"."source_branch" = $2 AND ("merge_requests"."state_id" IN ($3)) ORDER BY "merge_requests"."id" DESC LIMIT $4 /*application:test,correlation_id:49e0b8b286aadb22d674f99ce05d7ec5,db_config_name:main,line:/app/models/ci/pipeline.rb:965:in `block in open_merge_requests_refs'*/	merge_requests	201.59
f910611da0bb2d43	SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", ... FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 /*application:test,correlation_id:2321078f17f1dede476543276c9bb56b,db_config_name:main,line:/app/models/event.rb:360:in `reset_project_activity'*/	projects	2627.73
135e2d0e575a5294	SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", ... FROM "projects" WHERE "projects"."id" = $1 /*application:test,correlation_id:7ae1a8794a0c560eb6b1556714fdacf4,endpoint_id:Ci::ArchiveTraceWorker,db_config_name:main,line:/app/workers/ci/archive_trace_worker.rb:15:in `perform'*/	projects	860.91
6ab9bdf40bce21f6	SELECT "deployments"."id", "deployments"."iid", "deployments"."project_id", "deployments"."environment_id", "deployments"."ref", "deployments"."tag", "deployments"."sha", "deployments"."user_id", "deployments"."deployable_type", "deployments"."created_at", "deployments"."updated_at", "deployments"."on_stop", "deployments"."status", "deployments"."finished_at", "deployments"."deployable_id", "deployments"."archived" FROM "deployments" WHERE "deployments"."deployable_id" = $1 AND "deployments"."deployable_type" = $2 LIMIT $3 /*application:test,correlation_id:01HJ3WDG4MX2Y0HSKP57H3X36K,endpoint_id:GraphqlController#execute,db_config_name:main,line:/ee/app/models/concerns/ee/ci/deployable.rb:17:in `waiting_for_deployment_approval?'*/	deployments	449.97
ff294a87ec5a9c64	SELECT $1 AS one FROM "requirements" INNER JOIN "issues" ON "issues"."id" = "requirements"."issue_id" WHERE "requirements"."project_id" = $2 AND "issues"."state_id" = $3 LIMIT $4 /*application:test,correlation_id:87287a35eb17e1ad950e60dc260d0e3a,db_config_name:main,line:/ee/app/models/ee/project.rb:1293:in `block (2 levels) in requirements_ci_variables'*/	issues	208.48
82d66501e69c81b9	SELECT $1 AS one FROM "jira_connect_subscriptions" WHERE "jira_connect_subscriptions"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $2 AND "namespaces"."id" = $3) LIMIT $4 /*application:test,correlation_id:6b08b3388458bf56b801bb5f767abdf8,db_config_name:main,line:/app/models/project.rb:2821:in `jira_subscription_exists?'*/	namespaces	145.71
0147fbeed044c6a7	SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 /*application:test,correlation_id:9cb18086a3de174fc2be76065a6d48da,db_config_name:main,line:/app/models/group.rb:338:in `get_ids_by_ids_or_paths'*/	namespaces	648.97

Deliverables:

• Add a new comment for each fingerprint and capture the known information. Tag people/team who can help to make a decision.
• Create a separate issues to improve/remove/cache the query (fingerprint) in &11660.