Add clusters index to improve usage data queries
What does this MR do?
Adds a missing index to clusters
to improve performance of usage ping queries:
https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/lib/ee/gitlab/usage_data.rb#L211-214:
Example query fromSELECT
COUNT(DISTINCT "clusters"."user_id")
FROM
"clusters"
WHERE
"clusters"."cluster_type" = 3
AND "clusters"."enabled" = TRUE
AND "clusters"."created_at" BETWEEN '2020-03-20 04:23:25.724123' AND '2020-04-20 04:23:25.724204'
AND "clusters"."id" BETWEEN 40000 AND 49999;
Before:
Aggregate (cost=1390.10..1390.11 rows=1 width=8) (actual time=1.301..1.302 rows=1 loops=1)
Buffers: shared hit=245
-> Index Scan using clusters_pkey on public.clusters (cost=0.29..1389.71 rows=157 width=4) (actual time=1.285..1.285 rows=0 loops=1)
Index Cond: ((clusters.id >= 40000) AND (clusters.id <= 49999))
Filter: (clusters.enabled AND (clusters.created_at >= '2020-03-20 04:23:25.724123+00'::timestamp with time zone) AND (clusters.created_at <= '2020-04-20 04:23:25.724204+00'::timestamp with time zone) AND (clusters.cluster_type = 3))
Rows Removed by Filter: 3097
Buffers: shared hit=245
After:
Aggregate (cost=232.13..232.14 rows=1 width=8) (actual time=0.480..0.480 rows=1 loops=1)
Buffers: shared hit=16 read=1
I/O Timings: read=0.120
-> Index Scan using index_clusters_on_enabled_cluster_type_id_and_created_at on public.clusters (cost=0.29..231.73 rows=157 width=4) (actual time=0.259..0.259 rows=0 loops=1)
Index Cond: ((clusters.enabled = true) AND (clusters.cluster_type = 3) AND (clusters.id >= 40000) AND (clusters.id <= 49999) AND (clusters.created_at >= '2020-03-20 04:23:25.724123+00'::timestamp with time zone) AND (clusters.created_at <= '2020-04-20 04:23:25.724204+00'::timestamp with time zone))
Filter: clusters.enabled
Rows Removed by Filter: 0
Buffers: shared hit=12
https://gitlab.com/gitlab-org/gitlab/-/blob/master/lib/gitlab/usage_data.rb#L68-75
Example query fromSELECT
COUNT("clusters"."id")
FROM
"clusters"
WHERE
"clusters"."enabled" = TRUE
AND "clusters"."cluster_type" = 3
AND "clusters"."id" BETWEEN 7 AND 100006
Before:
Aggregate (cost=2805.97..2805.98 rows=1 width=8) (actual time=23.369..23.369 rows=1 loops=1)
Buffers: shared hit=11701
-> Index Scan using clusters_pkey on public.clusters (cost=0.29..2733.72 rows=28900 width=4) (actual time=0.012..20.038 rows=28844 loops=1)
Index Cond: ((clusters.id >= 7) AND (clusters.id <= 100006))
Filter: (clusters.enabled AND (clusters.cluster_type = 3))
Rows Removed by Filter: 7031
Buffers: shared hit=11701
After:
Aggregate (cost=1205.64..1205.65 rows=1 width=8) (actual time=12.243..12.243 rows=1 loops=1)
Buffers: shared hit=2451 read=113
I/O Timings: read=2.295
-> Index Only Scan using index_clusters_on_enabled_cluster_type_id_and_created_at on public.clusters (cost=0.29..1133.37 rows=28908 width=4) (actual time=0.164..9.556 rows=28844 loops=1)
Index Cond: ((clusters.enabled = true) AND (clusters.cluster_type = 3) AND (clusters.id >= 7) AND (clusters.id <= 100006))
Heap Fetches: 3265
Filter: clusters.enabled
Rows Removed by Filter: 0
Buffers: shared hit=2451 read=113
I/O Timings: read=2.295
Migrate up:
== 20200402001106 AddClusterTypeIndexToClusters: migrating ====================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:clusters, [:enabled, :cluster_type, :id, :created_at], {:name=>"index_clusters_on_enabled_cluster_type_id_and_created_at", :algorithm=>:concurrently})
-> 0.0044s
-- execute("SET statement_timeout TO 0")
-> 0.0002s
-- add_index(:clusters, [:enabled, :cluster_type, :id, :created_at], {:name=>"index_clusters_on_enabled_cluster_type_id_and_created_at", :algorithm=>:concurrently})
-> 0.0066s
-- execute("RESET ALL")
-> 0.0002s
== 20200402001106 AddClusterTypeIndexToClusters: migrated (0.0115s) ===========
Migrate down:
== 20200402001106 AddClusterTypeIndexToClusters: reverting ====================
-- transaction_open?()
-> 0.0000s
-- indexes(:clusters)
-> 0.0039s
-- execute("SET statement_timeout TO 0")
-> 0.0002s
-- remove_index(:clusters, {:algorithm=>:concurrently, :name=>"index_clusters_on_enabled_cluster_type_id_and_created_at"})
-> 0.0028s
-- execute("RESET ALL")
-> 0.0002s
== 20200402001106 AddClusterTypeIndexToClusters: reverted (0.0073s) ===========
Screenshots
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
Edited by 🤖 GitLab Bot 🤖