Optimize User#ci_owned_runners query (!79729) · Merge requests · GitLab.org / GitLab

Furkan Ayhan requested to merge 336436-change-db-index-for-ci_owned_runners into master Feb 02, 2022

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;

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.

Edited Feb 07, 2022 by Furkan Ayhan

Optimize User#ci_owned_runners query

What does this MR do and why?

Database

MR acceptance checklist

Merge request reports