Add index on snippet for new monthly counters
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'
- Before
- Execution time with warm caches: 7-11ms
- Plan: https://explain.depesz.com/s/btlX
- After
- Executiom time with warm caches: 7-8ms
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'
- Before
- Execution time with warm caches: < 1ms
- Plan: https://explain.depesz.com/s/jmdk
- After
- Execution time with warm caches: < 1ms
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)