You need to sign in or sign up before continuing.
Optimize clusters platforms batch queries
What does this MR do?
Optimizes the clusters_platforms
counter queries for batch counting in usage data
Part of issue #208923 (closed)
Query
SELECT COUNT(“clusters”.“id”) FROM “clusters” INNER JOIN “cluster_providers_aws” ON “cluster_providers_aws”.“cluster_id” = “clusters”.“id” WHERE “clusters”.“provider_type” = 1 AND (“cluster_providers_aws”.“status” IN (3)) AND “clusters”.“enabled” = true AND “clusters”.“id” BETWEEN 0 AND 99999
Optimization
CREATE INDEX index_clusters_on_enabled_and_provider_type_and_id ON public.clusters USING btree (enabled, provider_type, id);
-- The query has been executed. Duration: 56.000 ms
https://explain.depesz.com/s/eY1g
Before: Bad!https://explain.depesz.com/s/DnmU
After: Good!Timing
After the index for batch counting takes 1 second pessimistic
- 33k clusters,
- with 10_000 batch sizes
- 33k/10_000 = 4 loops
- Time: < 1s ( cold cache with no time constraint )
Migration output
VERBOSE=true bundle exec rake db:migrate:up VERSION=20200310123229
== 20200310123229 AddIndexOnEnabledAndProviderTypeAndIdToClusters: migrating ==
-- transaction_open?()
-> 0.0000s
-- index_exists?(:clusters, [:enabled, :provider_type, :id], {:algorithm=>:concurrently})
-> 0.0029s
-- execute("SET statement_timeout TO 0")
-> 0.0007s
-- add_index(:clusters, [:enabled, :provider_type, :id], {:algorithm=>:concurrently})
-> 0.0060s
-- execute("RESET ALL")
-> 0.0006s
== 20200310123229 AddIndexOnEnabledAndProviderTypeAndIdToClusters: migrated (0.0103s)
VERBOSE=true bundle exec rake db:migrate:down VERSION=20200310123229
== 20200310123229 AddIndexOnEnabledAndProviderTypeAndIdToClusters: reverting ==
-- transaction_open?()
-> 0.0000s
-- index_exists?(:clusters, [:enabled, :provider_type, :id], {:algorithm=>:concurrently})
-> 0.0027s
-- execute("SET statement_timeout TO 0")
-> 0.0004s
-- remove_index(:clusters, {:algorithm=>:concurrently, :column=>[:enabled, :provider_type, :id]})
-> 0.0042s
-- execute("RESET ALL")
-> 0.0004s
== 20200310123229 AddIndexOnEnabledAndProviderTypeAndIdToClusters: reverted (0.0079s)
Edited by Alex Buijs