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

1. Enable the new method behavior feature flag

   Feature.enable(:linear_user_group_notification_settings_finder_ancestors_scopes)

2. Enable the feature flag for linear ancestors scopes

   Feature.enable(:use_traversal_ids_for_ancestor_scopes)

3. In Rails console enable the traversal id feature flag

   Feature.enable(:use_traversal_ids)

4. 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)