Add unique index to system note metadata

In issue 1, the sequential scan was taking 250+ ms.

See #30885 (closed)

Confirmed that on GitLab.com, we don't have any duplicates at the moment:

gitlabhq_production=# SELECT id, COUNT(id) FROM system_note_metadata GROUP BY id HAVING COUNT(id) > 1;
 id | count 
----+-------
(0 rows)

changed title from Add unique index to system note metadata to Add index to system note metadata

added 1 commit

• 16d58327 - Add index to system note metadata

Compare with previous version

added 1 commit

• 03ad1933 - Add index for notes_id to system note metadata table

Compare with previous version

added 1 commit

• a6635b34 - Add index for note_id to system note metadata table

Compare with previous version

resolved all discussions

Thanks @stanhu, this would definitely have got worse and worse. There shouldn't be any duplicates, but shouldn't your query check against note_id? I think this could indeed use a unique index. cc @jarka

assigned to @stanhu

Yep, the index can be unique. I don't expect any duplicates (something is probably wrong if there were some) however the query should really check note_id

Good call. That's what I get for trying to do this late at night:

gitlabhq_production=# SELECT note_id, COUNT(note_id) FROM system_note_metadata GROUP BY id HAVING COUNT(note_id) > 1;
 note_id | count 
---------+-------
(0 rows)

added 18 commits

• a6635b34...a9da3743 - 17 commits from branch master
• 3f0054a8 - Add unique index for note_id to system note metadata table

Compare with previous version

changed title from Add index to system note metadata to Add unique index to system note metadata

assigned to @smcgivern

approved this merge request

enabled an automatic merge when the pipeline for 3f0054a8 succeeds

merged

mentioned in commit 28c63ce3

Picked into 9-1-stable, will go into 9.1.0-rc6

mentioned in commit 715d6813