Query Performance Data - Query ID 5969303245325571000 (projects, project_mirror_data, and plans tables)

Description

This query is listed as a top 50 queries by total time in this report https://console.postgres.ai/gitlab/gitlab_production/reports/425/files/113266/md#postgres-checkup_K003

-- queryid: 5969303245325571000
-- NOTICE: the first 50k characters
-- NOTICE: current query size (bytes): '1358'
/*application:sidekiq,correlation_id:f8260577606cc4f3ff1a0dd43077b5d9,jid:e380732feee9f221ecb6029d,job_class:UpdateAllMirrorsWorker,endpoint_id:UpdateAllMirrorsWorker*/ 
SELECT "projects".* FROM "projects" 
INNER JOIN project_mirror_data import_state ON import_state.project_id = projects.id 
INNER JOIN namespaces AS root_namespaces ON root_namespaces.id = 
(WITH RECURSIVE "base_and_ancestors" AS ((SELECT "namespaces".* FROM "namespaces" WHERE (id = projects.namespace_id))
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."id" = "base_and_ancestors"."parent_id")) 
SELECT "id" FROM "base_and_ancestors" AS "namespaces" WHERE "namespaces"."parent_id" IS NULL) 
LEFT JOIN gitlab_subscriptions ON gitlab_subscriptions.namespace_id = root_namespaces.id 
LEFT JOIN plans ON plans.id = gitlab_subscriptions.hosted_plan_id 
WHERE "projects"."archived" = FALSE AND "projects"."pending_delete" = FALSE 
AND "projects"."mirror" = TRUE 
AND "import_state"."status" NOT IN ('scheduled', 'started') 
AND (import_state.next_execution_timestamp <= '2021-03-21 10:08:02.398442') 
AND (import_state.retry_count <= 14) AND (import_state.next_execution_timestamp > '2020-03-28 00:00:00') 
AND (plans.name IN ('bronze','silver','premium','gold','ultimate') 
OR projects.visibility_level = 20) ORDER BY import_state.next_execution_timestamp LIMIT 500

​

Data points

Calls

• 919
• 0.48/sec
• 1.00/call
• 0.01%

Total Time

• 252,827.12 ms
• 133.109 ms/sec
• 275.111 ms/call
• 13.06%

https://log.gprd.gitlab.net/goto/68015d53c6c776fe992a451a9abe5adc

Problem

UpdateAllMirrorsWorker does a SELECT query with too many JOINs:

• it lists all projects where pull mirroring is enable, using projects and project_mirror_data
• and since pull mirroring is a premium feature, it excludes private projects that aren't connected to a pain plan, using namespaces, subscriptions, and plans

Proposal

Deactivate mirrors that do not have the correct license, as proposed in #216783 (closed). This removes the need to check the subscription and the plan when listing all projects where pull mirroring as been set up, as this is currently the case in UpdateAllMirrorsWorker.