Optimize ldap_keys count in usage_data

What does this MR do?

Optimize the ldap_keys counters

# 1. https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/lib/ee/gitlab/usage_data.rb#L149 
ldap_keys: count(::LDAPKey)

# 2. https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/lib/ee/gitlab/usage_data.rb#L245
ldap_keys: distinct_count(::LDAPKey.where(time_period), :user_id)

# 3. https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/lib/ee/gitlab/usage_data.rb#L245
time_period = { created_at: 28.days.ago..Time.current }
ldap_keys: distinct_count(::LDAPKey.where(time_period), :user_id),

== 20200316111759 AddIndexOnIdAndLdapKeyToKeys: migrating =====================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:keys, [:id], {:where=>"type = 'LDAPKey'", :name=>"index_keys_on_id_and_ldap_key_type", :algorithm=>:concurrently})
   -> 0.0029s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:keys, [:id], {:where=>"type = 'LDAPKey'", :name=>"index_keys_on_id_and_ldap_key_type", :algorithm=>:concurrently})
   -> 0.0134s
-- execute("RESET ALL")
   -> 0.0004s
== 20200316111759 AddIndexOnIdAndLdapKeyToKeys: migrated (0.0173s) ============

Index added

exec CREATE INDEX CONCURRENTLY index_keys_on_id_and_ldap_key_type ON keys USING btree(id) WHERE type = 'LDAPKey'

The query has been executed. Duration: 52.938 s

Queries in database-lab

query MIN Calculation no period

explain SELECT MIN("keys"."id") FROM "keys" WHERE "keys"."type" = 'LDAPKey'

Before: Time: 4.285 min https://explain.depesz.com/s/LppF

Filter: ((keys.type)::text = 'LDAPKey'::text)

After: Time: 0.309 ms https://explain.depesz.com/s/f3d3

No Filter

---

query MAX Calculation no period

explain SELECT MAX("keys"."id") FROM "keys" WHERE "keys"."type" = 'LDAPKey'

Before: Time: 21.815 s https://explain.depesz.com/s/5UfA

Filter: ((keys.type)::text = 'LDAPKey'::text)

After: Time: 0.235 ms https://explain.depesz.com/s/9Yt6

No Filter

---

query COUNT DISTINCT no period

explain SELECT COUNT("keys"."id") FROM "keys" WHERE "keys"."type" = 'LDAPKey' AND "keys"."id" BETWEEN 0 AND 99999

Before: Time: 46.027 ms https://explain.depesz.com/s/1Yj7L

Filter: ((keys.type)::text = 'LDAPKey'::text)

After: Time: 0.272 ms https://explain.depesz.com/s/wIoV

No Filter

---

query MIN Calculation with no period

explain SELECT MIN("keys"."user_id") FROM "keys" WHERE "keys"."type" = 'LDAPKey'

Before: Time: 4.581 min https://explain.depesz.com/s/UtrX

Filter: ((keys.type)::text = 'LDAPKey'::text)

After: Time: 0.287 ms https://explain.depesz.com/s/FeN3

No filter

---

query MAX Calculation no period

explain SELECT MAX("keys"."user_id") FROM "keys" WHERE "keys"."type" = 'LDAPKey'

Before: 27.428 s https://explain.depesz.com/s/FyVw

Filter: ((keys.type)::text = 'LDAPKey'::text

After: Time: 0.307 ms https://explain.depesz.com/s/ow8f

No Filter

---

query COUNT DISTINCT no period

explain SELECT COUNT(DISTINCT "keys"."user_id") FROM "keys" WHERE "keys"."type" = 'LDAPKey' AND "keys"."user_id" BETWEEN 0 AND 9999

Before: 20.946 ms https://explain.depesz.com/s/C4Ge

Filter: ((keys.type)::text = 'LDAPKey'::text)

After: Time: 0.400 ms https://explain.depesz.com/s/Oumx

Filter: ((keys.user_id >= 0) AND (keys.user_id <= 9999))

---

query MIN Calculation with period

explain SELECT MIN("keys"."user_id") FROM "keys" WHERE "keys"."type" = 'LDAPKey' AND "keys"."created_at" BETWEEN '2020-02-17 08:28:45.247119' AND '2020-03-16 08:28:45.247354'

Before: Time 28.575 s https://explain.depesz.com/s/1Kbu

Filter: ((keys.created_at >= '2020-02-17 08:28:45.247119+00'::timestamp with time zone) AND (keys.created_at <= '2020-03-16 08:28:45.247354+00'::timestamp with time zone) AND ((keys.type)::text = 'LDAPKey'::text))

After: Time: 0.255 ms https://explain.depesz.com/s/jO1i

Filter: ((keys.created_at >= '2020-02-17 08:28:45.247119+00'::timestamp with time zone) AND (keys.created_at <= '2020-03-16 08:28:45.247354+00'::timestamp with time zone))

---

query MAX Calculation with period

explain  SELECT MAX("keys"."user_id") FROM "keys" WHERE "keys"."type" = 'LDAPKey' AND "keys"."created_at" BETWEEN '2020-02-17 08:28:45.247119' AND '2020-03-16 08:28:45.247354'

Before: Time: 40.053 s https://explain.depesz.com/s/iJqs

Filter: ((keys.created_at >= '2020-02-17 08:28:45.247119+00'::timestamp with time zone) AND (keys.created_at <= '2020-03-16 08:28:45.247354+00'::timestamp with time zone) AND ((keys.type)::text = 'LDAPKey'::text))

After: Time: 0.604 ms https://explain.depesz.com/s/yx44

Filter: ((keys.created_at >= '2020-02-17 08:28:45.247119+00'::timestamp with time zone) AND (keys.created_at <= '2020-03-16 08:28:45.247354+00'::timestamp with time zone))

---

query COUNT DISTINCT Calculation with period

explain  SELECT COUNT(DISTINCT "keys"."user_id") FROM "keys" WHERE "keys"."type" = 'LDAPKey' AND "keys"."created_at" BETWEEN '2020-02-17 08:28:45.247119' AND '2020-03-16 08:28:45.247354' AND "keys"."user_id" BETWEEN 0 AND 9999

Before: Time: 20.403 ms https://explain.depesz.com/s/OFIQ

Filter: ((keys.created_at >= '2020-02-17 08:28:45.247119+00'::timestamp with time zone) AND (keys.created_at <= '2020-03-16 08:28:45.247354+00'::timestamp with time zone) AND ((keys.type)::text = 'LDAPKey'::text))

After: Time: 0.453 ms https://explain.depesz.com/s/JYGa

Filter: ((keys.created_at >= '2020-02-17 08:28:45.247119+00'::timestamp with time zone) AND (keys.created_at <= '2020-03-16 08:28:45.247354+00'::timestamp with time zone) AND (keys.user_id >= 0) AND (keys.user_id <= 9999))

---

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry
• Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• Tested in all supported browsers
• Informed Infrastructure department of a default or new setting change, if applicable per definition of done

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

Closes #211474 (closed)