Add Gitpod enabled user setting to Usage Data
What does this MR do?
In this MR we add to usage data the number of users who have enabled Gitpod in their user preferences.
Index Creation
CREATE INDEX index_user_preferences_on_gitpod_enabled ON user_preferences USING btree (gitpod_enabled);
The query has been executed. Duration: 16.265 s
The index needs 16 seconds to be created in #database-lab
and it should be safe to be added in a regular migration
Generated Queries
Min user
SELECT MIN("user_preferences"."id") FROM "user_preferences" INNER JOIN "users" ON "users"."id" = "user_preferences"."user_id" WHERE "user_preferences"."gitpod_enabled" = TRUE AND ("users"."state" IN ('active')) AND ("users"."user_type" IS NULL OR "users"."user_type" IN (NULL, 4, 6))
Execution times:
Time: 0.760 ms
- planning: 0.669 ms
- execution: 0.091 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 1 (~8.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Query plan https://explain.depesz.com/s/c5yF
Max user
SELECT MAX("user_preferences"."id") FROM "user_preferences" INNER JOIN "users" ON "users"."id" = "user_preferences"."user_id" WHERE "user_preferences"."gitpod_enabled" = TRUE AND ("users"."state" IN ('active')) AND ("users"."user_type" IS NULL OR "users"."user_type" IN (NULL, 4, 6))
Execution times:
Time: 0.784 ms
- planning: 0.703 ms
- execution: 0.081 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 1 (~8.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Query plan https://explain.depesz.com/s/GEty
Count query
(users ids are retrieved from local)
SELECT COUNT("user_preferences"."id") FROM "user_preferences" INNER JOIN "users" ON "users"."id" = "user_preferences"."user_id" WHERE "user_preferences"."gitpod_enabled" = TRUE AND ("users"."state" IN ('active')) AND ("users"."user_type" IS NULL OR "users"."user_type" IN (NULL, 4, 6)) AND "user_preferences"."id" BETWEEN 1 AND 100000
Execution times:
Time: 0.943 ms
- planning: 0.844 ms
- execution: 0.099 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 1 (~8.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Query plan https://explain.depesz.com/s/szXa
Migration up
== 20200921093826 AddIndexToUserPreferences: migrating ========================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:user_preferences, :gitpod_enabled, {:name=>:index_user_preferences_on_gitpod_enabled, :algorithm=>:concurrently})
-> 0.0116s
-- add_index(:user_preferences, :gitpod_enabled, {:name=>:index_user_preferences_on_gitpod_enabled, :algorithm=>:concurrently})
-> 0.0139s
== 20200921093826 AddIndexToUserPreferences: migrated (0.0263s) ===============
Migration down
== 20200921093826 AddIndexToUserPreferences: reverting ========================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:user_preferences, :gitpod_enabled, {:name=>:index_user_preferences_on_gitpod_enabled, :algorithm=>:concurrently})
-> 0.0088s
-- remove_index(:user_preferences, {:name=>:index_user_preferences_on_gitpod_enabled, :algorithm=>:concurrently, :column=>:gitpod_enabled})
-> 0.0133s
== 20200921093826 AddIndexToUserPreferences: reverted (0.0236s) ===============
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Refs #242016 (closed)
Edited by Yannis Roussos