Draft: Optimize User#ci_owned_runners query V2

What does this MR do and why?

This is a new version of !79729 (merged). It was reverted by !80225 (merged) because of a staging QA incident: gitlab-com/gl-infra/production#6315 (closed). The problem is that the user gitlab-qa / 1614863 has a very extraordinary number of groups and projects (~6-7k) while the staging environment does not have many other than those. So, the SQL query plan is not good.

This MR optimizes the query from User#ci_owned_runners by moving subqueries into CTEs and letting them use the existing index with a smarter query plan.

• Original issue: #336436
• Feature flag: #350322 (closed) ci_owned_runners_cross_joins_fix

Database

I already shared the original queries in the previous MR so I'll compare only new ones for staging.

UNION Query 2

Fetching namespace ids for gitlab-qa...

SELECT "members"."source_id"
FROM "members"
WHERE "members"."source_type" = 'Namespace'
  AND "members"."type" = 'GroupMember'
  AND "members"."user_id" = 1614863 -- gitlab-qa
  AND "members"."requested_at" IS NULL
  AND (access_level >= 10)
  AND (access_level >= 40);

Previous attempt

EXPLAIN (BUFFERS, ANALYZE) 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 (ci_namespace_mirrors.traversal_ids && ARRAY[...~6k integer...]::int[])
)
SELECT ci_runners.*
FROM ci_runners
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);

Staging env result;

 Hash Join  (cost=117571.92..118249.20 rows=29058 width=271) (actual time=30208.241..30229.210 rows=98 loops=1)
   Hash Cond: (ci_runner_projects.runner_id = ci_runners.id)
   Buffers: shared hit=49272 read=16346
   I/O Timings: read=273.387
   CTE cte_project_ids
     ->  Gather  (cost=1000.43..104325.10 rows=433480 width=4) (actual time=2.644..30141.609 rows=9596 loops=1)
           Workers Planned: 2
           Workers Launched: 2
           Buffers: shared hit=27949 read=16346
           I/O Timings: read=273.387
           ->  Nested Loop  (cost=0.43..59977.10 rows=180617 width=4) (actual time=0.516..30137.616 rows=3199 loops=3)
                 Buffers: shared hit=27949 read=16346
                 I/O Timings: read=273.387
                 ->  Parallel Seq Scan on ci_namespace_mirrors  (cost=0.00..27391.45 rows=16915 width=25) (actual time=0.287..30122.705 rows=2624 loops=3)
                       Filter: (traversal_ids && '{..6k-integer..}'::integer[])
                       Rows Removed by Filter: 646334
                       Buffers: shared hit=1093 read=16346
                       I/O Timings: read=273.387
                 ->  Index Scan using index_ci_project_mirrors_on_namespace_id on ci_project_mirrors  (cost=0.43..1.82 rows=11 width=8) (actual time=0.004..0.005 rows=1 loops=7873)
                       Index Cond: (namespace_id = (ci_namespace_mirrors.traversal_ids)[array_length(ci_namespace_mirrors.traversal_ids, 1)])
                       Buffers: shared hit=26856
   ->  Nested Loop  (cost=9753.59..10354.59 rows=29058 width=4) (actual time=30148.470..30169.108 rows=98 loops=1)
         Buffers: shared hit=47243 read=16346
         I/O Timings: read=273.387
         ->  HashAggregate  (cost=9753.30..9755.30 rows=200 width=4) (actual time=30147.870..30151.023 rows=9596 loops=1)
               Group Key: cte_project_ids.project_id
               Buffers: shared hit=27949 read=16346
               I/O Timings: read=273.387
               ->  CTE Scan on cte_project_ids  (cost=0.00..8669.60 rows=433480 width=4) (actual time=2.647..30143.762 rows=9596 loops=1)
                     Buffers: shared hit=27949 read=16346
                     I/O Timings: read=273.387
         ->  Index Scan using index_ci_runner_projects_on_project_id on ci_runner_projects  (cost=0.29..2.98 rows=2 width=8) (actual time=0.002..0.002 rows=0 loops=9596)
               Index Cond: (project_id = cte_project_ids.project_id)
               Buffers: shared hit=19294
   ->  Hash  (cost=2679.77..2679.77 rows=65077 width=271) (actual time=59.672..59.674 rows=65080 loops=1)
         Buckets: 65536  Batches: 1  Memory Usage: 16718kB
         Buffers: shared hit=2029
         ->  Seq Scan on ci_runners  (cost=0.00..2679.77 rows=65077 width=271) (actual time=0.011..30.437 rows=65080 loops=1)
               Buffers: shared hit=2029
 Planning Time: 2.799 ms
 Execution Time: 30229.598 ms

