Optimize batching for NullifyOrphanRunnerIdOnCiBuilds

What does this MR do and why?

This MR optimizes the batching for the NullifyOrphanRunnerIdOnCiBuilds migration.

This migration could not complete because of timeout errors: #30159 (comment 956410367)

This MR changes the batching to optimize the query.

Related to #30159 (closed)

Screenshots or screen recordings

SQL output of a test case with NullifyOrphanRunnerIdOnCiBuilds#perform(1, 10, :ci_builds, :id, 3, 0)

Before

SELECT "ci_builds"."id"
FROM "ci_builds"
LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
WHERE (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
  AND "ci_builds"."id" BETWEEN 1 AND 10
ORDER BY "ci_builds"."id" ASC, "ci_builds"."type" ASC LIMIT 1;

UPDATE "ci_builds" SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
  SELECT "ci_builds"."id"
  FROM "ci_builds"
  LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
  WHERE (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
    AND "ci_builds"."id" BETWEEN 1 AND 10 AND "ci_builds"."id" >= 1
    AND "ci_builds"."id" < 6
);

UPDATE "ci_builds" SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
  SELECT "ci_builds"."id"
  FROM "ci_builds"
  LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
  WHERE (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
    AND "ci_builds"."id" BETWEEN 1 AND 10 AND "ci_builds"."id" >= 6
    AND "ci_builds"."id" < 10
);

UPDATE "ci_builds" SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
  SELECT "ci_builds"."id"
  FROM "ci_builds"
  LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
  WHERE (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
    AND "ci_builds"."id" BETWEEN 1 AND 10
    AND "ci_builds"."id" >= 10
);

After

SELECT "ci_builds"."id"
FROM "ci_builds"
WHERE "ci_builds"."id" BETWEEN 1 AND 10
ORDER BY "ci_builds"."id" ASC, "ci_builds"."type" ASC
LIMIT 1;

SELECT "ci_builds"."id"
FROM "ci_builds"
WHERE "ci_builds"."id" BETWEEN 1 AND 10 AND "ci_builds"."id" >= 1
ORDER BY "ci_builds"."id" ASC, "ci_builds"."type" ASC
LIMIT 1 OFFSET 3;

UPDATE "ci_builds"
SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
  SELECT "ci_builds"."id"
  FROM "ci_builds"
  LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
  WHERE "ci_builds"."id" BETWEEN 1 AND 10
    AND "ci_builds"."id" >= 1
    AND "ci_builds"."id" < 4
    AND (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
);

SELECT "ci_builds"."id"
FROM "ci_builds"
WHERE "ci_builds"."id" BETWEEN 1 AND 10 AND "ci_builds"."id" >= 4
ORDER BY "ci_builds"."id" ASC, "ci_builds"."type" ASC
LIMIT 1 OFFSET 3;

UPDATE "ci_builds"
SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
  SELECT "ci_builds"."id"
  FROM "ci_builds"
  LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
  WHERE "ci_builds"."id" BETWEEN 1 AND 10
    AND "ci_builds"."id" >= 4
    AND "ci_builds"."id" < 7
    AND (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
);

SELECT "ci_builds"."id"
FROM "ci_builds"
WHERE "ci_builds"."id" BETWEEN 1 AND 10 AND "ci_builds"."id" >= 7
ORDER BY "ci_builds"."id" ASC, "ci_builds"."type" ASC
LIMIT 1 OFFSET 3;

UPDATE "ci_builds"
SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
  SELECT "ci_builds"."id"
  FROM "ci_builds"
  LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
  WHERE "ci_builds"."id" BETWEEN 1 AND 10
    AND "ci_builds"."id" >= 7
    AND "ci_builds"."id" < 10
    AND (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
);

SELECT "ci_builds"."id"
FROM "ci_builds"
WHERE "ci_builds"."id" BETWEEN 1 AND 10 AND "ci_builds"."id" >= 10
ORDER BY "ci_builds"."id" ASC, "ci_builds"."type" ASC
LIMIT 1 OFFSET 3;

UPDATE "ci_builds"
SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
  SELECT "ci_builds"."id"
  FROM "ci_builds"
  LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
  WHERE "ci_builds"."id" BETWEEN 1 AND 10
  AND "ci_builds"."id" >= 10
  AND (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
);

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.