Remove cross joins for ProjectMonthlyUsage.for_namespace_monthly_usage
Ref: #343301 (closed)
What does this MR do and why?
This MR removes a cross joins query for Ci::Minutes::ProjectMonthlyUsage.for_namespace_monthly_usage
.
This change is behind a feature flag : ci_decompose_for_namespace_monthly_usage_query
.
Database review
Old SQL Query
SELECT
ci_project_monthly_usages.*
FROM
ci_project_monthly_usages
WHERE
ci_project_monthly_usages.date = '2021-05-01'
AND ci_project_monthly_usages.project_id IN (
SELECT
projects.id
FROM
projects
WHERE
projects.namespace_id = 278964)
Run the following commands to backfill our new ci mirror tables which will happen in !76327 (merged).
exec INSERT INTO ci_project_mirrors (project_id, namespace_id)
SELECT "projects"."id", "projects"."namespace_id" FROM "projects"
ON CONFLICT (project_id) DO NOTHING;
exec INSERT INTO ci_namespace_mirrors (namespace_id, traversal_ids)
SELECT "namespaces"."id", "namespaces"."traversal_ids" FROM "namespaces"
ON CONFLICT (namespace_id) DO NOTHING;
exec ANALYZE ci_project_mirrors;
exec ANALYZE ci_namespace_mirrors;
New SQL Query
SELECT
ci_project_monthly_usages.*
FROM
ci_project_monthly_usages
WHERE
ci_project_monthly_usages.date = '2021-05-01'
AND ci_project_monthly_usages.project_id IN (
SELECT
ci_project_mirrors.project_id
FROM
ci_project_mirrors
WHERE
ci_project_mirrors.namespace_id = 278964);
Other SQL Query considered (not retained)
SELECT
ci_project_monthly_usages.*
FROM
ci_project_monthly_usages
WHERE
ci_project_monthly_usages.date = '2021-05-01'
AND ci_project_monthly_usages.project_id IN (98024, 116212, 140724, 143237, 145205, 150440, 227582, 250324, 250833, 278964, 280425, 375711, 387896, 413007, 430285, 443787, 444821, 455030, 480929, 554859, 593728, 629054, 629060, 684698, 730448, 734943, 747741, 766015, 818896, 876090, 887372, 928825, 931715, 998792, 1075790, 1120019, 1209837, 1265999, 1329047, 1379171, 1441932, 1470839, 1507906, 1533158, 1777822, 1794617, 1911766, 1990920, 2009901, 2127625, 2317465, 2337675, 2347063, 2383700, 2651596, 2670515, 2694799, 2725567, 2890326, 2953390, 3010986, 3010998, 3094319, 3101096, 3305972, 3362933, 3466815, 3588247, 3605985, 3631141, 3651684, 3662568, 3662668, 3674569, 3698388, 3871132, 3871556, 3885956, 3885980, 3933206, 3933372, 3991945)
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Max Orefice