New attempt

EXPLAIN (BUFFERS, ANALYZE) WITH cte_namespace_ids AS MATERIALIZED (
  SELECT ci_namespace_mirrors.namespace_id
  FROM
    (VALUES (x),(y),(z),(...)) AS namespace_ids (id),
    LATERAL (
      SELECT ci_namespace_mirrors.traversal_ids[array_length(ci_namespace_mirrors.traversal_ids, 1)] AS namespace_id
      FROM ci_namespace_mirrors
      WHERE ci_namespace_mirrors.traversal_ids && ARRAY[namespace_ids.id]
    ) AS ci_namespace_mirrors
),
cte_project_ids AS MATERIALIZED (
  SELECT ci_project_mirrors.project_id
  FROM ci_project_mirrors
  WHERE ci_project_mirrors.namespace_id IN (SELECT namespace_id FROM cte_namespace_ids)
),
cte_runner_ids AS MATERIALIZED (
  SELECT ci_runner_projects.runner_id
  FROM ci_runner_projects
  WHERE ci_runner_projects.project_id IN (SELECT project_id FROM cte_project_ids)
)
SELECT ci_runners.*
FROM ci_runners
WHERE ci_runners.id IN (SELECT runner_id FROM cte_runner_ids);

Staging env result;

 Nested Loop  (cost=6505558.85..6506024.02 rows=29058 width=271) (actual time=1309.428..1309.771 rows=98 loops=1)
   Buffers: shared hit=173157
   CTE cte_namespace_ids
     ->  Nested Loop  (cost=154.63..3851907.64 rows=116120480 width=4) (actual time=0.315..1254.647 rows=8553 loops=1)
           Buffers: shared hit=126621
           ->  Values Scan on "*VALUES*"  (cost=0.00..74.54 rows=5963 width=4) (actual time=0.001..3.443 rows=5963 loops=1)
           ->  Bitmap Heap Scan on ci_namespace_mirrors  (cost=154.63..402.53 rows=19474 width=25) (actual time=0.207..0.208 rows=1 loops=5963)
                 Recheck Cond: (traversal_ids && ARRAY["*VALUES*".column1])
                 Heap Blocks: exact=6781
                 Buffers: shared hit=126621
                 ->  Bitmap Index Scan on index_gin_ci_namespace_mirrors_on_traversal_ids  (cost=0.00..149.76 rows=19474 width=0) (actual time=0.205..0.205 rows=2 loops=5963)
                       Index Cond: (traversal_ids && ARRAY["*VALUES*".column1])
                       Buffers: shared hit=119273
   CTE cte_project_ids
     ->  Nested Loop  (cost=2612711.23..2614581.33 rows=1680644 width=4) (actual time=1262.798..1288.301 rows=9613 loops=1)
           Buffers: shared hit=153532
           ->  HashAggregate  (cost=2612710.80..2612712.80 rows=200 width=4) (actual time=1262.772..1264.955 rows=7888 loops=1)
                 Group Key: cte_namespace_ids.namespace_id
                 Buffers: shared hit=126621
                 ->  CTE Scan on cte_namespace_ids  (cost=0.00..2322409.60 rows=116120480 width=4) (actual time=0.317..1257.723 rows=8553 loops=1)
                       Buffers: shared hit=126621
           ->  Index Scan using index_ci_project_mirrors_on_namespace_id on ci_project_mirrors  (cost=0.43..9.23 rows=11 width=8) (actual time=0.002..0.003 rows=1 loops=7888)
                 Index Cond: (namespace_id = cte_namespace_ids.namespace_id)
                 Buffers: shared hit=26911
   CTE cte_runner_ids
     ->  Nested Loop  (cost=37814.78..38415.78 rows=29058 width=4) (actual time=1295.012..1309.306 rows=98 loops=1)
           Buffers: shared hit=172863
           ->  HashAggregate  (cost=37814.49..37816.49 rows=200 width=4) (actual time=1294.646..1296.914 rows=9613 loops=1)
                 Group Key: cte_project_ids.project_id
                 Buffers: shared hit=153532
                 ->  CTE Scan on cte_project_ids  (cost=0.00..33612.88 rows=1680644 width=4) (actual time=1262.800..1291.046 rows=9613 loops=1)
                       Buffers: shared hit=153532
           ->  Index Scan using index_ci_runner_projects_on_project_id on ci_runner_projects  (cost=0.29..2.98 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=9613)
                 Index Cond: (project_id = cte_project_ids.project_id)
                 Buffers: shared hit=19331
   ->  HashAggregate  (cost=653.80..655.80 rows=200 width=4) (actual time=1309.409..1309.431 rows=98 loops=1)
         Group Key: cte_runner_ids.runner_id
         Buffers: shared hit=172863
         ->  CTE Scan on cte_runner_ids  (cost=0.00..581.16 rows=29058 width=4) (actual time=1295.014..1309.352 rows=98 loops=1)
               Buffers: shared hit=172863
   ->  Index Scan using ci_runners_pkey on ci_runners  (cost=0.29..2.73 rows=1 width=271) (actual time=0.003..0.003 rows=1 loops=98)
         Index Cond: (id = cte_runner_ids.runner_id)
         Buffers: shared hit=294
 Planning Time: 4.384 ms
 Execution Time: 1310.302 ms

