Filter by ref name is added

nagraj raikarrequested to merge
gitlab-community/gitlab-org/gitlab:407821_filter_by_ref_name into master

What does this MR do and why?

This MR adds a new ref query parameter to the /projects/:id/jobs API endpoint, allowing users to filter jobs by branch name (ref). This enhancement addresses the need to retrieve jobs for a specific branch without fetching all jobs and filtering client-side.

Key changes:

  • Added a new by_ref scope in the Ci::Build model that filters builds by project ID and ref name
  • Updated the Jobs API to accept an optional ref parameter
  • Added comprehensive test coverage for the new filtering functionality
  • Updated OpenAPI documentation to reflect the new parameter

Performance considerations: The database query plan shows efficient index usage when filtering by ref, with the query completing in ~292ms for 100 builds on the gitlab-org/gitlab project.

Changelog: added

References

Related issue: #409821

Screenshots or screen recordings

screenrecord
for_ref

How to set up and validate locally

  • Start GDK
  • Fetch branch locally
  • Create some jobs on different branches (or use existing ones)
  • Test the API with the ref parameter: 
    curl --header "PRIVATE-TOKEN: <your-token>" "http://gdk.test:3000/api/v4/projects/<project-id>/jobs?ref=<branch-name>"
  • Verify only jobs from the specified branch are returned
  • Test without the ref parameter to ensure all jobs are still returned

Database query plan

Fetching 100 builds by ref from gitlab-org/gitlab project

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/46054/commands/140801

SELECT "p_ci_builds"."id"
FROM "p_ci_builds"
  INNER JOIN "p_ci_pipelines" ON "p_ci_pipelines"."partition_id" IS NOT NULL
    AND "p_ci_pipelines"."id" = "p_ci_builds"."commit_id"
    AND "p_ci_pipelines"."partition_id" = "p_ci_builds"."partition_id"
WHERE "p_ci_builds"."type" = 'Ci::Build'
  AND "p_ci_builds"."project_id" = 278964 -- gitlab-org/gitlab
  AND "p_ci_builds"."ref" = 'master'
