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