Optimize suggestions counters in usage data

Optimize notes with suggestions counters in usage data

1. Adds a very specialized index
2. Gives manual start and finish for the batch counter

Main issue #208890 (closed)

Optimization

exec CREATE INDEX aa_index_notes_c1 ON public.notes USING btree (author_id, created_at, id)
# The query has been executed. Duration: 50.695 min

Query 1

SELECT MIN("notes"."author_id") FROM "notes" INNER JOIN "suggestions" ON "suggestions"."note_id" = "notes"."id" WHERE "notes"."created_at" BETWEEN '2020-02-10 18:14:49.508694' AND '2020-03-09 18:14:49.508982'

Before: https://explain.depesz.com/s/Ebq8 7 minutes

After: we give up and use SELECT MIN("users"."id") FROM "users" similar to !27589 (merged)

Query 2

SELECT MAX("notes"."author_id") FROM "notes" INNER JOIN "suggestions" ON "suggestions"."note_id" = "notes"."id" WHERE "notes"."created_at" BETWEEN '2020-02-10 18:14:49.508694' AND '2020-03-09 18:14:49.508982'

Before: https://explain.depesz.com/s/4mZU 7 minutes

After: we give up and use SELECT MAX("users"."id") FROM "users" similar to !27589 (merged)

Query 3

SELECT COUNT(DISTINCT "notes"."author_id") FROM "notes" INNER JOIN "suggestions" ON "suggestions"."note_id" = "notes"."id" WHERE "notes"."created_at" BETWEEN '2020-02-10 18:14:49.508694' AND '2020-03-09 18:14:49.508982' AND "notes"."author_id" BETWEEN 100000 AND 101250

Before: https://explain.depesz.com/s/Pelg

After: https://explain.depesz.com/s/Cb8O

Migration output

$ VERBOSE=true bundle exec rake db:migrate:up VERSION=20200330132913
== 20200330132913 AddIndexOnAuthorIdAndCreatedAtAndIdToNotes: migrating =======
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:notes, [:author_id, :created_at, :id], {:algorithm=>:concurrently})
   -> 0.0042s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- add_index(:notes, [:author_id, :created_at, :id], {:algorithm=>:concurrently})
   -> 0.0150s
-- execute("RESET ALL")
   -> 0.0002s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:notes, [:author_id, :created_at], {:algorithm=>:concurrently})
   -> 0.0033s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- remove_index(:notes, {:algorithm=>:concurrently, :column=>[:author_id, :created_at]})
   -> 0.0056s
-- execute("RESET ALL")
   -> 0.0001s
== 20200330132913 AddIndexOnAuthorIdAndCreatedAtAndIdToNotes: migrated (0.0289s) 

aakgun@saygitu:~/aakgun/1/gdk/gitlab$ VERBOSE=true bundle exec rake db:migrate:down VERSION=20200330132913
== 20200330132913 AddIndexOnAuthorIdAndCreatedAtAndIdToNotes: reverting =======
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:notes, [:author_id, :created_at], {:algorithm=>:concurrently})
   -> 0.0040s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- add_index(:notes, [:author_id, :created_at], {:algorithm=>:concurrently})
   -> 0.0296s
-- execute("RESET ALL")
   -> 0.0005s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:notes, [:author_id, :created_at, :id], {:algorithm=>:concurrently})
   -> 0.0144s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- remove_index(:notes, {:algorithm=>:concurrently, :column=>[:author_id, :created_at, :id]})
   -> 0.0234s
-- execute("RESET ALL")
   -> 0.0006s
== 20200330132913 AddIndexOnAuthorIdAndCreatedAtAndIdToNotes: reverted (0.0739s)