LIMIT 100
 Limit  (cost=1.29..5331.12 rows=100 width=8) (actual time=19.017..292.076 rows=100 loops=1)
   Buffers: shared hit=60 read=304 dirtied=1
   WAL: records=1 fpi=1 bytes=6725
   ->  Nested Loop  (cost=1.29..694589673.51 rows=13032133 width=8) (actual time=19.015..292.042 rows=100 loops=1)
         Buffers: shared hit=60 read=304 dirtied=1
         WAL: records=1 fpi=1 bytes=6725
         ->  Append  (cost=0.71..304573992.40 rows=35398881 width=24) (actual time=8.733..275.372 rows=100 loops=1)
               Buffers: shared hit=20 read=286 dirtied=1
               WAL: records=1 fpi=1 bytes=6725
               ->  Index Scan using index_e4a5307529 on gitlab_partitions_dynamic.ci_builds p_ci_builds_1  (cost=0.71..133930660.94 rows=20237604 width=24) (actual time=8.731..275.329 rows=100 loops=1)
                     Index Cond: (p_ci_builds_1.project_id = 278964)
                     Filter: (((p_ci_builds_1.type)::text = 'Ci::Build'::text) AND ((p_ci_builds_1.ref)::text = 'master'::text))
                     Rows Removed by Filter: 216
                     Buffers: shared hit=20 read=286 dirtied=1
                     WAL: records=1 fpi=1 bytes=6725
               ->  Index Scan using index_a2c19538cd on gitlab_partitions_dynamic.ci_builds_101 p_ci_builds_2  (cost=0.58..41994242.13 rows=3859620 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                     Index Cond: (p_ci_builds_2.project_id = 278964)
                     Filter: (((p_ci_builds_2.type)::text = 'Ci::Build'::text) AND ((p_ci_builds_2.ref)::text = 'master'::text))
                     Rows Removed by Filter: 0
               ->  Index Scan using index_75567f633a on gitlab_partitions_dynamic.ci_builds_102 p_ci_builds_3  (cost=0.70..73340327.82 rows=6189255 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                     Index Cond: (p_ci_builds_3.project_id = 278964)
                     Filter: (((p_ci_builds_3.type)::text = 'Ci::Build'::text) AND ((p_ci_builds_3.ref)::text = 'master'::text))
                     Rows Removed by Filter: 0
               ->  Bitmap Heap Scan on gitlab_partitions_dynamic.ci_builds_103 p_ci_builds_4  (cost=7720282.56..9778344.05 rows=967068 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                     ->  BitmapAnd  (cost=7720282.56..7720282.56 rows=967068 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                           ->  Bitmap Index Scan using ci_builds_103_project_id_id_idx  (cost=0.00..120552.56 rows=8409799 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (p_ci_builds_4.project_id = 278964)
                           ->  Bitmap Index Scan using ci_builds_103_commit_id_type_ref_idx  (cost=0.00..7599246.21 rows=44945052 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (((p_ci_builds_4.type)::text = 'Ci::Build'::text) AND ((p_ci_builds_4.ref)::text = 'master'::text))
               ->  Bitmap Heap Scan on gitlab_partitions_dynamic.ci_builds_104 p_ci_builds_5  (cost=8878599.09..11039234.51 rows=1022913 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                     ->  BitmapAnd  (cost=8878599.09..8878599.09 rows=1022913 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                           ->  Bitmap Index Scan using ci_builds_104_project_id_id_idx  (cost=0.00..128544.65 rows=9005411 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (p_ci_builds_5.project_id = 278964)
                           ->  Bitmap Index Scan using ci_builds_104_commit_id_type_ref_idx  (cost=0.00..8749542.73 rows=51471624 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (((p_ci_builds_5.type)::text = 'Ci::Build'::text) AND ((p_ci_builds_5.ref)::text = 'master'::text))
               ->  Bitmap Heap Scan on gitlab_partitions_dynamic.ci_builds_105 p_ci_builds_6  (cost=7792635.30..9717188.58 rows=910120 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                     ->  BitmapAnd  (cost=7792635.30..7792635.30 rows=910120 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                           ->  Bitmap Index Scan using ci_builds_105_project_id_id_idx  (cost=0.00..121108.97 rows=8615587 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (p_ci_builds_6.project_id = 278964)
                           ->  Bitmap Index Scan using ci_builds_105_commit_id_type_ref_idx  (cost=0.00..7671071.01 rows=41844621 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (((p_ci_builds_6.type)::text = 'Ci::Build'::text) AND ((p_ci_builds_6.ref)::text = 'master'::text))
               ->  Bitmap Heap Scan on gitlab_partitions_dynamic.ci_builds_106 p_ci_builds_7  (cost=8016820.23..9761258.69 rows=838729 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                     ->  BitmapAnd  (cost=8016820.23..8016820.23 rows=838729 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                           ->  Bitmap Index Scan using ci_builds_106_project_id_id_idx  (cost=0.00..118531.06 rows=8161265 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (p_ci_builds_7.project_id = 278964)
                           ->  Bitmap Index Scan using ci_builds_106_commit_id_type_ref_idx  (cost=0.00..7897869.55 rows=42560686 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (((p_ci_builds_7.type)::text = 'Ci::Build'::text) AND ((p_ci_builds_7.ref)::text = 'master'::text))
               ->  Bitmap Heap Scan on gitlab_partitions_dynamic.ci_builds_107 p_ci_builds_8  (cost=11937184.50..14835741.27 rows=1373571 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                     ->  BitmapAnd  (cost=11937184.50..11937184.50 rows=1373571 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                           ->  Bitmap Index Scan using ci_builds_107_project_id_id_idx  (cost=0.00..176723.77 rows=13063092 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (p_ci_builds_8.project_id = 278964)
                           ->  Bitmap Index Scan using ci_builds_107_commit_id_type_ref_idx  (cost=0.00..11759773.70 rows=65170200 width=0) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (((p_ci_builds_8.type)::text = 'Ci::Build'::text) AND ((p_ci_builds_8.ref)::text = 'master'::text))
               ->  Seq Scan on gitlab_partitions_dynamic.ci_builds_108 p_ci_builds_9  (cost=0.00..0.00 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                     Filter: (((p_ci_builds_9.type)::text = 'Ci::Build'::text) AND (p_ci_builds_9.project_id = 278964) AND ((p_ci_builds_9.ref)::text = 'master'::text))
                     Rows Removed by Filter: 0
         ->  Memoize  (cost=0.59..12.12 rows=7 width=16) (actual time=0.165..0.165 rows=1 loops=100)
               Buffers: shared hit=40 read=18
               ->  Append  (cost=0.58..12.11 rows=7 width=16) (actual time=0.740..0.740 rows=1 loops=14)
                     Buffers: shared hit=40 read=18
                     ->  Index Only Scan using ci_pipelines_pkey on gitlab_partitions_dynamic.ci_pipelines p_ci_pipelines_1  (cost=0.58..2.02 rows=1 width=16) (actual time=0.734..0.734 rows=1 loops=14)
                           Index Cond: ((p_ci_pipelines_1.id = p_ci_builds.commit_id) AND (p_ci_pipelines_1.partition_id = p_ci_builds.partition_id))
                           Heap Fetches: 0
                           Buffers: shared hit=40 read=18
                     ->  Index Only Scan using ci_pipelines_103_pkey on gitlab_partitions_dynamic.ci_pipelines_103 p_ci_pipelines_2  (cost=0.56..2.01 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_pipelines_2.id = p_ci_builds.commit_id) AND (p_ci_pipelines_2.partition_id = p_ci_builds.partition_id))
                           Heap Fetches: 0
                     ->  Index Only Scan using ci_pipelines_104_pkey on gitlab_partitions_dynamic.ci_pipelines_104 p_ci_pipelines_3  (cost=0.57..2.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_pipelines_3.id = p_ci_builds.commit_id) AND (p_ci_pipelines_3.partition_id = p_ci_builds.partition_id))
                           Heap Fetches: 0
                     ->  Index Only Scan using ci_pipelines_105_pkey on gitlab_partitions_dynamic.ci_pipelines_105 p_ci_pipelines_4  (cost=0.56..2.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_pipelines_4.id = p_ci_builds.commit_id) AND (p_ci_pipelines_4.partition_id = p_ci_builds.partition_id))
                           Heap Fetches: 0
                     ->  Index Only Scan using ci_pipelines_106_pkey on gitlab_partitions_dynamic.ci_pipelines_106 p_ci_pipelines_5  (cost=0.57..1.98 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_pipelines_5.id = p_ci_builds.commit_id) AND (p_ci_pipelines_5.partition_id = p_ci_builds.partition_id))
                           Heap Fetches: 0
                     ->  Index Only Scan using ci_pipelines_107_pkey on gitlab_partitions_dynamic.ci_pipelines_107 p_ci_pipelines_6  (cost=0.57..2.07 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_pipelines_6.id = p_ci_builds.commit_id) AND (p_ci_pipelines_6.partition_id = p_ci_builds.partition_id))
                           Heap Fetches: 0
                     ->  Seq Scan on gitlab_partitions_dynamic.ci_pipelines_108 p_ci_pipelines_7  (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
                           Filter: ((p_ci_builds.commit_id = p_ci_pipelines_7.id) AND (p_ci_builds.partition_id = p_ci_pipelines_7.partition_id))
                           Rows Removed by Filter: 0
Settings: jit = 'off', seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '338688MB', random_page_cost = '1.5'

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Pedro Pombeiro

Merge request reports