Skip to content

Add Gitpod enabled user setting to Usage Data

Francisco Javier López (ex-Gitlab) requested to merge 242016-fj-add-user-preferences-gitpod-enabled-to-usage-data into master

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

Refs #242016 (closed)

Edited by Yannis Roussos

Merge request reports