UNION Query 3

Fetching namespace ids for gitlab-qa...

SELECT "members"."source_id"
FROM "members"
WHERE "members"."source_type" = 'Namespace'
  AND "members"."type" = 'GroupMember'
  AND "members"."user_id" = 1614863 -- gitlab-qa
  AND "members"."requested_at" IS NULL
  AND (access_level >= 10)
  AND (access_level >= 50);

Previous attempt

EXPLAIN (BUFFERS, ANALYZE) 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 (ci_namespace_mirrors.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);

Staging env result;

 Nested Loop  (cost=33411.54..34166.45 rows=375 width=271) (actual time=29658.600..29674.581 rows=745 loops=1)
   Buffers: shared hit=19692
   CTE cte_namespace_ids
     ->  Gather  (cost=1000.00..32493.34 rows=40596 width=4) (actual time=2.575..29659.853 rows=8106 loops=1)
           Workers Planned: 2
           Workers Launched: 2
           Buffers: shared hit=17451
           ->  Parallel Seq Scan on ci_namespace_mirrors  (cost=0.00..27433.74 rows=16915 width=4) (actual time=0.263..29643.134 rows=2702 loops=3)
                 Filter: (traversal_ids && '{..6k-integer..}'::integer[])
                 Rows Removed by Filter: 646340
                 Buffers: shared hit=17451
   ->  Hash Join  (cost=917.91..937.55 rows=375 width=4) (actual time=29658.574..29658.858 rows=745 loops=1)
         Hash Cond: (ci_runner_namespaces.namespace_id = cte_namespace_ids.namespace_id)
         Buffers: shared hit=17457
         ->  Seq Scan on ci_runner_namespaces  (cost=0.00..13.50 rows=750 width=8) (actual time=0.011..0.099 rows=754 loops=1)
               Buffers: shared hit=6
         ->  Hash  (cost=915.41..915.41 rows=200 width=4) (actual time=29658.548..29658.550 rows=8106 loops=1)
               Buckets: 8192 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 349kB
               Buffers: shared hit=17451
               ->  HashAggregate  (cost=913.41..915.41 rows=200 width=4) (actual time=29655.198..29657.057 rows=8106 loops=1)
                     Group Key: cte_namespace_ids.namespace_id
                     Buffers: shared hit=17451
                     ->  CTE Scan on cte_namespace_ids  (cost=0.00..811.92 rows=40596 width=4) (actual time=2.577..29649.377 rows=8106 loops=1)
                           Buffers: shared hit=17451
   ->  Index Scan using ci_runners_pkey on ci_runners  (cost=0.29..1.96 rows=1 width=271) (actual time=0.002..0.002 rows=1 loops=745)
         Index Cond: (id = ci_runner_namespaces.runner_id)
         Buffers: shared hit=2235
 Planning Time: 3.144 ms
 Execution Time: 29674.812 ms

New attempt

