Skip ORDER BY to improve query performance
What does this MR do and why?
This change improves the query performance for a slow query.
Before
SELECT
"security_findings".*
FROM
"security_findings"
INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id"
WHERE
"security_scans"."pipeline_id" = 832983315
ORDER BY
"security_findings"."id" ASC
LIMIT 1https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/17711/commands/59141
Limit (cost=108221.11..108221.11 rows=1 width=1113) (actual time=14793.566..14793.580 rows=1 loops=1)
Buffers: shared hit=2544 read=17843 dirtied=11378
I/O Timings: read=14457.550 write=0.000
-> Sort (cost=108221.11..108221.48 rows=146 width=1113) (actual time=14793.564..14793.577 rows=1 loops=1)
Sort Key: security_findings_42.id
Sort Method: top-N heapsort Memory: 27kB
Buffers: shared hit=2544 read=17843 dirtied=11378
I/O Timings: read=14457.550 write=0.000
-> Nested Loop (cost=1.13..108220.38 rows=146 width=1113) (actual time=472.564..14711.169 rows=89413 loops=1)
Buffers: shared hit=2541 read=17843 dirtied=11378
I/O Timings: read=14457.550 write=0.000
-> Index Scan using index_security_scans_on_pipeline_id on public.security_scans (cost=0.56..5.65 rows=6 width=8) (actual time=39.233..43.017 rows=3 loops=1)
Index Cond: (security_scans.pipeline_id = 832983315)
Buffers: shared hit=3 read=7 dirtied=3
I/O Timings: read=40.434 write=0.000
-> Append (cost=0.57..17817.43 rows=21836 width=1113) (actual time=145.484..4870.861 rows=29804 loops=3)
Buffers: shared hit=2538 read=17836 dirtied=11375
I/O Timings: read=14417.117 write=0.000
-> Index Scan using security_findings_42_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_42 (cost=0.57..1302.85 rows=1607 width=1045) (actual time=15.804..15.805 rows=0 loops=3)
Index Cond: (security_findings_42.scan_id = security_scans.id)
Buffers: shared hit=8 read=4
I/O Timings: read=47.302 write=0.000
-> Index Scan using security_findings_43_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_43 (cost=0.57..2121.93 rows=2684 width=915) (actual time=6.296..6.296 rows=0 loops=3)
Index Cond: (security_findings_43.scan_id = security_scans.id)
Buffers: shared hit=8 read=4
I/O Timings: read=18.795 write=0.000
-> Index Scan using security_findings_44_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_44 (cost=0.57..984.51 rows=1204 width=1121) (actual time=9.469..9.469 rows=0 loops=3)
Index Cond: (security_findings_44.scan_id = security_scans.id)
Buffers: shared hit=8 read=4
I/O Timings: read=28.310 write=0.000
-> Index Scan using security_findings_45_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_45 (cost=0.57..1075.04 rows=1326 width=1136) (actual time=10.377..10.377 rows=0 loops=3)
Index Cond: (security_findings_45.scan_id = security_scans.id)
Buffers: shared hit=8 read=4
I/O Timings: read=31.040 write=0.000
-> Index Scan using security_findings_46_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_46 (cost=0.57..1133.33 rows=1398 width=1132) (actual time=8.019..8.019 rows=0 loops=3)
Index Cond: (security_findings_46.scan_id = security_scans.id)
Buffers: shared hit=8 read=4
I/O Timings: read=23.983 write=0.000
-> Index Scan using security_findings_47_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_47 (cost=0.57..1200.00 rows=1484 width=1123) (actual time=12.260..12.260 rows=0 loops=3)
Index Cond: (security_findings_47.scan_id = security_scans.id)
Buffers: shared hit=8 read=4
I/O Timings: read=36.705 write=0.000
-> Index Scan using security_findings_48_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_48 (cost=0.57..1047.30 rows=1293 width=1137) (actual time=11.168..11.168 rows=0 loops=3)
Index Cond: (security_findings_48.scan_id = security_scans.id)
Buffers: shared hit=8 read=4
I/O Timings: read=33.417 write=0.000
-> Index Scan using security_findings_49_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_49 (cost=0.57..1167.91 rows=1446 width=1127) (actual time=8.114..8.114 rows=0 loops=3)
Index Cond: (security_findings_49.scan_id = security_scans.id)
Buffers: shared hit=8 read=4
I/O Timings: read=24.263 write=0.000
-> Index Scan using security_findings_50_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_50 (cost=0.57..1198.19 rows=1482 width=1131) (actual time=8.042..8.042 rows=0 loops=3)
Index Cond: (security_findings_50.scan_id = security_scans.id)
Buffers: shared hit=8 read=4
I/O Timings: read=24.035 write=0.000
-> Index Scan using security_findings_51_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_51 (cost=0.57..1183.95 rows=1458 width=1119) (actual time=14.831..14.831 rows=0 loops=3)
Index Cond: (security_findings_51.scan_id = security_scans.id)
Buffers: shared hit=8 read=4
I/O Timings: read=44.395 write=0.000
-> Index Scan using security_findings_52_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_52 (cost=0.57..1300.09 rows=1601 width=1113) (actual time=12.605..12.605 rows=0 loops=3)
Index Cond: (security_findings_52.scan_id = security_scans.id)
Buffers: shared hit=8 read=4
I/O Timings: read=37.719 write=0.000
-> Index Scan using security_findings_53_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_53 (cost=0.57..1471.97 rows=1783 width=1168) (actual time=8.658..8.658 rows=0 loops=3)
Index Cond: (security_findings_53.scan_id = security_scans.id)
Buffers: shared hit=8 read=4
I/O Timings: read=25.889 write=0.000
-> Index Scan using security_findings_54_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_54 (cost=0.57..1367.99 rows=1659 width=1202) (actual time=9.885..9.885 rows=0 loops=3)
Index Cond: (security_findings_54.scan_id = security_scans.id)
Buffers: shared hit=8 read=4
I/O Timings: read=29.573 write=0.000
-> Index Scan using security_findings_55_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_55 (cost=0.56..1153.19 rows=1411 width=1232) (actual time=9.867..4730.018 rows=29804 loops=3)
Index Cond: (security_findings_55.scan_id = security_scans.id)
Buffers: shared hit=2434 read=17784 dirtied=11375
I/O Timings: read=14011.691 write=0.000モ time curl -s -o /dev/null -H "Authorization: Bearer $GITLAB_TOKEN" http://gdk.test:3000/api/v4/projects/29/vulnerability_findings
real 0m12.924s
user 0m0.005s
sys 0m0.009sAfter
SELECT
"security_findings".*
FROM
"security_findings"
INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id"
WHERE
"security_scans"."pipeline_id" = 832983315
LIMIT 1https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/17711/commands/59142
Limit (cost=1.13..742.36 rows=1 width=1113) (actual time=0.336..0.339 rows=1 loops=1)
Buffers: shared hit=65
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1.13..108220.38 rows=146 width=1113) (actual time=0.335..0.337 rows=1 loops=1)
Buffers: shared hit=65
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_security_scans_on_pipeline_id on public.security_scans (cost=0.56..5.65 rows=6 width=8) (actual time=0.070..0.070 rows=1 loops=1)
Index Cond: (security_scans.pipeline_id = 832983315)
Buffers: shared hit=8
I/O Timings: read=0.000 write=0.000
-> Append (cost=0.57..17817.43 rows=21836 width=1113) (actual time=0.261..0.263 rows=1 loops=1)
Buffers: shared hit=57
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_42_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_42 (cost=0.57..1302.85 rows=1607 width=1045) (actual time=0.019..0.019 rows=0 loops=1)
Index Cond: (security_findings_42.scan_id = security_scans.id)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_43_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_43 (cost=0.57..2121.93 rows=2684 width=915) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: (security_findings_43.scan_id = security_scans.id)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_44_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_44 (cost=0.57..984.51 rows=1204 width=1121) (actual time=0.020..0.020 rows=0 loops=1)
Index Cond: (security_findings_44.scan_id = security_scans.id)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_45_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_45 (cost=0.57..1075.04 rows=1326 width=1136) (actual time=0.018..0.018 rows=0 loops=1)
Index Cond: (security_findings_45.scan_id = security_scans.id)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_46_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_46 (cost=0.57..1133.33 rows=1398 width=1132) (actual time=0.019..0.019 rows=0 loops=1)
Index Cond: (security_findings_46.scan_id = security_scans.id)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_47_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_47 (cost=0.57..1200.00 rows=1484 width=1123) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: (security_findings_47.scan_id = security_scans.id)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_48_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_48 (cost=0.57..1047.30 rows=1293 width=1137) (actual time=0.021..0.021 rows=0 loops=1)
Index Cond: (security_findings_48.scan_id = security_scans.id)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_49_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_49 (cost=0.57..1167.91 rows=1446 width=1127) (actual time=0.013..0.013 rows=0 loops=1)
Index Cond: (security_findings_49.scan_id = security_scans.id)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_50_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_50 (cost=0.57..1198.19 rows=1482 width=1131) (actual time=0.028..0.028 rows=0 loops=1)
Index Cond: (security_findings_50.scan_id = security_scans.id)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_51_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_51 (cost=0.57..1183.95 rows=1458 width=1119) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: (security_findings_51.scan_id = security_scans.id)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_52_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_52 (cost=0.57..1300.09 rows=1601 width=1113) (actual time=0.020..0.021 rows=0 loops=1)
Index Cond: (security_findings_52.scan_id = security_scans.id)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_53_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_53 (cost=0.57..1471.97 rows=1783 width=1168) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: (security_findings_53.scan_id = security_scans.id)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_54_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_54 (cost=0.57..1367.99 rows=1659 width=1202) (actual time=0.020..0.020 rows=0 loops=1)
Index Cond: (security_findings_54.scan_id = security_scans.id)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_findings_55_scan_id_id_idx on gitlab_partitions_dynamic.security_findings_55 (cost=0.56..1153.19 rows=1411 width=1232) (actual time=0.020..0.020 rows=1 loops=1)
Index Cond: (security_findings_55.scan_id = security_scans.id)
Buffers: shared hit=5
I/O Timings: read=0.000 write=0.000モ time curl -s -o /dev/null -H "Authorization: Bearer $GITLAB_TOKEN" http://gdk.test:3000/api/v4/projects/29/vulnerability_findings
real 0m1.179s
user 0m0.006s
sys 0m0.010shttps://gitlab.com/gitlab-com/sec-sub-department/section-sec-request-for-help/-/issues/29
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by mo khan