Skip to content

Added migrations for secret snippets

Francisco Javier López (ex-Gitlab) requested to merge fj-secret-snippet-migrations into master

What does this MR do?

In order to merge !19753 (closed) we need first to add a couple of migrations.

In this MR we add the migrations and some other basic code we will reuse.

The querie plans with the existing and the new indexes are:

select * from snippets where visibility_level=20

Existing index:

Index Scan using index_snippets_on_visibility_level on public.snippets  (cost=0.42..102925.74 rows=124308 width=1081) (actual time=51.819..171.309 rows=52556 loops=1)
  Index Cond: (snippets.visibility_level = 20)
  Buffers: shared hit=59300 read=163

Cost: 102925.74

Time: 177.479 ms
  - planning: 3.163 ms
  - execution: 174.316 ms
    - I/O read: 4.001 ms

Shared buffers:
  - hits: 59300 (~463.30 MiB) from the buffer pool
  - reads: 163 (~1.30 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

New Index:

Index Scan using index_snippets_on_visibility_level_and_secret on public.snippets  (cost=0.42..116308.28 rows=96178 width=1079) (actual time=0.192..104.372 rows=52556 loops=1)
  Index Cond: (snippets.visibility_level = 20)
  Buffers: shared hit=27363 read=15

Cost: 116308.28

Time: 110.727 ms
  - planning: 3.350 ms
  - execution: 107.377 ms
    - I/O read: 1.132 ms

Shared buffers:
  - hits: 27363 (~213.80 MiB) from the buffer pool
  - reads: 15 (~120.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

select * from snippets where visibility_level=20 and secret=true

Existing index:

Index Scan using index_snippets_on_visibility_level on public.snippets  (cost=0.42..77989.45 rows=47085 width=1079) (actual time=58.414..189.874 rows=47088 loops=1)
   Index Cond: (snippets.visibility_level = 20)
   Filter: snippets.secret
   Rows Removed by Filter: 5468
   Buffers: shared hit=61610

Cost: 77989.45

Time: 195.680 ms
  - planning: 3.050 ms
  - execution: 192.630 ms

Shared buffers:
  - hits: 61610 (~481.30 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

New Index:

Index Scan using index_snippets_on_visibility_level_and_secret on public.snippets  (cost=0.42..106590.98 rows=84561 width=1079) (actual time=0.128..204.389 rows=47088 loops=1)
  Index Cond: ((snippets.visibility_level = 20) AND (snippets.secret = true))
  Filter: snippets.secret
  Rows Removed by Filter: 0
  Buffers: shared dirtied=130 hit=43857 read=254

Cost: 106590.98

Time: 116.125 ms
  - planning: 4.465 ms
  - execution: 111.660 ms

Shared buffers:
  - hits: 22504 (~175.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Refs #14201

Does this MR meet the acceptance criteria?

Conformity

Edited by 🤖 GitLab Bot 🤖

Merge request reports