Add limit to RelatedPipelinesFinder
What does this MR do and why?
This MR adds a limit to the number of pipelines returned from Security::RelatedPipelinesFinder
and also fixes the Database/AvoidUsingPluckWithoutLimit
rubocop error. More context !147710 (comment 1847252604)
Database
WITH RECURSIVE "base_and_descendants" AS (
SELECT
"ci_pipelines"."id"
FROM
"ci_pipelines"
WHERE
"ci_pipelines"."project_id" = 278964
AND "ci_pipelines"."id" IN (687215962, 685462259, 685325240, 687005950)
UNION (
SELECT
"ci_pipelines"."id"
FROM
"ci_pipelines",
"base_and_descendants",
"ci_sources_pipelines"
WHERE
"ci_sources_pipelines"."pipeline_id" = "ci_pipelines"."id"
AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id"
AND "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id"))
SELECT
"ci_pipelines"."id"
FROM
"base_and_descendants" AS "ci_pipelines" LIMIT 1000;
Query Plan
Limit (cost=1210.30..1210.52 rows=11 width=4) (actual time=6.724..55.864 rows=12 loops=1)
Buffers: shared hit=80 read=46
I/O Timings: read=54.789 write=0.000
CTE base_and_descendants
-> Recursive Union (cost=0.58..1210.30 rows=11 width=4) (actual time=6.720..55.832 rows=12 loops=1)
Buffers: shared hit=80 read=46
I/O Timings: read=54.789 write=0.000
-> Index Only Scan using index_ci_pipelines_on_project_id_and_id_desc on public.ci_pipelines ci_pipelines_1 (cost=0.58..9.86 rows=1 width=4) (actual time=6.715..10.083 rows=4 loops=1)
Index Cond: ((ci_pipelines_1.project_id = 278964) AND (ci_pipelines_1.id = ANY ('{687215962,685462259,685325240,687005950}'::integer[])))
Heap Fetches: 0
Buffers: shared hit=13 read=10
I/O Timings: read=9.811 write=0.000
-> Nested Loop (cost=1.15..120.02 rows=1 width=4) (actual time=11.597..22.848 rows=4 loops=2)
Buffers: shared hit=67 read=36
I/O Timings: read=44.978 write=0.000
-> Nested Loop (cost=0.57..118.01 rows=1 width=8) (actual time=8.939..14.777 rows=4 loops=2)
Buffers: shared hit=41 read=21
I/O Timings: read=29.127 write=0.000
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.007 rows=6 loops=2)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_sources_pipelines_on_source_pipeline_id on public.ci_sources_pipelines (cost=0.57..11.77 rows=1 width=16) (actual time=2.279..2.458 rows=1 loops=12)
Index Cond: (ci_sources_pipelines.source_pipeline_id = base_and_descendants.id)
Filter: (ci_sources_pipelines.source_project_id = ci_sources_pipelines.project_id)
Rows Removed by Filter: 0
Buffers: shared hit=41 read=21
I/O Timings: read=29.127 write=0.000
-> Index Only Scan using ci_pipelines_pkey on public.ci_pipelines ci_pipelines_2 (cost=0.58..2.01 rows=1 width=4) (actual time=2.013..2.013 rows=1 loops=8)
Index Cond: (ci_pipelines_2.id = ci_sources_pipelines.pipeline_id)
Heap Fetches: 0
Buffers: shared hit=26 read=15
I/O Timings: read=15.851 write=0.000
-> CTE Scan on base_and_descendants ci_pipelines (cost=0.00..0.22 rows=11 width=4) (actual time=6.723..55.848 rows=12 loops=1)
Buffers: shared hit=80 read=46
I/O Timings: read=54.789 write=0.000
Time: 63.553 ms
- planning: 7.534 ms
- execution: 56.019 ms
- I/O read: 54.789 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 80 (~640.00 KiB) from the buffer pool
- reads: 46 (~368.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.