Update default value for code_suggestions settings
What does this MR do and why?
This sets the default value for code_suggestions on the namespace_settings
table. The change is required to allow users to have more control over the
code_suggestions
feature. It also allows the owning group to disable the
feature for all members.
- For group namespace, the default is true.
- For user namespace, the default is false.
- For project namespace, it is left untouched.
🐘 Databases
Sub-Batch Update Query
Query
UPDATE
"namespace_settings"
SET
"code_suggestions" = TRUE
WHERE
"namespace_settings"."namespace_id" IN (
SELECT
"namespace_settings"."namespace_id"
FROM
"namespace_settings"
INNER JOIN namespaces ON namespaces.id = namespace_settings.namespace_id
WHERE
"namespace_settings"."namespace_id" BETWEEN 1
AND 100
AND "namespaces"."type" = 'Group'
)
Plan with Execution
ModifyTable on public.namespace_settings (cost=445.02..475.77 rows=3 width=137) (actual time=64.383..64.391 rows=0 loops=1)
Buffers: shared hit=408 read=57
I/O Timings: read=62.233 write=0.000
-> Nested Loop (cost=445.02..475.77 rows=3 width=137) (actual time=64.381..64.387 rows=0 loops=1)
Buffers: shared hit=408 read=57
I/O Timings: read=62.233 write=0.000
-> HashAggregate (cost=444.59..444.68 rows=9 width=20) (actual time=64.380..64.385 rows=0 loops=1)
Group Key: namespace_settings_1.namespace_id
Buffers: shared hit=408 read=57
I/O Timings: read=62.233 write=0.000
-> Nested Loop (cost=1.00..444.56 rows=9 width=20) (actual time=64.377..64.381 rows=0 loops=1)
Buffers: shared hit=408 read=57
I/O Timings: read=62.233 write=0.000
-> Index Scan using namespace_settings_pkey on public.namespace_settings namespace_settings_1 (cost=0.44..96.82 rows=97 width=10) (actual time=3.369..4.108 rows=91 loops=1)
Index Cond: ((namespace_settings_1.namespace_id >= 1) AND (namespace_settings_1.namespace_id <= 100))
Buffers: shared hit=8 read=2
I/O Timings: read=3.910 write=0.000
-> Index Scan using namespaces_pkey on public.namespaces (cost=0.56..3.58 rows=1 width=10) (actual time=0.660..0.660 rows=0 loops=91)
Index Cond: (namespaces.id = namespace_settings_1.namespace_id)
Filter: ((namespaces.type)::text = 'Group'::text)
Rows Removed by Filter: 1
Buffers: shared hit=400 read=55
I/O Timings: read=58.323 write=0.000
-> Index Scan using namespace_settings_pkey on public.namespace_settings (cost=0.44..3.46 rows=1 width=116) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespace_settings.namespace_id = namespaces.id)
I/O Timings: read=0.000 write=0.000
Summary
Time: 66.462 ms
- planning: 1.841 ms
- execution: 64.621 ms
- I/O read: 62.233 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 408 (~3.20 MiB) from the buffer pool
- reads: 57 (~456.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/18711/commands/62127.
Rows affected
explain SELECT id FROM "namespaces" WHERE "namespaces"."type" in ('Group', 'User')
Index Only Scan using index_namespaces_on_type_and_id on public.namespaces (cost=0.56..460944.87 rows=18155043 width=4) (actual time=0.113..4821.061 rows=18131546 loops=1)
Index Cond: (namespaces.type = ANY ('{Group,User}'::text[]))
Heap Fetches: 613272
Buffers: shared hit=6087924 read=1597
I/O Timings: read=123.165 write=0.000
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/18711/commands/62128
~18 mil (18,155,043)
Estimated runtime
6 h 38 min 46 s
Breakdown:
- per sub-batch (2 queries): 66.462 ms * 2 = 132.92 ms
- per batch (500 sub-batches per batch): 132.92 ms * 500 = 66,460 ms
- total (360 batches required to process 18M records): 66,460 ms * 360 = 23,925,600 ms
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
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.
Related to #411936 (closed)
Edited by Tan Le