Index traversal_ids for group type
There are failing jobs in the QA pipeline because the PG optimizer is finding a sub-optimal path. We can add an index to provide a better option than it's currently taking.
The PG optimizer is improperly optimizing linear queries for User#manageable_groups
(called via user.can?(:create_fork)
).
WITH "descendants_base_cte" AS MATERIALIZED (
SELECT
"namespaces"."traversal_ids",
"namespaces"."id"
FROM
"namespaces"
INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "namespaces"."type" = 'Group'
AND "members"."user_id" = 1675733
AND "members"."requested_at" IS NULL
AND (access_level >= 10)
AND "members"."access_level" IN (40, 50)
),
"superset" AS (
SELECT
d1.traversal_ids
FROM
descendants_base_cte d1
WHERE
NOT EXISTS (
SELECT
1
FROM
descendants_base_cte d2
WHERE
d2.id = ANY(d1.traversal_ids)
AND d2.id <> d1.id
)
)
SELECT
DISTINCT *
FROM
"superset",
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND next_traversal_ids_sibling("superset"."traversal_ids") > "namespaces"."traversal_ids"
AND "superset"."traversal_ids" <= "namespaces"."traversal_ids"
The third last line of "namespaces"."type" = 'Group'
pushes the query to 700ms. Without this the query is < 2ms.