Loading
Stabilize runner jobs authorization query
What does this MR do and why?
Keep the runner jobs permission check correlated with each job using a CROSS JOIN LATERAL lookup of ci_project_mirrors. The OFFSET 0 and LIMIT 1 on that lookup are intended to keep PostgreSQL from switching to a project-first plan over partitioned CI builds. This is a plan-stability change, not a bounded scan or a scan-budget fallback.
The default-off lateral_runner_jobs_api_pagination flag switches non-admin requests to the lateral authorization query, including requests with system_id and keyset pagination. With the flag off, the existing project filter remains in use. Status filtering and sorting remain outside the authorization check.
Finder and API specs cover authorized results, status filtering, ordering, and pagination. Database review still needs representative full page and count SQL and production-scale query plans, including requests with and without a status filter.
Database query plans
Before:
The problematic query flips to use index on (status, type, runner_id) in PG18
Click to expand the old sql
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT
"p_ci_builds".*
FROM
"p_ci_builds"
WHERE
"p_ci_builds"."type" = 'Ci::Build'
AND "p_ci_builds"."runner_id" = 51700412
AND "p_ci_builds"."project_id" IN (
SELECT
"ci_project_mirrors"."project_id"
FROM
(
(
SELECT
"ci_project_mirrors".*
FROM
"ci_project_mirrors"
WHERE
1 = 0
)
UNION
(
SELECT
"ci_project_mirrors".*
FROM
"ci_project_mirrors"
WHERE
"ci_project_mirrors"."namespace_id" IN (
SELECT
"ci_namespace_mirrors"."namespace_id"
FROM
(
(
SELECT
"ci_namespace_mirrors".*
FROM
"ci_namespace_mirrors"
WHERE
(((traversal_ids [1])) IN ((59715498)))
)
) ci_namespace_mirrors
)
)
) ci_project_mirrors
)
ORDER BY
"p_ci_builds"."id" DESC
LIMIT
101 OFFSET 100;Click to expand the query plan
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=142931.05..142931.30 rows=101 width=783) (actual time=30.527..30.755 rows=101.00 loops=1)
Buffers: shared hit=15730
-> Sort (cost=142930.80..142932.33 rows=613 width=783) (actual time=30.518..30.744 rows=201.00 loops=1)
Sort Key: p_ci_builds.id DESC
Sort Method: top-N heapsort Memory: 145kB
Buffers: shared hit=15730
-> Hash Join (cost=31616.97..142904.28 rows=613 width=783) (actual time=21.209..29.168 rows=2188.00 loops=1)
Hash Cond: (p_ci_builds.project_id = ci_project_mirrors.project_id)
Buffers: shared hit=15730
-> Append (cost=0.71..111067.94 rows=81240 width=782) (actual time=2.549..9.228 rows=2188.00 loops=1)
Buffers: shared hit=3275
-> Index Scan using index_ci_builds_on_status_and_type_and_runner_id on ci_builds p_ci_builds_1 (cost=0.71..26507.43 rows=18937 width=1271) (actual time=0.297..0.297 rows=0.00 loops=1)
Index Cond: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
Index Searches: 19
Buffers: shared hit=105
-> Index Scan using ci_builds_101_status_type_runner_id_idx on ci_builds_101 p_ci_builds_2 (cost=0.70..8499.41 rows=6337 width=618) (actual time=0.373..0.373 rows=0.00 loops=1)
Index Cond: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
Index Searches: 20
Buffers: shared hit=110
-> Index Scan using ci_builds_102_status_type_runner_id_idx on ci_builds_102 p_ci_builds_3 (cost=0.70..21488.09 rows=16128 width=638) (actual time=0.354..0.354 rows=0.00 loops=1)
Index Cond: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
Index Searches: 20
Buffers: shared hit=109
-> Index Scan using ci_builds_103_status_type_runner_id_idx on ci_builds_103 p_ci_builds_4 (cost=0.57..3977.70 rows=2929 width=634) (actual time=0.260..0.260 rows=0.00 loops=1)
Index Cond: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
Index Searches: 19
Buffers: shared hit=86
-> Index Scan using ci_builds_104_status_type_runner_id_idx on ci_builds_104 p_ci_builds_5 (cost=0.57..4618.73 rows=3411 width=635) (actual time=0.267..0.268 rows=0.00 loops=1)
Index Cond: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
Index Searches: 19
Buffers: shared hit=86
-> Index Scan using ci_builds_105_status_type_runner_id_idx on ci_builds_105 p_ci_builds_6 (cost=0.57..4212.02 rows=3098 width=623) (actual time=0.276..0.276 rows=0.00 loops=1)
Index Cond: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
Index Searches: 19
Buffers: shared hit=86
-> Index Scan using ci_builds_106_status_type_runner_id_idx on ci_builds_106 p_ci_builds_7 (cost=0.57..4318.48 rows=3176 width=621) (actual time=0.321..0.322 rows=0.00 loops=1)
Index Cond: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
Index Searches: 19
Buffers: shared hit=86
-> Index Scan using ci_builds_107_status_type_runner_id_idx on ci_builds_107 p_ci_builds_8 (cost=0.70..12738.94 rows=9335 width=635) (actual time=0.307..0.307 rows=0.00 loops=1)
Index Cond: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
Index Searches: 19
Buffers: shared hit=106
-> Index Scan using ci_builds_108_status_type_runner_id_idx on ci_builds_108 p_ci_builds_9 (cost=0.57..4285.68 rows=3146 width=638) (actual time=0.090..0.305 rows=7.00 loops=1)
Index Cond: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
Index Searches: 19
Buffers: shared hit=94
-> Index Scan using ci_builds_109_status_type_runner_id_idx on ci_builds_109 p_ci_builds_10 (cost=0.57..4742.38 rows=3476 width=639) (actual time=0.316..0.316 rows=0.00 loops=1)
Index Cond: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
Index Searches: 19
Buffers: shared hit=87
-> Index Scan using ci_builds_110_status_type_runner_id_idx on ci_builds_110 p_ci_builds_11 (cost=0.57..4764.14 rows=3493 width=638) (actual time=0.128..0.511 rows=69.00 loops=1)
Index Cond: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
Index Searches: 19
Buffers: shared hit=155
-> Index Scan using ci_builds_111_status_type_runner_id_idx on ci_builds_111 p_ci_builds_12 (cost=0.57..4935.13 rows=3637 width=635) (actual time=0.112..1.369 rows=449.00 loops=1)
Index Cond: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
Index Searches: 19
Buffers: shared hit=506
-> Index Scan using ci_builds_112_status_type_runner_id_idx on ci_builds_112 p_ci_builds_13 (cost=0.57..5573.60 rows=4136 width=638) (actual time=0.125..4.089 rows=1663.00 loops=1)
Index Cond: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
Index Searches: 22
Buffers: shared hit=1659
-> Seq Scan on ci_builds_113 p_ci_builds_14 (cost=0.00..0.00 rows=1 width=3516) (actual time=0.010..0.010 rows=0.00 loops=1)
Filter: (((type)::text = 'Ci::Build'::text) AND (runner_id = 51700412))
-> Hash (cost=31613.77..31613.77 rows=200 width=4) (actual time=18.643..18.853 rows=2395.00 loops=1)
Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 117kB
Buffers: shared hit=12455
-> HashAggregate (cost=31611.77..31613.77 rows=200 width=4) (actual time=18.145..18.573 rows=2395.00 loops=1)
Group Key: ci_project_mirrors.project_id
Batches: 1 Memory Usage: 169kB
Buffers: shared hit=12455
-> Subquery Scan on ci_project_mirrors (cost=31416.36..31608.37 rows=1358 width=4) (actual time=16.705..17.861 rows=2395.00 loops=1)
Buffers: shared hit=12455
-> Unique (cost=31416.36..31594.79 rows=1358 width=16) (actual time=16.703..17.674 rows=2395.00 loops=1)
Buffers: shared hit=12455
-> Merge Append (cost=31416.36..31584.61 rows=1358 width=16) (actual time=16.702..17.391 rows=2395.00 loops=1)
Sort Key: id, project_id, namespace_id
Buffers: shared hit=12455
-> Sort (cost=0.01..0.02 rows=0 width=0) (actual time=0.015..0.015 rows=0.00 loops=1)
Sort Key: id, project_id, namespace_id
Sort Method: quicksort Memory: 25kB
-> Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.002 rows=0.00 loops=1)
One-Time Filter: false
-> Gather Merge (cost=31416.34..31571.00 rows=1357 width=16) (actual time=16.685..17.214 rows=2395.00 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=12455
-> Sort (cost=30416.33..30418.33 rows=798 width=16) (actual time=12.493..12.552 rows=1197.50 loops=2)
Sort Key: ci_project_mirrors_1.id, ci_project_mirrors_1.project_id, ci_project_mirrors_1.namespace_id
Sort Method: quicksort Memory: 111kB
Buffers: shared hit=12455
Worker 0: Sort Method: quicksort Memory: 37kB
-> Nested Loop (cost=63.91..30377.87 rows=798 width=16) (actual time=1.246..12.220 rows=1197.50 loops=2)
Buffers: shared hit=12438
-> Parallel Bitmap Heap Scan on ci_namespace_mirrors (cost=63.47..3852.98 rows=1359 width=4) (actual time=0.631..3.335 rows=1412.00 loops=2)
Recheck Cond: (traversal_ids[1] = 59715498)
Heap Blocks: exact=1042
Buffers: shared hit=1946
Worker 0: Heap Blocks: exact=829
-> Bitmap Index Scan on index_ci_namespace_mirrors_on_traversal_ids_unnest (cost=0.00..62.89 rows=2310 width=0) (actual time=0.868..0.868 rows=2824.00 loops=1)
Index Cond: (traversal_ids[1] = 59715498)
Index Searches: 1
Buffers: shared hit=51
-> Index Scan using index_ci_project_mirrors_on_namespace_id on ci_project_mirrors ci_project_mirrors_1 (cost=0.44..19.17 rows=35 width=16) (actual time=0.004..0.006 rows=0.85 loops=2824)
Index Cond: (namespace_id = ci_namespace_mirrors.namespace_id)
Index Searches: 2824
Buffers: shared hit=10492
Settings: effective_cache_size = '338688MB', random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB'
Planning:
Buffers: shared hit=12455
Planning Time: 191.511 ms
Execution Time: 31.248 ms
(113 rows)After
The new query will stay with index on (runner_id, id DESC) in PG18
Click to expand the new query
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT
"p_ci_builds".*
FROM
"p_ci_builds"
CROSS JOIN LATERAL (
SELECT
1
FROM
"ci_project_mirrors"
WHERE
"ci_project_mirrors"."project_id" = "p_ci_builds"."project_id"
AND "ci_project_mirrors"."project_id" IN (
SELECT
"ci_project_mirrors"."project_id"
FROM
(
(
SELECT
"ci_project_mirrors".*
FROM
"ci_project_mirrors"
WHERE
1 = 0
)
UNION
(
SELECT
"ci_project_mirrors".*
FROM
"ci_project_mirrors"
WHERE
"ci_project_mirrors"."namespace_id" IN (
SELECT
"ci_namespace_mirrors"."namespace_id"
FROM
(
(
SELECT
"ci_namespace_mirrors".*
FROM
"ci_namespace_mirrors"
WHERE
(((traversal_ids [1])) IN ((59715498)))
)
) ci_namespace_mirrors
)
)
) ci_project_mirrors
)
LIMIT
1 OFFSET 0
) allowed_project
WHERE
"p_ci_builds"."type" = 'Ci::Build'
AND "p_ci_builds"."runner_id" = 51700412
ORDER BY
"p_ci_builds"."id" DESC
LIMIT
101 OFFSET 100;Click to expand the query plan
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1056.05..2106.62 rows=101 width=783) (actual time=2.808..3.196 rows=101.00 loops=1)
Buffers: shared hit=432
-> Nested Loop (cost=15.88..845049.11 rows=81240 width=783) (actual time=1.680..3.184 rows=201.00 loops=1)
Buffers: shared hit=432
-> Merge Append (cost=8.11..134528.39 rows=81240 width=782) (actual time=0.339..1.078 rows=201.00 loops=1)
Sort Key: p_ci_builds.id DESC
Buffers: shared hit=249
-> Index Scan using index_c2e24a89ff on ci_builds p_ci_builds_1 (cost=0.71..29733.33 rows=18937 width=1271) (actual time=0.040..0.040 rows=0.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=8
-> Index Scan using index_3c2855dadb on ci_builds_101 p_ci_builds_2 (cost=0.58..10336.32 rows=6337 width=618) (actual time=0.018..0.019 rows=0.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=4
-> Index Scan using index_98bb37e896 on ci_builds_102 p_ci_builds_3 (cost=0.70..26590.36 rows=16128 width=638) (actual time=0.032..0.032 rows=0.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=5
-> Index Scan using ci_builds_103_runner_id_id_idx on ci_builds_103 p_ci_builds_4 (cost=0.57..4824.76 rows=2929 width=634) (actual time=0.018..0.018 rows=0.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=4
-> Index Scan using ci_builds_104_runner_id_id_idx on ci_builds_104 p_ci_builds_5 (cost=0.57..5640.98 rows=3411 width=635) (actual time=0.024..0.024 rows=0.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=4
-> Index Scan using ci_builds_105_runner_id_id_idx on ci_builds_105 p_ci_builds_6 (cost=0.57..5128.93 rows=3098 width=623) (actual time=0.017..0.017 rows=0.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=4
-> Index Scan using ci_builds_106_runner_id_id_idx on ci_builds_106 p_ci_builds_7 (cost=0.57..5275.99 rows=3176 width=621) (actual time=0.023..0.023 rows=0.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=4
-> Index Scan using ci_builds_107_runner_id_id_idx on ci_builds_107 p_ci_builds_8 (cost=0.58..15426.59 rows=9335 width=635) (actual time=0.016..0.016 rows=0.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=4
-> Index Scan using ci_builds_108_runner_id_id_idx on ci_builds_108 p_ci_builds_9 (cost=0.57..5219.87 rows=3146 width=638) (actual time=0.034..0.034 rows=1.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=5
-> Index Scan using ci_builds_109_runner_id_id_idx on ci_builds_109 p_ci_builds_10 (cost=0.57..5765.78 rows=3476 width=639) (actual time=0.020..0.020 rows=0.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=4
-> Index Scan using ci_builds_110_runner_id_id_idx on ci_builds_110 p_ci_builds_11 (cost=0.57..5763.83 rows=3493 width=638) (actual time=0.034..0.034 rows=1.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=5
-> Index Scan using ci_builds_111_runner_id_id_idx on ci_builds_111 p_ci_builds_12 (cost=0.57..6018.59 rows=3637 width=635) (actual time=0.030..0.030 rows=1.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=5
-> Index Scan using ci_builds_112_runner_id_id_idx on ci_builds_112 p_ci_builds_13 (cost=0.57..6846.90 rows=4136 width=638) (actual time=0.023..0.722 rows=201.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=191
-> Index Scan using ci_builds_113_runner_id_id_idx on ci_builds_113 p_ci_builds_14 (cost=0.12..3.15 rows=1 width=3516) (actual time=0.005..0.005 rows=0.00 loops=1)
Index Cond: (runner_id = 51700412)
Filter: ((type)::text = 'Ci::Build'::text)
Index Searches: 1
Buffers: shared hit=2
-> Memoize (cost=7.77..10.85 rows=1 width=0) (actual time=0.010..0.010 rows=1.00 loops=201)
Cache Key: p_ci_builds.project_id
Cache Mode: binary
Hits: 189 Misses: 12 Evictions: 0 Overflows: 0 Memory Usage: 2kB
Buffers: shared hit=183
-> Subquery Scan on allowed_project (cost=7.76..10.84 rows=1 width=0) (actual time=0.063..0.064 rows=1.00 loops=12)
Buffers: shared hit=183
-> Limit (cost=7.76..10.83 rows=1 width=4) (actual time=0.063..0.063 rows=1.00 loops=12)
Buffers: shared hit=183
-> Nested Loop Semi Join (cost=7.76..10.83 rows=1 width=4) (actual time=0.063..0.063 rows=1.00 loops=12)
Buffers: shared hit=183
-> Index Only Scan using index_ci_project_mirrors_on_project_id on ci_project_mirrors (cost=0.56..3.58 rows=1 width=4) (actual time=0.044..0.044 rows=1.00 loops=12)
Index Cond: (project_id = p_ci_builds.project_id)
Heap Fetches: 2
Index Searches: 12
Buffers: shared hit=60
-> Unique (cost=7.19..7.21 rows=2 width=16) (actual time=0.018..0.018 rows=1.00 loops=12)
Buffers: shared hit=123
-> Sort (cost=7.19..7.20 rows=2 width=16) (actual time=0.018..0.018 rows=1.00 loops=12)
Sort Key: id, project_id, namespace_id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=123
-> Append (cost=0.00..7.18 rows=2 width=16) (actual time=0.015..0.015 rows=1.00 loops=12)
Buffers: shared hit=120
-> Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0.00 loops=12)
One-Time Filter: false
-> Nested Loop (cost=1.13..7.17 rows=1 width=16) (actual time=0.014..0.015 rows=1.00 loops=12)
Buffers: shared hit=120
-> Index Scan using index_ci_project_mirrors_on_project_id on ci_project_mirrors ci_project_mirrors_1 (cost=0.56..3.58 rows=1 width=16) (actual time=0.007..0.007 rows=1.00 loops=12)
Index Cond: (project_id = p_ci_builds.project_id)
Index Searches: 12
Buffers: shared hit=60
-> Index Scan using index_ci_namespace_mirrors_on_namespace_id on ci_namespace_mirrors (cost=0.57..3.59 rows=1 width=4) (actual time=0.006..0.006 rows=1.00 loops=12)
Index Cond: (namespace_id = ci_project_mirrors_1.namespace_id)
Filter: (traversal_ids[1] = 59715498)
Index Searches: 12
Buffers: shared hit=60
Settings: effective_cache_size = '338688MB', random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB'
Planning:
Buffers: shared hit=12418
Planning Time: 74.234 ms
Execution Time: 3.698 ms
(119 rows)References
Screenshots or screen recordings
Not applicable: no UI changes.
How to set up and validate locally
- With the flag off, compare authorized runner jobs, status filters, and pagination against the existing behavior.
- Enable
lateral_runner_jobs_api_paginationand repeat for offset, keyset, andsystem_idrequests. - Compare complete page and count query plans on representative production-scale data before enabling the flag broadly.
MR acceptance checklist
- Add finder and request specs for the query behavior.
- Keep the new behavior behind a default-off feature flag.
- Add representative SQL and query plans; obtain database review.
- Confirm the feature-flag rollout plan before enabling it.
Edited by Tianwen Chen