Optimize groups template page query
What does this MR do and why?
Contributes to #381077 (closed)
Database
Before
Time: 5.834 s
- planning: 4.514 ms
- execution: 5.829 s
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 69611 (~543.80 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Click to expand
SELECT
COUNT(*)
FROM
"projects"
WHERE
"projects"."namespace_id" IN ( WITH "descendants_base_cte" AS MATERIALIZED (
SELECT
"namespaces"."id",
"namespaces"."traversal_ids"
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" = 421631
AND "members"."requested_at" IS NULL
AND (
access_level >= 10
)
AND (
members.access_level >= 30
)
),
"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
"namespaces"."id"
FROM
"superset",
"namespaces"
INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
WHERE
"namespaces"."type" = 'Group'
AND next_traversal_ids_sibling ("superset"."traversal_ids") > "namespaces"."traversal_ids"
AND "superset"."traversal_ids" <= "namespaces"."traversal_ids"
AND "namespaces"."id" IN ( WITH "descendants_base_cte" AS MATERIALIZED (
WITH "base_ancestors_cte" AS MATERIALIZED (
SELECT
"namespaces"."traversal_ids"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."custom_project_templates_group_id" IS NOT NULL
)
SELECT
"id",
"traversal_ids"
FROM
"namespaces"
INNER JOIN (
SELECT DISTINCT
unnest(
"base_ancestors_cte"."traversal_ids"
)
FROM
base_ancestors_cte
) AS ancestors (
ancestor_id
) ON namespaces.id = ancestors.ancestor_id
WHERE
"namespaces"."type" = 'Group'
AND (
EXISTS (
SELECT
1
FROM
"plans"
INNER JOIN "gitlab_subscriptions" ON "gitlab_subscriptions"."hosted_plan_id" = "plans"."id"
WHERE
"plans"."name" IN (
'silver', 'premium', 'premium_trial', 'gold', 'ultimate', 'ultimate_trial', 'opensource'
)
AND (
gitlab_subscriptions.namespace_id = namespaces.id
)
)
)
),
"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
"custom_project_templates_group_id"
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"
AND "namespaces"."custom_project_templates_group_id" IS NOT NULL))
AND "projects"."marked_for_deletion_at" IS NULL
AND "projects"."pending_delete" = FALSE;https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21413/commands/69823
After
Time: 82.037 ms
- planning: 15.571 ms
- execution: 66.466 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 20570 (~160.70 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Click to expand
SELECT
"plans"."id"
FROM
"plans"
WHERE
"plans"."name" IN ('silver', 'premium', 'premium_trial', 'gold', 'ultimate', 'ultimate_trial', 'opensource');SELECT
COUNT(*)
FROM
"projects"
WHERE
"projects"."namespace_id" IN ( WITH "descendants_base_cte" AS MATERIALIZED (
SELECT
"namespaces"."id",
"namespaces"."traversal_ids"
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" = 421631
AND "members"."requested_at" IS NULL
AND (
access_level >= 10
)
AND (
members.access_level >= 30
)
),
"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
"namespaces"."id"
FROM
"superset",
"namespaces"
INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
WHERE
"namespaces"."type" = 'Group'
AND next_traversal_ids_sibling ("superset"."traversal_ids") > "namespaces"."traversal_ids"
AND "superset"."traversal_ids" <= "namespaces"."traversal_ids"
AND "namespaces"."id" IN (
SELECT
"namespaces"."custom_project_templates_group_id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids[1] IN (
SELECT
"gitlab_subscriptions"."namespace_id"
FROM
"gitlab_subscriptions"
WHERE
"gitlab_subscriptions"."trial" = FALSE
AND "gitlab_subscriptions"."hosted_plan_id" IN (1, 2, 3, 6, 5, 4, 7)))
AND "namespaces"."custom_project_templates_group_id" IS NOT NULL))
AND "projects"."marked_for_deletion_at" IS NULL
AND "projects"."pending_delete" = FALSE;- https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21433/commands/69921
- https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21433/commands/69923
How to set up and validate locally
- Enable feature flag
Feature.enable(:optimize_group_template_query) - Update code in
groups_with_templates_finder.rb
- if ::Gitlab::CurrentSettings.should_check_namespace_plan?
+ if true- Visit page http://127.0.0.1:3000/projects/new#create_from_template
- Open a query list from the performance bar
- You should see a new version of the query
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.
Edited by Vasilii Iakliushin