Skip to content

Add index on snippet for new monthly counters

Francisco Javier López (ex-Gitlab) requested to merge fj-optimize-snippet-monthly-counters into master

What does this MR do?

In this MR we introduce a new index on snippets to optimize the query for usage data monthly counters. The query uses the id, created_at, and type, so a new index on these fields improves the time a lot.

There is already an index for that matter introduced in !34918 (merged), but it is only based on id and created_at. Once the query for usage data use the snippet type field, this index is not used, that's why we're going to replace it with the new one.

Refs #219399 (closed) MR !35155 (merged)

Optimization

CREATE INDEX index_snippets_on_id_and_created_at_and_type ON public.snippets USING btree (id, created_at, type);

This new size index is about 13MB, while the existing one is about 8MB.

Queries

We're going to perform these queries for both type=PersonalSnippet and type=ProjectSnippet, therefore we cannot make the index based on a specific type. I'm posting here the queries for just one type because they're the same.

SELECT MIN("snippets"."id") FROM "snippets" WHERE "snippets"."type" = 'PersonalSnippet' AND "snippets"."created_at" BETWEEN '2020-05-22 09:12:11.847078' AND '2020-06-19 09:12:11.847153'
SELECT MAX("snippets"."id") FROM "snippets" WHERE "snippets"."type" = 'PersonalSnippet' AND "snippets"."created_at" BETWEEN '2020-05-22 09:12:11.847078' AND '2020-06-19 09:12:11.847153'
SELECT COUNT("snippets"."id") FROM "snippets" WHERE "snippets"."type" = 'PersonalSnippet' AND "snippets"."created_at" BETWEEN '2020-05-22 09:12:11.847078' AND '2020-06-19 09:12:11.847153' AND "snippets"."id" BETWEEN 1900000 AND 1999999
  • Before
    • Execution time with warm caches: 16ms
  • After
    • Execution time with warm caches: 9-12ms

Migration up

== 20200623161610 AddIndexOnIdAndCreatedAtAndTypeToSnippets: migrating ========
-- transaction_open?()
   -> 0.0001s
-- index_exists?(:snippets, [:id, :created_at, :type], {:algorithm=>:concurrently})
   -> 0.0143s
-- add_index(:snippets, [:id, :created_at, :type], {:algorithm=>:concurrently})
   -> 0.0108s
== 20200623161610 AddIndexOnIdAndCreatedAtAndTypeToSnippets: migrated (0.0262s)

== 20200623161939 RemoveIndexSnippetsOnIdAndType: migrating ===================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:snippets, [:id, :created_at], {:algorithm=>:concurrently})
   -> 0.0046s
-- remove_index(:snippets, {:algorithm=>:concurrently, :column=>[:id, :created_at]})
   -> 0.0290s
== 20200623161939 RemoveIndexSnippetsOnIdAndType: migrated (0.0341s) ==========

Migration down

== 20200623161939 RemoveIndexSnippetsOnIdAndType: reverting ===================
-- transaction_open?()
   -> 0.0001s
-- index_exists?(:snippets, [:id, :created_at], {:algorithm=>:concurrently})
   -> 0.0166s
-- add_index(:snippets, [:id, :created_at], {:algorithm=>:concurrently})
   -> 0.0044s
== 20200623161939 RemoveIndexSnippetsOnIdAndType: reverted (0.0219s) ==========

== 20200623161610 AddIndexOnIdAndCreatedAtAndTypeToSnippets: reverting ========
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:snippets, [:id, :created_at, :type], {:algorithm=>:concurrently})
   -> 0.0265s
-- remove_index(:snippets, {:algorithm=>:concurrently, :column=>[:id, :created_at, :type]})
   -> 0.0106s
== 20200623161610 AddIndexOnIdAndCreatedAtAndTypeToSnippets: reverted (0.0379s)

Does this MR meet the acceptance criteria?

Conformity

Edited by 🤖 GitLab Bot 🤖

Merge request reports