EXPLAIN (BUFFERS, ANALYZE) WITH cte_namespace_ids AS MATERIALIZED (
  SELECT ci_namespace_mirrors.namespace_id
  FROM
    (VALUES (x),(y),(z),(...)) AS namespace_ids (id),
    LATERAL (
      SELECT ci_namespace_mirrors.traversal_ids[array_length(ci_namespace_mirrors.traversal_ids, 1)] AS namespace_id
      FROM ci_namespace_mirrors
      WHERE ci_namespace_mirrors.traversal_ids && ARRAY[namespace_ids.id]
    ) AS ci_namespace_mirrors
),
cte_runner_ids AS MATERIALIZED (
  SELECT ci_runner_namespaces.runner_id
  FROM ci_runner_namespaces
  WHERE ci_runner_namespaces.namespace_id IN (SELECT namespace_id FROM cte_namespace_ids)
)
SELECT ci_runners.*
FROM ci_runners
WHERE ci_runners.id IN (SELECT runner_id FROM cte_runner_ids);

Staging env result;

 Nested Loop  (cost=6464651.31..6465196.61 rows=375 width=271) (actual time=1481.193..1483.263 rows=745 loops=1)
   Buffers: shared hit=140803
   CTE cte_namespace_ids
     ->  Nested Loop  (cost=154.63..3851907.64 rows=116120480 width=4) (actual time=0.309..1468.367 rows=8809 loops=1)
           Buffers: shared hit=138562
           ->  Values Scan on "*VALUES*"  (cost=0.00..74.54 rows=5963 width=4) (actual time=0.001..3.732 rows=5963 loops=1)
           ->  Bitmap Heap Scan on ci_namespace_mirrors  (cost=154.63..402.53 rows=19474 width=25) (actual time=0.243..0.243 rows=1 loops=5963)
                 Recheck Cond: (traversal_ids && ARRAY["*VALUES*".column1])
                 Heap Blocks: exact=6795
                 Buffers: shared hit=138562
                 ->  Bitmap Index Scan on index_gin_ci_namespace_mirrors_on_traversal_ids  (cost=0.00..149.76 rows=19474 width=0) (actual time=0.240..0.240 rows=2 loops=5963)
                       Index Cond: (traversal_ids && ARRAY["*VALUES*".column1])
                       Buffers: shared hit=131199
   CTE cte_runner_ids
     ->  Hash Join  (cost=2612715.30..2612734.94 rows=375 width=4) (actual time=1480.509..1480.792 rows=745 loops=1)
           Hash Cond: (ci_runner_namespaces.namespace_id = cte_namespace_ids.namespace_id)
           Buffers: shared hit=138568
           ->  Seq Scan on ci_runner_namespaces  (cost=0.00..13.50 rows=750 width=8) (actual time=0.014..0.113 rows=754 loops=1)
                 Buffers: shared hit=6
           ->  Hash  (cost=2612712.80..2612712.80 rows=200 width=4) (actual time=1480.479..1480.480 rows=8144 loops=1)
                 Buckets: 8192 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 351kB
                 Buffers: shared hit=138562
                 ->  HashAggregate  (cost=2612710.80..2612712.80 rows=200 width=4) (actual time=1477.457..1479.185 rows=8144 loops=1)
                       Group Key: cte_namespace_ids.namespace_id
                       Buffers: shared hit=138562
                       ->  CTE Scan on cte_namespace_ids  (cost=0.00..2322409.60 rows=116120480 width=4) (actual time=0.311..1471.778 rows=8809 loops=1)
                             Buffers: shared hit=138562
   ->  HashAggregate  (cost=8.44..10.44 rows=200 width=4) (actual time=1481.162..1481.271 rows=745 loops=1)
         Group Key: cte_runner_ids.runner_id
         Buffers: shared hit=138568
         ->  CTE Scan on cte_runner_ids  (cost=0.00..7.50 rows=375 width=4) (actual time=1480.511..1480.961 rows=745 loops=1)
               Buffers: shared hit=138568
   ->  Index Scan using ci_runners_pkey on ci_runners  (cost=0.29..2.73 rows=1 width=271) (actual time=0.002..0.002 rows=1 loops=745)
         Index Cond: (id = cte_runner_ids.runner_id)
         Buffers: shared hit=2235
 Planning Time: 4.379 ms
 Execution Time: 1483.749 ms

