Exclude instance-level services from usage data
What does this MR do?
After introducing instance-level integrations &2430 we want to exclude instance-level services from being counted.
This merge request excludes instance-level services from usage ping data.
SQL Queries
Original query
EXPLAIN SELECT COUNT("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."template" = FALSE
AND "services"."type" = 'Jira'
AND "services"."id" BETWEEN 0 AND 99999;
Time: 0.515 ms
- planning: 0.461 ms
- execution: 0.054 ms
https://explain.depesz.com/s/Xlbv
New query with the old index
EXPLAIN SELECT COUNT("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."template" = FALSE
AND "services"."instance" = FALSE
AND "services"."type" = 'Jira'
AND "services"."id" BETWEEN 0 AND 99999;
Time: 0.532 ms
- planning: 0.398 ms
- execution: 0.134 ms
https://explain.depesz.com/s/OJ65
There is an index index_services_on_type_and_id_and_template_when_active
introduced in !27093 (merged) to optimize the queries performance for the services usage data.
The usage data counter is iterating over the index in 10K batches, without covering all the fields (type
, id
, template
, instance
), the database would need to constantly look at the table for the instance
column (extra I/O). We have decided to replace the index !38147 (comment 387641920).
Migration script output
$ rails db:migrate
== 20200730083043 ReplaceIndexForServiceUsageData: migrating ==================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:services, [:type, :id], {:where=>"active = TRUE AND instance = FALSE AND template = FALSE", :name=>"index_services_on_type_id_when_active_not_instance_not_template", :algorithm=>:concurrently})
-> 0.0036s
-- add_index(:services, [:type, :id], {:where=>"active = TRUE AND instance = FALSE AND template = FALSE", :name=>"index_services_on_type_id_when_active_not_instance_not_template", :algorithm=>:concurrently})
-> 0.0032s
-- transaction_open?()
-> 0.0000s
-- indexes(:services)
-> 0.0029s
-- remove_index(:services, {:algorithm=>:concurrently, :name=>"index_services_on_type_and_id_and_template_when_active"})
-> 0.0012s
== 20200730083043 ReplaceIndexForServiceUsageData: migrated (0.0115s) =========
$ rails db:rollback
== 20200730083043 ReplaceIndexForServiceUsageData: reverting ==================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:services, [:type, :id, :template], {:where=>"active = TRUE", :name=>"index_services_on_type_and_id_and_template_when_active", :algorithm=>:concurrently})
-> 0.0053s
-- add_index(:services, [:type, :id, :template], {:where=>"active = TRUE", :name=>"index_services_on_type_and_id_and_template_when_active", :algorithm=>:concurrently})
-> 0.0037s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:services, "index_services_on_type_id_when_active_not_instance_not_template", {:algorithm=>:concurrently})
-> 0.0038s
== 20200730083043 ReplaceIndexForServiceUsageData: reverted (0.0136s) =========
New query with the replaced index
EXPLAIN SELECT COUNT("services"."id")
FROM "services"
WHERE "services"."active" = TRUE
AND "services"."template" = FALSE
AND "services"."instance" = FALSE
AND "services"."type" = 'Jira'
AND "services"."id" BETWEEN 0 AND 99999;
Time: 0.580 ms
- planning: 0.269 ms
- execution: 0.311 ms
- I/O read: 0.211 ms
- I/O write: 0.000 ms
https://explain.depesz.com/s/A7Z5
Related to #204802 (closed)