Product analytics for group-level integrations
What does this MR do?
We need to measure the adoption of the new group-level integrations &2543.
- Exclude the group-level services from being counted in
projects_#{service_name}_active
. - New counter
groups_#{service_name}_active
for active group-level integrations. - Update
projects_inheriting_instance_#{service_name}_active
toprojects_inheriting_#{service_name}_active
because a project can inherit from an instance or a group, we are not reading that key at the moment https://gitlab.com/gitlab-data/analytics/-/issues/5849. - New counter
groups_inheriting_#{service_name}_active
for active group integrations inheriting settings.
Migration script output
$ rails db:migrate
== 20201015154527 AddIndexOnServicesForUsageData: migrating ===================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:services, [:type, :id], {:where=>"active = TRUE AND project_id IS NOT NULL", :name=>"index_services_on_type_id_when_active_and_project_id_not_null", :algorithm=>:concurrently})
-> 0.0041s
-- execute("SET statement_timeout TO 0")
-> 0.0002s
-- add_index(:services, [:type, :id], {:where=>"active = TRUE AND project_id IS NOT NULL", :name=>"index_services_on_type_id_when_active_and_project_id_not_null", :algorithm=>:concurrently})
-> 0.0036s
-- execute("RESET ALL")
-> 0.0002s
== 20201015154527 AddIndexOnServicesForUsageData: migrated (0.0085s) ==========
$ rails db:migrate:down VERSION=20201015154527
== 20201015154527 AddIndexOnServicesForUsageData: reverting ===================
-- transaction_open?()
-> 0.0000s
-- indexes(:services)
-> 0.0050s
-- execute("SET statement_timeout TO 0")
-> 0.0001s
-- remove_index(:services, {:algorithm=>:concurrently, :name=>"index_services_on_type_id_when_active_and_project_id_not_null"})
-> 0.0018s
-- execute("RESET ALL")
-> 0.0001s
== 20201015154527 AddIndexOnServicesForUsageData: reverted (0.0074s) ==========
SQL Queries
CREATE INDEX index_services_on_type_id_when_active_and_project_id_not_null ON services USING btree (type, id) WHERE ((active = true) AND (project_id IS NOT NULL));
SELECT MIN("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."project_id" IS NOT NULL
AND "services"."type" = 'JiraService';
Time: 0.812 ms
- planning: 0.504 ms
- execution: 0.308 ms
Query plan: https://explain.depesz.com/s/jDov
SELECT MAX("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."project_id" IS NOT NULL
AND "services"."type" = 'JiraService';
Time: 0.738 ms
- planning: 0.250 ms
- execution: 0.488 ms
Query plan: https://explain.depesz.com/s/Kdnf
SELECT COUNT("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."project_id" IS NOT NULL
AND "services"."type" = 'JiraService'
AND "services"."id" BETWEEN 0 AND 99999;
Time: 0.449 ms
- planning: 0.361 ms
- execution: 0.088 ms
Query plan: https://explain.depesz.com/s/g8ce
SELECT MIN("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."group_id" IS NOT NULL
AND "services"."type" = 'JiraService';
Time: 3.824 ms
- planning: 0.224 ms
- execution: 3.600 ms
Query plan: https://explain.depesz.com/s/EYDP
SELECT MAX("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."group_id" IS NOT NULL
AND "services"."type" = 'JiraService';
Time: 0.299 ms
- planning: 0.246 ms
- execution: 0.053 ms
Query plan: https://explain.depesz.com/s/wB87
SELECT COUNT("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."group_id" IS NOT NULL
AND "services"."type" = 'JiraService'
AND "services"."id" BETWEEN 0 AND 99999;
Time: 0.357 ms
- planning: 0.290 ms
- execution: 0.067 ms
Query plan: https://explain.depesz.com/s/CA07
SELECT MIN("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."project_id" IS NOT NULL
AND "services"."inherit_from_id" IS NOT NULL
AND "services"."type" = 'JiraService';
Time: 6.984 ms
- planning: 0.321 ms
- execution: 6.663 ms
Query plan: https://explain.depesz.com/s/gTZX
SELECT MAX("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."project_id" IS NOT NULL
AND "services"."inherit_from_id" IS NOT NULL
AND "services"."type" = 'JiraService';
Time: 0.694 ms
- planning: 0.620 ms
- execution: 0.074 ms
Query plan: https://explain.depesz.com/s/Hlri
SELECT COUNT("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."project_id" IS NOT NULL
AND "services"."type" = 'JiraService'
AND "services"."inherit_from_id" IS NOT NULL
AND "services"."id" BETWEEN 0 AND 99999;
Time: 0.559 ms
- planning: 0.463 ms
- execution: 0.096 ms
Query plan: https://explain.depesz.com/s/iVJK
SELECT MIN("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."group_id" IS NOT NULL
AND "services"."inherit_from_id" IS NOT NULL
AND "services"."type" = 'JiraService';
Time: 0.601 ms
- planning: 0.428 ms
- execution: 0.173 ms
Query plan: https://explain.depesz.com/s/aBcB
SELECT MAX("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."group_id" IS NOT NULL
AND "services"."inherit_from_id" IS NOT NULL
AND "services"."type" = 'JiraService';
Time: 0.523 ms
- planning: 0.403 ms
- execution: 0.120 ms
Query plan: https://explain.depesz.com/s/PFHk
SELECT COUNT("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."group_id" IS NOT NULL
AND "services"."type" = 'JiraService'
AND "services"."inherit_from_id" IS NOT NULL
AND "services"."id" BETWEEN 0 AND 99999;
Time: 0.811 ms
- planning: 0.732 ms
- execution: 0.079 ms
Query plan: https://explain.depesz.com/s/JG6B
Related to #233940 (closed)
Edited by Arturo Herrero