Partition pruning for build relation of Ci::UnlockPipelineService (!210469) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Partition pruning in build relation in CTE for Ci::UnlockPipelineService

This is to ensure planner choose index p_ci_builds_commit_id_type_ref_idx over the other indexes.

Below queries is about the pipeline that has 0 builds and 65k generic commit statuses (pipeline_id: 57861266).

the query plan before the change

Click to expand

gitlabhq_dblab=# EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, SETTINGS TRUE, WAL)
gitlabhq_dblab-# WITH "cte_builds" AS MATERIALIZED (
gitlabhq_dblab(#   SELECT
gitlabhq_dblab(#     "p_ci_builds"."id"
gitlabhq_dblab(#   FROM
gitlabhq_dblab(#     "p_ci_builds"
gitlabhq_dblab(#   WHERE
gitlabhq_dblab(#     "p_ci_builds"."type" = 'Ci::Build'
gitlabhq_dblab(#     AND "p_ci_builds"."commit_id" = 57861266
gitlabhq_dblab(# )
gitlabhq_dblab-# SELECT
gitlabhq_dblab-#   "p_ci_builds"."id"
gitlabhq_dblab-# FROM
gitlabhq_dblab-#   "cte_builds" AS "p_ci_builds"
gitlabhq_dblab-# ORDER BY
gitlabhq_dblab-#   "p_ci_builds"."id" ASC
gitlabhq_dblab-# LIMIT
gitlabhq_dblab-#   1;
                                                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=147109.70..147109.70 rows=1 width=8) (actual time=34.930..34.937 rows=0 loops=1)
  Output: p_ci_builds.id
  Buffers: shared hit=6 read=33
  I/O Timings: shared read=34.677
  CTE cte_builds
    ->  Append  (cost=0.71..144461.20 rows=105940 width=8) (actual time=34.892..34.897 rows=0 loops=1)
          Buffers: shared hit=3 read=33
          I/O Timings: shared read=34.677
          ->  Index Scan using index_ci_builds_on_commit_id_and_type_and_ref on gitlab_partitions_dynamic.ci_builds p_ci_builds_2  (cost=0.71..142688.53 rows=104614 width=8) (actual time=0.421..0.421 rows=0 loops=1)
                Output: p_ci_builds_2.id
                Index Cond: ((p_ci_builds_2.commit_id = 57861266) AND ((p_ci_builds_2.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=3 read=5
                I/O Timings: shared read=0.397
          ->  Index Scan using ci_builds_101_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_101 p_ci_builds_3  (cost=0.58..279.14 rows=274 width=8) (actual time=2.647..2.647 rows=0 loops=1)
                Output: p_ci_builds_3.id
                Index Cond: ((p_ci_builds_3.commit_id = 57861266) AND ((p_ci_builds_3.type)::text = 'Ci::Build'::text))
                Buffers: shared read=4
                I/O Timings: shared read=2.617
          ->  Index Scan using ci_builds_102_commit_id_convert_to_bigint_type_ref_idx on gitlab_partitions_dynamic.ci_builds_102 p_ci_builds_4  (cost=0.58..506.89 rows=530 width=8) (actual time=8.439..8.440 rows=0 loops=1)
                Output: p_ci_builds_4.id
                Index Cond: ((p_ci_builds_4.commit_id = 57861266) AND ((p_ci_builds_4.type)::text = 'Ci::Build'::text))
                Buffers: shared read=4
                I/O Timings: shared read=8.417
          ->  Index Scan using ci_builds_103_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_103 p_ci_builds_5  (cost=0.57..102.91 rows=116 width=8) (actual time=1.539..1.539 rows=0 loops=1)
                Output: p_ci_builds_5.id
                Index Cond: ((p_ci_builds_5.commit_id = 57861266) AND ((p_ci_builds_5.type)::text = 'Ci::Build'::text))
                Buffers: shared read=4
                I/O Timings: shared read=1.519
          ->  Index Scan using ci_builds_104_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_104 p_ci_builds_6  (cost=0.57..101.98 rows=117 width=8) (actual time=2.626..2.626 rows=0 loops=1)
                Output: p_ci_builds_6.id
                Index Cond: ((p_ci_builds_6.commit_id = 57861266) AND ((p_ci_builds_6.type)::text = 'Ci::Build'::text))
                Buffers: shared read=4
                I/O Timings: shared read=2.602
          ->  Index Scan using ci_builds_105_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_105 p_ci_builds_7  (cost=0.57..85.70 rows=98 width=8) (actual time=8.878..8.878 rows=0 loops=1)
                Output: p_ci_builds_7.id
                Index Cond: ((p_ci_builds_7.commit_id = 57861266) AND ((p_ci_builds_7.type)::text = 'Ci::Build'::text))
                Buffers: shared read=4
                I/O Timings: shared read=8.856
          ->  Index Scan using ci_builds_106_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_106 p_ci_builds_8  (cost=0.57..100.90 rows=113 width=8) (actual time=2.638..2.638 rows=0 loops=1)
                Output: p_ci_builds_8.id
                Index Cond: ((p_ci_builds_8.commit_id = 57861266) AND ((p_ci_builds_8.type)::text = 'Ci::Build'::text))
                Buffers: shared read=4
                I/O Timings: shared read=2.617
          ->  Index Scan using ci_builds_107_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_107 p_ci_builds_9  (cost=0.57..65.45 rows=77 width=8) (actual time=7.677..7.678 rows=0 loops=1)
                Output: p_ci_builds_9.id
                Index Cond: ((p_ci_builds_9.commit_id = 57861266) AND ((p_ci_builds_9.type)::text = 'Ci::Build'::text))
                Buffers: shared read=4
                I/O Timings: shared read=7.652
          ->  Seq Scan on gitlab_partitions_dynamic.ci_builds_108 p_ci_builds_10  (cost=0.00..0.00 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=1)
                Output: p_ci_builds_10.id
                Filter: (((p_ci_builds_10.type)::text = 'Ci::Build'::text) AND (p_ci_builds_10.commit_id = 57861266))
  ->  Sort  (cost=2648.50..2913.35 rows=105940 width=8) (actual time=34.928..34.930 rows=0 loops=1)
        Output: p_ci_builds.id
        Sort Key: p_ci_builds.id
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=6 read=33
        I/O Timings: shared read=34.677
        ->  CTE Scan on cte_builds p_ci_builds  (cost=0.00..2118.80 rows=105940 width=8) (actual time=34.893..34.894 rows=0 loops=1)
              Output: p_ci_builds.id
              Buffers: shared hit=3 read=33
              I/O Timings: shared read=34.677
Settings: effective_cache_size = '338688MB', random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB'
Query Identifier: -2324212643672950759
Planning:
  Buffers: shared hit=5597 read=746 dirtied=31
  I/O Timings: shared read=410.059
Planning Time: 464.632 ms
Execution Time: 35.082 ms
(68 rows)

the query plan after the change, see https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/45156/commands/138532

Below queries is about the pipeline that has 2 builds and 186k generic commit statuses (pipeline_id: 1821178317).

the query plan before the change

Click to expand

gitlabhq_dblab=# EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, SETTINGS TRUE, WAL)
gitlabhq_dblab-# WITH "cte_builds" AS MATERIALIZED (
gitlabhq_dblab(#   SELECT
gitlabhq_dblab(#     "p_ci_builds"."id"
gitlabhq_dblab(#   FROM
gitlabhq_dblab(#     "p_ci_builds"
gitlabhq_dblab(#   WHERE
gitlabhq_dblab(#     "p_ci_builds"."type" = 'Ci::Build'
gitlabhq_dblab(#     AND "p_ci_builds"."commit_id" = 1821178317
gitlabhq_dblab(# )
gitlabhq_dblab-# SELECT
gitlabhq_dblab-#   "p_ci_builds"."id"
gitlabhq_dblab-# FROM
gitlabhq_dblab-#   "cte_builds" AS "p_ci_builds"
gitlabhq_dblab-# ORDER BY
gitlabhq_dblab-#   "p_ci_builds"."id" ASC
gitlabhq_dblab-# LIMIT
gitlabhq_dblab-#   1;
                                                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=162200.42..162200.42 rows=1 width=8) (actual time=46.555..46.561 rows=1 loops=1)
  Output: p_ci_builds.id
  Buffers: shared hit=19 read=180
  I/O Timings: shared read=45.936
  CTE cte_builds
    ->  Append  (cost=0.71..157727.99 rows=178897 width=8) (actual time=33.714..46.546 rows=2 loops=1)
          Buffers: shared hit=19 read=180
          I/O Timings: shared read=45.936
          ->  Index Scan using index_ci_builds_on_commit_id_and_type_and_ref on gitlab_partitions_dynamic.ci_builds p_ci_builds_2  (cost=0.71..1712.73 rows=1255 width=8) (actual time=17.909..17.910 rows=0 loops=1)
                Output: p_ci_builds_2.id
                Index Cond: ((p_ci_builds_2.commit_id = 1821178317) AND ((p_ci_builds_2.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=1 read=4
                I/O Timings: shared read=17.880
          ->  Index Scan using ci_builds_101_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_101 p_ci_builds_3  (cost=0.58..279.14 rows=274 width=8) (actual time=4.413..4.413 rows=0 loops=1)
                Output: p_ci_builds_3.id
                Index Cond: ((p_ci_builds_3.commit_id = 1821178317) AND ((p_ci_builds_3.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=1 read=3
                I/O Timings: shared read=4.388
          ->  Index Scan using ci_builds_102_commit_id_convert_to_bigint_type_ref_idx on gitlab_partitions_dynamic.ci_builds_102 p_ci_builds_4  (cost=0.58..506.89 rows=530 width=8) (actual time=7.643..7.643 rows=0 loops=1)
                Output: p_ci_builds_4.id
                Index Cond: ((p_ci_builds_4.commit_id = 1821178317) AND ((p_ci_builds_4.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=1 read=3
                I/O Timings: shared read=7.623
          ->  Index Scan using ci_builds_103_commit_id_status_type_idx on gitlab_partitions_dynamic.ci_builds_103 p_ci_builds_5  (cost=0.57..153980.71 rows=176432 width=8) (actual time=3.745..16.525 rows=2 loops=1)
                Output: p_ci_builds_5.id
                Index Cond: ((p_ci_builds_5.commit_id = 1821178317) AND ((p_ci_builds_5.type)::text = 'Ci::Build'::text))
                Buffers: shared read=170
                I/O Timings: shared read=16.045
          ->  Index Scan using ci_builds_104_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_104 p_ci_builds_6  (cost=0.57..101.98 rows=117 width=8) (actual time=0.017..0.017 rows=0 loops=1)
                Output: p_ci_builds_6.id
                Index Cond: ((p_ci_builds_6.commit_id = 1821178317) AND ((p_ci_builds_6.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=4
          ->  Index Scan using ci_builds_105_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_105 p_ci_builds_7  (cost=0.57..85.70 rows=98 width=8) (actual time=0.007..0.007 rows=0 loops=1)
                Output: p_ci_builds_7.id
                Index Cond: ((p_ci_builds_7.commit_id = 1821178317) AND ((p_ci_builds_7.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=4
          ->  Index Scan using ci_builds_106_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_106 p_ci_builds_8  (cost=0.57..100.90 rows=113 width=8) (actual time=0.006..0.006 rows=0 loops=1)
                Output: p_ci_builds_8.id
                Index Cond: ((p_ci_builds_8.commit_id = 1821178317) AND ((p_ci_builds_8.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=4
          ->  Index Scan using ci_builds_107_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_107 p_ci_builds_9  (cost=0.57..65.45 rows=77 width=8) (actual time=0.007..0.007 rows=0 loops=1)
                Output: p_ci_builds_9.id
                Index Cond: ((p_ci_builds_9.commit_id = 1821178317) AND ((p_ci_builds_9.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=4
          ->  Seq Scan on gitlab_partitions_dynamic.ci_builds_108 p_ci_builds_10  (cost=0.00..0.00 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)
                Output: p_ci_builds_10.id
                Filter: (((p_ci_builds_10.type)::text = 'Ci::Build'::text) AND (p_ci_builds_10.commit_id = 1821178317))
  ->  Sort  (cost=4472.43..4919.67 rows=178897 width=8) (actual time=46.554..46.555 rows=1 loops=1)
        Output: p_ci_builds.id
        Sort Key: p_ci_builds.id
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=19 read=180
        I/O Timings: shared read=45.936
        ->  CTE Scan on cte_builds p_ci_builds  (cost=0.00..3577.94 rows=178897 width=8) (actual time=33.717..46.548 rows=2 loops=1)
              Output: p_ci_builds.id
              Buffers: shared hit=19 read=180
              I/O Timings: shared read=45.936
Settings: effective_cache_size = '338688MB', random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB'
Query Identifier: -2324212643672950759
Planning:
  Buffers: shared hit=28
Planning Time: 1.569 ms
Execution Time: 46.635 ms
(63 rows)

the query plan after the change, see https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/45156/commands/138540

Here is a comparison of a pipeline that has around 60k builds and 0 generic commit statuses (pipeline_id: 88503022).

the query plan before the change

Click to expand

gitlabhq_dblab=# EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, SETTINGS TRUE, WAL)
gitlabhq_dblab-# WITH "cte_builds" AS MATERIALIZED (
gitlabhq_dblab(#   SELECT
gitlabhq_dblab(#     "p_ci_builds"."id"
gitlabhq_dblab(#   FROM
gitlabhq_dblab(#     "p_ci_builds"
gitlabhq_dblab(#   WHERE
gitlabhq_dblab(#     "p_ci_builds"."type" = 'Ci::Build'
gitlabhq_dblab(#     AND "p_ci_builds"."commit_id" = 88503022
gitlabhq_dblab(# )
gitlabhq_dblab-# SELECT
gitlabhq_dblab-#   "p_ci_builds"."id"
gitlabhq_dblab-# FROM
gitlabhq_dblab-#   "cte_builds" AS "p_ci_builds"
gitlabhq_dblab-# ORDER BY
gitlabhq_dblab-#   "p_ci_builds"."id" ASC
gitlabhq_dblab-# LIMIT
gitlabhq_dblab-#   1;
                                                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=3033.14..3033.14 rows=1 width=8) (actual time=1512.138..1512.143 rows=1 loops=1)
  Output: p_ci_builds.id
  Buffers: shared hit=30 read=59293
  I/O Timings: shared read=1369.873
  CTE cte_builds
    ->  Append  (cost=0.71..2968.61 rows=2581 width=8) (actual time=0.395..1488.519 rows=60000 loops=1)
          Buffers: shared hit=30 read=59293
          I/O Timings: shared read=1369.873
          ->  Index Scan using index_ci_builds_on_commit_id_and_type_and_ref on gitlab_partitions_dynamic.ci_builds p_ci_builds_2  (cost=0.71..1712.73 rows=1255 width=8) (actual time=0.394..1482.402 rows=60000 loops=1)
                Output: p_ci_builds_2.id
                Index Cond: ((p_ci_builds_2.commit_id = 88503022) AND ((p_ci_builds_2.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=2 read=59293
                I/O Timings: shared read=1369.873
          ->  Index Scan using ci_builds_101_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_101 p_ci_builds_3  (cost=0.58..279.14 rows=274 width=8) (actual time=0.022..0.022 rows=0 loops=1)
                Output: p_ci_builds_3.id
                Index Cond: ((p_ci_builds_3.commit_id = 88503022) AND ((p_ci_builds_3.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=4
          ->  Index Scan using ci_builds_102_commit_id_convert_to_bigint_type_ref_idx on gitlab_partitions_dynamic.ci_builds_102 p_ci_builds_4  (cost=0.58..506.89 rows=530 width=8) (actual time=0.019..0.019 rows=0 loops=1)
                Output: p_ci_builds_4.id
                Index Cond: ((p_ci_builds_4.commit_id = 88503022) AND ((p_ci_builds_4.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=4
          ->  Index Scan using ci_builds_103_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_103 p_ci_builds_5  (cost=0.57..102.91 rows=116 width=8) (actual time=0.006..0.006 rows=0 loops=1)
                Output: p_ci_builds_5.id
                Index Cond: ((p_ci_builds_5.commit_id = 88503022) AND ((p_ci_builds_5.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=4
          ->  Index Scan using ci_builds_104_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_104 p_ci_builds_6  (cost=0.57..101.98 rows=117 width=8) (actual time=0.006..0.006 rows=0 loops=1)
                Output: p_ci_builds_6.id
                Index Cond: ((p_ci_builds_6.commit_id = 88503022) AND ((p_ci_builds_6.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=4
          ->  Index Scan using ci_builds_105_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_105 p_ci_builds_7  (cost=0.57..85.70 rows=98 width=8) (actual time=0.006..0.006 rows=0 loops=1)
                Output: p_ci_builds_7.id
                Index Cond: ((p_ci_builds_7.commit_id = 88503022) AND ((p_ci_builds_7.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=4
          ->  Index Scan using ci_builds_106_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_106 p_ci_builds_8  (cost=0.57..100.90 rows=113 width=8) (actual time=0.010..0.010 rows=0 loops=1)
                Output: p_ci_builds_8.id
                Index Cond: ((p_ci_builds_8.commit_id = 88503022) AND ((p_ci_builds_8.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=4
          ->  Index Scan using ci_builds_107_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_107 p_ci_builds_9  (cost=0.57..65.45 rows=77 width=8) (actual time=0.008..0.008 rows=0 loops=1)
                Output: p_ci_builds_9.id
                Index Cond: ((p_ci_builds_9.commit_id = 88503022) AND ((p_ci_builds_9.type)::text = 'Ci::Build'::text))
                Buffers: shared hit=4
          ->  Seq Scan on gitlab_partitions_dynamic.ci_builds_108 p_ci_builds_10  (cost=0.00..0.00 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)
                Output: p_ci_builds_10.id
                Filter: (((p_ci_builds_10.type)::text = 'Ci::Build'::text) AND (p_ci_builds_10.commit_id = 88503022))
  ->  Sort  (cost=64.53..70.98 rows=2581 width=8) (actual time=1512.137..1512.138 rows=1 loops=1)
        Output: p_ci_builds.id
        Sort Key: p_ci_builds.id
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=30 read=59293
        I/O Timings: shared read=1369.873
        ->  CTE Scan on cte_builds p_ci_builds  (cost=0.00..51.62 rows=2581 width=8) (actual time=0.398..1505.547 rows=60000 loops=1)
              Output: p_ci_builds.id
              Buffers: shared hit=30 read=59293
              I/O Timings: shared read=1369.873
Settings: effective_cache_size = '338688MB', random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB'
Query Identifier: -2324212643672950759
Planning:
  Buffers: shared hit=28
Planning Time: 1.946 ms
Execution Time: 1512.905 ms
(60 rows)

the query plan after the change, see https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/45156/commands/138533

References

Screenshots or screen recordings

Before	After

How to set up and validate locally

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.

Related to #578388

Edited Nov 03, 2025 by Tianwen Chen

Partition pruning for build relation of Ci::UnlockPipelineService

What does this MR do and why?

References

Screenshots or screen recordings

How to set up and validate locally

MR acceptance checklist

Merge request reports