Optimize User#ci_owned_runners query
What does this MR do and why?
This MR optimizes the query from User#ci_owned_runners
by moving a subquery into a CTE and letting it use the existing index
with a smarter query plan.
- Original issue: #336436
- Timeout: #351460 (comment 828379528)
- FF: #350322 (comment 829057990)
Database
An example of timeout log: https://log.gprd.gitlab.net/app/discover#/doc/7092c4e2-4eb5-46f2-8305-a7da2edad090/pubsub-rails-inf-gprd-008176?id=8MpBtn4Br5CsQdLeQUMZ
The user gitlab-qa
/ 1614863
has many groups (~6k), projects (~3k) => and runners;
SELECT "members"."source_id"
FROM "members"
WHERE "members"."source_type" = 'Project'
AND "members"."type" = 'ProjectMember'
AND "members"."user_id" = 1614863
AND "members"."requested_at" IS NULL
AND (access_level >= 40);
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8382/commands/29545
SELECT "members"."source_id"
FROM "members"
WHERE "members"."source_type" = 'Namespace'
AND "members"."type" = 'GroupMember'
AND "members"."user_id" = 1614863
AND "members"."requested_at" IS NULL
AND (access_level >= 10)
AND (access_level >= 40);
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8382/commands/29547
SELECT "members"."source_id"
FROM "members"
WHERE "members"."source_type" = 'Namespace'
AND "members"."type" = 'GroupMember'
AND "members"."user_id" = 1614863
AND "members"."requested_at" IS NULL
AND (access_level >= 10)
AND (access_level >= 50);
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8382/commands/29549
explain SELECT "ci_runners".*
FROM (
(
SELECT ci_runners.*
FROM "ci_runner_projects"
INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
WHERE "ci_runner_projects"."project_id" IN (...~3k integer...)
)
UNION
(
SELECT ci_runners.*
FROM "ci_runner_projects"
INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
JOIN ci_project_mirrors ON ci_project_mirrors.project_id = ci_runner_projects.project_id
JOIN ci_namespace_mirrors ON ci_namespace_mirrors.namespace_id = ci_project_mirrors.namespace_id
WHERE (traversal_ids && ARRAY[...~6k integer...]::int[])
)
UNION
(
SELECT ci_runners.*
FROM "ci_runner_namespaces"
INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_namespaces"."runner_id"
JOIN ci_namespace_mirrors ON ci_namespace_mirrors.namespace_id = ci_runner_namespaces.namespace_id
WHERE (traversal_ids && ARRAY[...~6k integer...]::int[])
)
) ci_runners
LIMIT 20
OFFSET 0;
- cold: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8382/commands/29550
- hot: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8382/commands/29551
after;
VACUUM ANALYZE ci_runners;
VACUUM ANALYZE ci_runner_namespaces;
VACUUM ANALYZE ci_runner_projects;
result: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8382/commands/29555
So...
The slowest query is the last UNION;
SELECT ci_runners.*
FROM "ci_runner_namespaces"
INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_namespaces"."runner_id"
JOIN ci_namespace_mirrors ON ci_namespace_mirrors.namespace_id = ci_runner_namespaces.namespace_id
WHERE (traversal_ids && ARRAY[...~6k integer...]::int[]);
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8451/commands/29928
We may try to join with ci_namespace_mirrors.traversal_ids[array_length(ci_namespace_mirrors.traversal_ids, 1)]
instead of ci_namespace_mirrors.namespace_id
to use more efficient index.
SELECT ci_runners.*
FROM "ci_runner_namespaces"
INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_namespaces"."runner_id"
JOIN ci_namespace_mirrors ON ci_namespace_mirrors.traversal_ids[array_length(ci_namespace_mirrors.traversal_ids, 1)] = ci_runner_namespaces.namespace_id
WHERE (traversal_ids && ARRAY[...~6k integer...]::int[]);
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8451/commands/29930
still not smart enough...
Let's move this logic to CTE; (huge thanks to @ahegyi)
WITH cte_namespace_ids AS MATERIALIZED (
SELECT ci_namespace_mirrors.traversal_ids[array_length(ci_namespace_mirrors.traversal_ids, 1)] AS namespace_id
FROM ci_namespace_mirrors
WHERE (traversal_ids && ARRAY[...~6k integer...]::int[])
)
SELECT ci_runners.*
FROM ci_runners
INNER JOIN ci_runner_namespaces ON ci_runner_namespaces.runner_id = ci_runners.id
WHERE ci_runner_namespaces.namespace_id IN (SELECT namespace_id FROM cte_namespace_ids);
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8451/commands/29934
yay
Let's use it in our main query;
explain SELECT "ci_runners".*
FROM (
(
SELECT ci_runners.*
FROM "ci_runner_projects"
INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
WHERE "ci_runner_projects"."project_id" IN (...~3k integer...)
)
UNION
(
SELECT ci_runners.*
FROM "ci_runner_projects"
INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
JOIN ci_project_mirrors ON ci_project_mirrors.project_id = ci_runner_projects.project_id
JOIN ci_namespace_mirrors ON ci_namespace_mirrors.namespace_id = ci_project_mirrors.namespace_id
WHERE (traversal_ids && ARRAY[...~6k integer...]::int[])
)
UNION
(
WITH cte_namespace_ids AS MATERIALIZED (
SELECT ci_namespace_mirrors.traversal_ids[array_length(ci_namespace_mirrors.traversal_ids, 1)] AS namespace_id
FROM ci_namespace_mirrors
WHERE (traversal_ids && ARRAY[...~6k integer...]::int[])
)
SELECT ci_runners.*
FROM ci_runners
INNER JOIN ci_runner_namespaces ON ci_runner_namespaces.runner_id = ci_runners.id
WHERE ci_runner_namespaces.namespace_id IN (SELECT namespace_id FROM cte_namespace_ids)
)
) ci_runners
LIMIT 20
OFFSET 0;
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8451/commands/29936
yay
However, we can also improve the 2nd UNION query even if it performs okay today.
From this;
SELECT ci_runners.*
FROM ci_runner_projects
INNER JOIN ci_runners ON ci_runners.id = ci_runner_projects.runner_id
JOIN ci_project_mirrors ON ci_project_mirrors.project_id = ci_runner_projects.project_id
JOIN ci_namespace_mirrors ON ci_namespace_mirrors.namespace_id = ci_project_mirrors.namespace_id
WHERE (traversal_ids && ARRAY[...~6k integer...]::int[])
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8463/commands/30020
To this;
WITH cte_project_ids AS MATERIALIZED (
SELECT ci_project_mirrors.project_id
FROM ci_project_mirrors
JOIN ci_namespace_mirrors ON ci_namespace_mirrors.traversal_ids[array_length(ci_namespace_mirrors.traversal_ids, 1)] = ci_project_mirrors.namespace_id
WHERE (traversal_ids && ARRAY[...~6k integer...]::int[])
)
SELECT ci_runners.*
FROM ci_runners
INNER JOIN ci_runner_projects ON ci_runners.id = ci_runner_projects.runner_id
WHERE ci_runner_projects.project_id IN (SELECT project_id FROM cte_project_ids);
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8463/commands/30022
Now the final result...
explain SELECT "ci_runners".*
FROM (
(
SELECT ci_runners.*
FROM "ci_runner_projects"
INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
WHERE "ci_runner_projects"."project_id" IN (...~3k integer...)
)
UNION
(
WITH cte_project_ids AS MATERIALIZED (
SELECT ci_project_mirrors.project_id
FROM ci_project_mirrors
JOIN ci_namespace_mirrors ON ci_namespace_mirrors.traversal_ids[array_length(ci_namespace_mirrors.traversal_ids, 1)] = ci_project_mirrors.namespace_id
WHERE (traversal_ids && ARRAY[...~6k integer...]::int[])
)
SELECT ci_runners.*
FROM ci_runners
INNER JOIN ci_runner_projects ON ci_runners.id = ci_runner_projects.runner_id
WHERE ci_runner_projects.project_id IN (SELECT project_id FROM cte_project_ids);
)
UNION
(
WITH cte_namespace_ids AS MATERIALIZED (
SELECT ci_namespace_mirrors.traversal_ids[array_length(ci_namespace_mirrors.traversal_ids, 1)] AS namespace_id
FROM ci_namespace_mirrors
WHERE (traversal_ids && ARRAY[...~6k integer...]::int[])
)
SELECT ci_runners.*
FROM ci_runners
INNER JOIN ci_runner_namespaces ON ci_runner_namespaces.runner_id = ci_runners.id
WHERE ci_runner_namespaces.namespace_id IN (SELECT namespace_id FROM cte_namespace_ids)
)
) ci_runners
LIMIT 20
OFFSET 0;
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8463/commands/30023
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.