Fix any label filter with custom sorting
What does this MR do?
It fixes SQL query for issuable finder with custom sorting.
E.g. milestone_due_date
or review_time
.
Finder query plans
Query Plan for =Any with custom sorting
MergeRequestsFinder.new(User.first,
project_id: 278964,
label_name: ['Any'],
sort: 'milestone_due_asc',
state: 'opened').execute.to_sql
explain SELECT “merge_requests”.* FROM “merge_requests” INNER JOIN “label_links” ON “label_links”.“target_id” = “merge_requests”.“id” AND “label_links”.“target_type” = ‘MergeRequest’ LEFT OUTER JOIN milestones ON merge_requests.milestone_id = milestones.id WHERE “merge_requests”.“target_project_id” = 278964 AND (“merge_requests”.“state_id” IN (1)) GROUP BY “merge_requests”.“id”, “milestones”.“id”, “milestones”.“due_date” ORDER BY milestones.due_date IS NULL, milestones.id IS NULL, milestones.due_date ASC, “merge_requests”.“id” DESC
Time: 56.568 ms
- planning: 1.160 ms
- execution: 55.408 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 30160 (~235.60 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Sort (cost=5730.37..5730.82 rows=182 width=739) (actual time=54.648..54.887 rows=1162 loops=1)
Sort Key: ((milestones.due_date IS NULL)), ((milestones.id IS NULL)), milestones.due_date, merge_requests.id DESC
Sort Method: quicksort Memory: 1573kB
Buffers: shared hit=30160
-> Group (cost=5722.17..5723.54 rows=182 width=739) (actual time=41.398..45.550 rows=1162 loops=1)
Group Key: merge_requests.id, milestones.id
Buffers: shared hit=30160
-> Sort (cost=5722.17..5722.63 rows=182 width=737) (actual time=41.392..43.877 rows=7384 loops=1)
Sort Key: merge_requests.id DESC, milestones.id
Sort Method: quicksort Memory: 9599kB
Buffers: shared hit=30160
-> Nested Loop Left Join (cost=1.42..5715.34 rows=182 width=737) (actual time=0.033..28.138 rows=7384 loops=1)
Buffers: shared hit=30160
-> Nested Loop (cost=0.99..4972.95 rows=182 width=729) (actual time=0.031..12.770 rows=7384 loops=1)
Buffers: shared hit=7828
-> Index Scan using idx_merge_requests_on_target_project_id_and_iid_opened on public.merge_requests (cost=0.43..2042.60 rows=1130 width=729) (actual time=0.016..2.640 rows=1171 loops=1)
Index Cond: (merge_requests.target_project_id = 278964)
Buffers: shared hit=1450
-> Index Only Scan using index_label_links_on_target_id_and_target_type on public.label_links (cost=0.56..2.58 rows=1 width=4) (actual time=0.005..0.007 rows=6 loops=1171)
Index Cond: ((label_links.target_id = merge_requests.id) AND (label_links.target_type = 'MergeRequest'::text))
Heap Fetches: 1006
Buffers: shared hit=6378
-> Index Scan using milestones_pkey on public.milestones (cost=0.42..4.07 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=7384)
Index Cond: (merge_requests.milestone_id = milestones.id)
Buffers: shared hit=22332
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
Label as security and @ mention @gitlab-com/gl-security/appsec
-
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team
Closes #216160 (closed)
Edited by Yorick Peterse