Skip to content

Disable partition pruning for UnlockPipelinesInQueueWorker

Marius Bobin requested to merge 435737-mb-fix-plan into master

What does this MR do and why?

In extremely rare cases the plans for this query are flipped and we can get the correct plans by querying all partitions:

Before: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/24901/commands/79090

After:

gitlabhq_dblab=# explain(analyze, buffers) SELECT "p_ci_builds"."id" FROM "p_ci_builds" WHERE "p_ci_builds"."type" = 'Ci::Build' AND "p_ci_builds"."commit_id" = 1112182921 ORDER BY "p_ci_builds"."id" ASC LIMIT 1;
                                                                                             QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=7573.83..7573.83 rows=1 width=8) (actual time=46.170..46.174 rows=1 loops=1)
   Buffers: shared hit=3 read=11
   I/O Timings: read=45.800
   ->  Sort  (cost=7573.83..7591.43 rows=7042 width=8) (actual time=46.168..46.170 rows=1 loops=1)
         Sort Key: p_ci_builds.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3 read=11
         I/O Timings: read=45.800
         ->  Append  (cost=0.71..7538.62 rows=7042 width=8) (actual time=40.004..46.096 rows=2 loops=1)
               Buffers: shared read=11
               I/O Timings: read=45.800
               ->  Index Scan using index_ci_builds_on_commit_id_and_status_and_type on ci_builds p_ci_builds_1  (cost=0.71..2281.13 rows=1672 width=8) (actual time=20.333..20.333 rows=0 loops=1)
                     Index Cond: ((commit_id = 1112182921) AND ((type)::text = 'Ci::Build'::text))
                     Buffers: shared read=5
                     I/O Timings: read=20.221
               ->  Index Scan using ci_builds_101_commit_id_type_ref_idx on ci_builds_101 p_ci_builds_2  (cost=0.56..5222.28 rows=5370 width=8) (actual time=19.665..25.751 rows=2 loops=1)
                     Index Cond: ((commit_id = 1112182921) AND ((type)::text = 'Ci::Build'::text))
                     Buffers: shared read=6
                     I/O Timings: read=25.579
 Planning Time: 1.061 ms
 Execution Time: 46.277 ms
(21 rows

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.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before After

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

Related to #435737 (closed)

Edited by Marius Bobin

Merge request reports

Loading