Total UNION

EXPLAIN (BUFFERS, ANALYZE) 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 (..6k-integer..)
  )
  UNION
  (
    WITH cte_namespace_ids AS MATERIALIZED (
      SELECT ci_namespace_mirrors.namespace_id
      FROM
        (VALUES (1982920),(2053124)...) AS namespace_ids (id),
        LATERAL (
          SELECT ci_namespace_mirrors.traversal_ids[array_length(ci_namespace_mirrors.traversal_ids, 1)] AS namespace_id
          FROM ci_namespace_mirrors
          WHERE ci_namespace_mirrors.traversal_ids && ARRAY[namespace_ids.id]
        ) AS ci_namespace_mirrors
    ),
    cte_project_ids AS MATERIALIZED (
      SELECT ci_project_mirrors.project_id
      FROM ci_project_mirrors
      WHERE ci_project_mirrors.namespace_id IN (SELECT namespace_id FROM cte_namespace_ids)
    ),
    cte_runner_ids AS MATERIALIZED (
      SELECT ci_runner_projects.runner_id
      FROM ci_runner_projects
      WHERE ci_runner_projects.project_id IN (SELECT project_id FROM cte_project_ids)
    )
    SELECT ci_runners.*
    FROM ci_runners
    WHERE ci_runners.id IN (SELECT runner_id FROM cte_runner_ids)
  )
  UNION
  (
    WITH cte_namespace_ids AS MATERIALIZED (
      SELECT ci_namespace_mirrors.namespace_id
      FROM
        (VALUES (2090737),(2090738)...) AS namespace_ids (id),
        LATERAL (
          SELECT ci_namespace_mirrors.traversal_ids[array_length(ci_namespace_mirrors.traversal_ids, 1)] AS namespace_id
          FROM ci_namespace_mirrors
          WHERE ci_namespace_mirrors.traversal_ids && ARRAY[namespace_ids.id]
        ) AS ci_namespace_mirrors
    ),
    cte_runner_ids AS MATERIALIZED (
      SELECT ci_runner_namespaces.runner_id
      FROM ci_runner_namespaces
      WHERE ci_runner_namespaces.namespace_id IN (SELECT namespace_id FROM cte_namespace_ids)
    )
    SELECT ci_runners.*
    FROM ci_runners
    WHERE ci_runners.id IN (SELECT runner_id FROM cte_runner_ids)
  )
) ci_runners
LIMIT 20
OFFSET 0;

