Use linear version UserGroupNotificationSettingsFinder#execute
What does this MR do and why?
In this MR, we're switching the behavior of the UserGroupNotificationSettingsFinder#execute
method to use the linear version. The new behavior is behind the linear_user_group_notification_settings_finder_ancestors_scopes
feature flag.
How to set up and validate locally
-
Enable the new method behavior feature flag
Feature.enable(:linear_user_group_notification_settings_finder_ancestors_scopes)
-
Enable the feature flag for linear ancestors scopes
Feature.enable(:use_traversal_ids_for_ancestor_scopes)
-
In Rails console enable the traversal id feature flag
Feature.enable(:use_traversal_ids)
-
In Rails console, execute:
UserGroupNotificationSettingsFinder.new(User.first, Group.first).execute
SQL Queries
This class executes two queries referencing ancestors.
Query 1
The former SQL query was:
WITH RECURSIVE "base_and_ancestors" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" IN
(SELECT "namespaces"."id"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
LIMIT 10))
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_ancestors"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
SELECT "namespaces".*
FROM "base_and_ancestors" AS "namespaces"
This is the query plan and the times are:
Time: 2.235 ms
- planning: 1.624 ms
- execution: 0.611 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 54 (~432.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
The new SQL query is:
SELECT "namespaces".*
FROM
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" IN
(SELECT unnest(traversal_ids)
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" IN
(SELECT "namespaces"."id"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
LIMIT 10))) namespaces
WHERE "namespaces"."type" = 'Group'
This is the query plan and the times are:
Time: 1.707 ms
- planning: 1.320 ms
- execution: 0.387 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 94 (~752.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Query 2
The former SQL query was:
SELECT "notification_settings".*
FROM "notification_settings"
WHERE "notification_settings"."user_id" = 1
AND "notification_settings"."source_type" = 'Namespace'
AND "notification_settings"."source_id" IN
(WITH RECURSIVE "base_and_ancestors" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" IN
(SELECT "namespaces"."id"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
LIMIT 10))
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_ancestors"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT "id"
FROM "base_and_ancestors" AS "namespaces")
This is the query plan and the times are:
Time: 2.727 ms
- planning: 1.550 ms
- execution: 1.177 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 125 (~1000.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
The new SQL query is:
SELECT "notification_settings".*
FROM "notification_settings"
WHERE "notification_settings"."user_id" = 1
AND "notification_settings"."source_type" = 'Namespace'
AND "notification_settings"."source_id" IN
(SELECT "namespaces"."id"
FROM
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" IN
(SELECT unnest(traversal_ids)
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" IN
(SELECT "namespaces"."id"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
LIMIT 10))) namespaces
WHERE "namespaces"."type" = 'Group')
This is the query plan and the times are:
Time: 1.809 ms
- planning: 1.282 ms
- execution: 0.527 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 116 (~928.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #345791 (closed)