Skip to content

Create a single temporary index for note mentions migration

Alexandru Croitor requested to merge create_single_temporary_index_for_notes_mentions into master

What does this MR do?

From @krasio suggestion at !23859 (comment 293667614)

The reason to combine indexes will be to reduce the time we spend in migrations creating them. I assume the time for a creating index on all types will be the same as the time to create index on just one type - in both cases we have to scan the whole table, just that we save different part of what we find in the index. Size of the general index should be same as the combined size of all indexes, right? Saving time on creation and not duplicating work will make difference in deployments, especially if more than one of these MRs end up in the same release.

Luckily we have a way to verify the above - #database-lab.

Creating the index

CREATE INDEX CONCURRENTLY mentions_in_notes_temp_index ON notes (id, noteable_type) WHERE note ~~ '%@%'::text

takes 48 minutes. Index size is 796 MB.

Execution plans for the 4 queries from the MR description look pretty much the same, the index is always being used:

The whole session in #database-lab is here - https://gitlab.slack.com/archives/CLJMDRD8C/p1582746360394100 (internal link).

How many of these MRs we have left? Creating the more general index now will save us 40 to 50 minutes in execution time for migrations for each of the next MRs.

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

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
Edited by Alexandru Croitor

Merge request reports