Staging result;

 Limit  (cost=13019492.00..13019493.50 rows=20 width=3807) (actual time=4827.797..4827.845 rows=20 loops=1)
   Buffers: shared hit=474264
   ->  Unique  (cost=13019492.00..13021896.22 rows=36988 width=3807) (actual time=4827.796..4827.841 rows=20 loops=1)
         Buffers: shared hit=474264
         ->  Sort  (cost=13019492.00..13019584.47 rows=36988 width=3807) (actual time=4827.794..4827.816 rows=20 loops=1)
               Sort Key: ci_runners.id, ci_runners.token, ci_runners.created_at, ci_runners.updated_at, ci_runners.description, ci_runners.contacted_at, ci_runners.active, ci_runners.name, ci_runners.version, ci_runners.revision, ci_runners.platform, ci_runners.architecture, ci_runners.run_untagged, ci_runners.locked, ci_runners.access_level, ci_runners.ip_address, ci_runners.maximum_timeout, ci_runners.runner_type, ci_runners.token_encrypted, ci_runners.public_projects_minutes_cost_factor, ci_runners.private_projects_minutes_cost_factor, ci_runners.config, ci_runners.executor_type, ci_runners.maintainer_note, ci_runners.token_expires_at
               Sort Method: quicksort  Memory: 260kB
               Buffers: shared hit=474264
               ->  Append  (cost=3488.66..12977753.83 rows=36988 width=3807) (actual time=70.762..4826.976 rows=857 loops=1)
                     Buffers: shared hit=474264
                     ->  Hash Join  (cost=3488.66..5966.78 rows=7545 width=271) (actual time=70.761..72.855 rows=17 loops=1)
                           Hash Cond: (ci_runner_projects.runner_id = ci_runners.id)
                           Buffers: shared hit=13673
                           ->  Index Scan using index_ci_runner_projects_on_project_id on ci_runner_projects  (cost=0.29..2458.60 rows=7545 width=4) (actual time=3.486..5.561 rows=17 loops=1)
                                 Index Cond: (project_id = ANY ('{..6k-integer..}'::integer[]))
                                 Buffers: shared hit=11644
                           ->  Hash  (cost=2677.61..2677.61 rows=64861 width=271) (actual time=67.167..67.168 rows=65094 loops=1)
                                 Buckets: 65536  Batches: 1  Memory Usage: 16722kB
                                 Buffers: shared hit=2029
                                 ->  Seq Scan on ci_runners  (cost=0.00..2677.61 rows=64861 width=271) (actual time=0.011..32.113 rows=65094 loops=1)
                                       Buffers: shared hit=2029
                     ->  Nested Loop  (cost=6505564.97..6506029.55 rows=29067 width=271) (actual time=2408.085..2408.428 rows=98 loops=1)
                           Buffers: shared hit=254129
                           CTE cte_namespace_ids
                             ->  Nested Loop  (cost=154.63..3851913.64 rows=116120480 width=4) (actual time=0.513..2351.707 rows=9130 loops=1)
                                   Buffers: shared hit=204230
                                   ->  Values Scan on "*VALUES*"  (cost=0.00..74.54 rows=5963 width=4) (actual time=0.003..3.475 rows=5963 loops=1)
                                   ->  Bitmap Heap Scan on ci_namespace_mirrors  (cost=154.63..402.53 rows=19474 width=25) (actual time=0.391..0.391 rows=2 loops=5963)
                                         Recheck Cond: (traversal_ids && ARRAY["*VALUES*".column1])
                                         Heap Blocks: exact=6856
                                         Buffers: shared hit=204230
                                         ->  Bitmap Index Scan on index_gin_ci_namespace_mirrors_on_traversal_ids  (cost=0.00..149.76 rows=19474 width=0) (actual time=0.389..0.389 rows=2 loops=5963)
                                               Index Cond: (traversal_ids && ARRAY["*VALUES*".column1])
                                               Buffers: shared hit=196792
                           CTE cte_project_ids
                             ->  Nested Loop  (cost=2612711.23..2614581.33 rows=1680644 width=4) (actual time=2362.177..2387.763 rows=10117 loops=1)
                                   Buffers: shared hit=233448
                                   ->  HashAggregate  (cost=2612710.80..2612712.80 rows=200 width=4) (actual time=2362.135..2364.235 rows=8465 loops=1)
                                         Group Key: cte_namespace_ids.namespace_id
                                         Buffers: shared hit=204230
                                         ->  CTE Scan on cte_namespace_ids  (cost=0.00..2322409.60 rows=116120480 width=4) (actual time=0.515..2355.346 rows=9130 loops=1)
                                               Buffers: shared hit=204230
                                   ->  Index Scan using index_ci_project_mirrors_on_namespace_id on ci_project_mirrors  (cost=0.43..9.23 rows=11 width=8) (actual time=0.002..0.002 rows=1 loops=8465)
                                         Index Cond: (namespace_id = cte_namespace_ids.namespace_id)
                                         Buffers: shared hit=29218
                           CTE cte_runner_ids
                             ->  Nested Loop  (cost=37814.78..38415.70 rows=29067 width=4) (actual time=2394.983..2407.969 rows=98 loops=1)
                                   Buffers: shared hit=253835
                                   ->  HashAggregate  (cost=37814.49..37816.49 rows=200 width=4) (actual time=2394.601..2396.363 rows=10117 loops=1)
                                         Group Key: cte_project_ids.project_id
                                         Buffers: shared hit=233448
                                         ->  CTE Scan on cte_project_ids  (cost=0.00..33612.88 rows=1680644 width=4) (actual time=2362.179..2390.478 rows=10117 loops=1)
                                               Buffers: shared hit=233448
                                   ->  Index Scan using index_ci_runner_projects_on_project_id on ci_runner_projects ci_runner_projects_1  (cost=0.29..2.98 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=10117)
                                         Index Cond: (project_id = cte_project_ids.project_id)
                                         Buffers: shared hit=20387
                           ->  HashAggregate  (cost=654.01..656.01 rows=200 width=4) (actual time=2408.061..2408.080 rows=98 loops=1)
                                 Group Key: cte_runner_ids.runner_id
                                 Buffers: shared hit=253835
                                 ->  CTE Scan on cte_runner_ids  (cost=0.00..581.34 rows=29067 width=4) (actual time=2394.986..2408.010 rows=98 loops=1)
                                       Buffers: shared hit=253835
                           ->  Index Scan using ci_runners_pkey on ci_runners ci_runners_1  (cost=0.29..2.73 rows=1 width=271) (actual time=0.003..0.003 rows=1 loops=98)
                                 Index Cond: (id = cte_runner_ids.runner_id)
                                 Buffers: shared hit=294
                     ->  Nested Loop  (cost=6464657.38..6465202.68 rows=376 width=271) (actual time=2343.267..2345.558 rows=742 loops=1)
                           Buffers: shared hit=206462
                           CTE cte_namespace_ids
                             ->  Nested Loop  (cost=154.63..3851913.64 rows=116120480 width=4) (actual time=0.504..2329.417 rows=9130 loops=1)
                                   Buffers: shared hit=204230
                                   ->  Values Scan on "*VALUES*_1"  (cost=0.00..74.54 rows=5963 width=4) (actual time=0.002..3.400 rows=5963 loops=1)
                                   ->  Bitmap Heap Scan on ci_namespace_mirrors ci_namespace_mirrors_1  (cost=154.63..402.53 rows=19474 width=25) (actual time=0.387..0.388 rows=2 loops=5963)
                                         Recheck Cond: (traversal_ids && ARRAY["*VALUES*_1".column1])
                                         Heap Blocks: exact=6856
                                         Buffers: shared hit=204230
                                         ->  Bitmap Index Scan on index_gin_ci_namespace_mirrors_on_traversal_ids  (cost=0.00..149.76 rows=19474 width=0) (actual time=0.385..0.385 rows=2 loops=5963)
                                               Index Cond: (traversal_ids && ARRAY["*VALUES*_1".column1])
                                               Buffers: shared hit=196792
                           CTE cte_runner_ids
                             ->  Hash Join  (cost=2612715.30..2612734.99 rows=376 width=4) (actual time=2342.603..2342.861 rows=742 loops=1)
                                   Hash Cond: (ci_runner_namespaces.namespace_id = cte_namespace_ids_1.namespace_id)
                                   Buffers: shared hit=204236
                                   ->  Seq Scan on ci_runner_namespaces  (cost=0.00..13.53 rows=753 width=8) (actual time=0.013..0.108 rows=751 loops=1)
                                         Buffers: shared hit=6
                                   ->  Hash  (cost=2612712.80..2612712.80 rows=200 width=4) (actual time=2342.570..2342.571 rows=8465 loops=1)
                                         Buckets: 16384 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 426kB
                                         Buffers: shared hit=204230
                                         ->  HashAggregate  (cost=2612710.80..2612712.80 rows=200 width=4) (actual time=2339.524..2341.133 rows=8465 loops=1)
                                               Group Key: cte_namespace_ids_1.namespace_id
                                               Buffers: shared hit=204230
                                               ->  CTE Scan on cte_namespace_ids cte_namespace_ids_1  (cost=0.00..2322409.60 rows=116120480 width=4) (actual time=0.506..2333.213 rows=9130 loops=1)
                                                     Buffers: shared hit=204230
                           ->  HashAggregate  (cost=8.46..10.46 rows=200 width=4) (actual time=2343.243..2343.370 rows=742 loops=1)
                                 Group Key: cte_runner_ids_1.runner_id
                                 Buffers: shared hit=204236
                                 ->  CTE Scan on cte_runner_ids cte_runner_ids_1  (cost=0.00..7.52 rows=376 width=4) (actual time=2342.606..2343.041 rows=742 loops=1)
                                       Buffers: shared hit=204236
                           ->  Index Scan using ci_runners_pkey on ci_runners ci_runners_2  (cost=0.29..2.73 rows=1 width=271) (actual time=0.002..0.002 rows=1 loops=742)
                                 Index Cond: (id = cte_runner_ids_1.runner_id)
                                 Buffers: shared hit=2226
 Planning Time: 99.063 ms
 Execution Time: 4829.064 ms

Production result;

• cold: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8548/commands/31156
• hot: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8548/commands/31157

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.

Related to #336436