Add limit to security policy projects queried before deleting group
What does this MR do and why?
As a part of Update delete group message (!178971 - merged) we updated the group settings page to check if the group has any security policy projects before enabling the delete button. The query to get the security policy projects can get expensive for large namespaces with large number of security policy projects.
This MR fixes it by limiting the number of security policy projects queried and displaying only 10 of them.
Database queries
Offset
Query
SELECT
1 AS one
FROM
"security_orchestration_policy_configurations"
WHERE
"security_orchestration_policy_configurations"."security_policy_management_project_id" IN (
SELECT
"projects"."id"
FROM
UNNEST( COALESCE( (SELECT
ids
FROM
(SELECT
"namespace_descendants"."all_project_ids" AS ids
FROM
"namespace_descendants"
WHERE
"namespace_descendants"."outdated_at" IS NULL
AND "namespace_descendants"."namespace_id" = 9970) cached_query),
(SELECT
ids
FROM
(SELECT
ARRAY_AGG("projects"."id") AS ids
FROM
(SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (SELECT
"namespaces"."id"
FROM
UNNEST( COALESCE( (SELECT
ids
FROM
(SELECT
"namespace_descendants"."self_and_descendant_group_ids" AS ids
FROM
"namespace_descendants"
WHERE
"namespace_descendants"."outdated_at" IS NULL
AND "namespace_descendants"."namespace_id" = 9970) cached_query),
(SELECT
ids
FROM
(SELECT
ARRAY_AGG("namespaces"."id") AS ids
FROM
(SELECT
namespaces.traversal_ids[array_length(namespaces.traversal_ids,
1)] AS id
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))) namespaces) consistent_query)) ) AS namespaces(id) )) projects) consistent_query)) ) AS projects(id)
) LIMIT 1 OFFSET 10Query Plan
Limit (cost=1681.42..1682.55 rows=1 width=4) (actual time=35.693..35.697 rows=1 loops=1)
Buffers: shared hit=99 read=60 dirtied=5
WAL: records=5 fpi=5 bytes=40033
I/O Timings: read=32.317 write=0.000
InitPlan 1 (returns $0)
-> Index Scan using namespace_descendants_12_pkey on gitlab_partitions_static.namespace_descendants_12 namespace_descendants (cost=0.14..3.16 rows=1 width=25) (actual time=1.560..1.561 rows=1 loops=1)
Index Cond: (namespace_descendants.namespace_id = 9970)
Filter: (namespace_descendants.outdated_at IS NULL)
Rows Removed by Filter: 0
Buffers: shared hit=3 read=2
I/O Timings: read=1.529 write=0.000
InitPlan 4 (returns $4)
-> Aggregate (cost=1666.50..1666.51 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
InitPlan 2 (returns $1)
-> Index Scan using namespace_descendants_12_pkey on gitlab_partitions_static.namespace_descendants_12 namespace_descendants_1 (cost=0.14..3.16 rows=1 width=707) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespace_descendants_1.namespace_id = 9970)
Filter: (namespace_descendants_1.outdated_at IS NULL)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
InitPlan 3 (returns $2)
-> Aggregate (cost=1627.13..1627.14 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Bitmap Heap Scan on public.namespaces (cost=79.66..1622.24 rows=978 width=28) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan using index_namespaces_on_traversal_ids_for_groups (cost=0.00..79.42 rows=978 width=0) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces.traversal_ids @> '{9970}'::integer[])
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.72..35.41 rows=316 width=4) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=0.15..0.26 rows=10 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Group Key: namespaces_1.id
I/O Timings: read=0.000 write=0.000
-> Function Scan on unnest namespaces_1 (cost=0.03..0.13 rows=10 width=8) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_projects_on_namespace_id_and_id on public.projects projects_1 (cost=0.56..3.20 rows=32 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (projects_1.namespace_id = namespaces_1.id)
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.44..35.48 rows=31 width=4) (actual time=11.820..35.691 rows=11 loops=1)
Buffers: shared hit=99 read=60 dirtied=5
WAL: records=5 fpi=5 bytes=40033
I/O Timings: read=32.317 write=0.000
-> HashAggregate (cost=0.15..0.26 rows=10 width=8) (actual time=9.305..9.326 rows=53 loops=1)
Group Key: projects.id
Buffers: shared hit=31 read=14
I/O Timings: read=7.186 write=0.000
-> Function Scan on unnest projects (cost=0.03..0.13 rows=10 width=8) (actual time=7.815..8.051 rows=5914 loops=1)
Buffers: shared hit=31 read=14
I/O Timings: read=7.186 write=0.000
-> Index Only Scan using index_sop_configurations_project_id_policy_project_id on public.security_orchestration_policy_configurations (cost=0.29..3.49 rows=3 width=8) (actual time=0.497..0.497 rows=0 loops=53)
Index Cond: (security_orchestration_policy_configurations.security_policy_management_project_id = projects.id)
Heap Fetches: 7
Buffers: shared hit=68 read=46 dirtied=5
WAL: records=5 fpi=5 bytes=40033
I/O Timings: read=25.130 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off', work_mem = '100MB'
Time: 41.399 ms
- planning: 5.428 ms
- execution: 35.971 ms
- I/O read: 32.317 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 99 (~792.00 KiB) from the buffer pool
- reads: 60 (~480.00 KiB) from the OS file cache, including disk I/O
- dirtied: 5 (~40.00 KiB)
- writes: 0Limit
Query
SELECT
"security_orchestration_policy_configurations"."id",
"security_orchestration_policy_configurations"."project_id",
"security_orchestration_policy_configurations"."security_policy_management_project_id",
"security_orchestration_policy_configurations"."created_at",
"security_orchestration_policy_configurations"."updated_at",
"security_orchestration_policy_configurations"."configured_at",
"security_orchestration_policy_configurations"."namespace_id",
"security_orchestration_policy_configurations"."experiments"
FROM "security_orchestration_policy_configurations"
WHERE "security_orchestration_policy_configurations"."security_policy_management_project_id" IN (
SELECT "projects"."id"
FROM UNNEST(
COALESCE(
(
SELECT ids
FROM (
SELECT "namespace_descendants"."all_project_ids" AS ids
FROM "namespace_descendants"
WHERE "namespace_descendants"."outdated_at" IS NULL
AND "namespace_descendants"."namespace_id" = 9970
) cached_query
),
(
SELECT ids
FROM (
SELECT ARRAY_AGG("projects"."id") AS ids
FROM (
SELECT "projects"."id"
FROM "projects"
WHERE "projects"."namespace_id" IN (
SELECT "namespaces"."id"
FROM UNNEST(
COALESCE(
(
SELECT ids
FROM (
SELECT "namespace_descendants"."self_and_descendant_group_ids" AS ids
FROM "namespace_descendants"
WHERE "namespace_descendants"."outdated_at" IS NULL
AND "namespace_descendants"."namespace_id" = 9970
) cached_query
),
(
SELECT ids
FROM (
SELECT ARRAY_AGG("namespaces"."id") AS ids
FROM (
SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
) namespaces
) consistent_query
)
)
) AS namespaces(id)
)
) projects
) consistent_query
)
)
) AS projects(id)
)
LIMIT 10;Query Plan
Limit (cost=1670.12..1690.13 rows=10 width=61) (actual time=2.213..4.143 rows=10 loops=1)
Buffers: shared hit=132 read=3
I/O Timings: read=1.739 write=0.000
InitPlan 1 (returns $0)
-> Index Scan using namespace_descendants_12_pkey on gitlab_partitions_static.namespace_descendants_12 namespace_descendants (cost=0.14..3.16 rows=1 width=25) (actual time=0.016..0.017 rows=1 loops=1)
Index Cond: (namespace_descendants.namespace_id = 9970)
Filter: (namespace_descendants.outdated_at IS NULL)
Rows Removed by Filter: 0
Buffers: shared hit=5
I/O Timings: read=0.000 write=0.000
InitPlan 4 (returns $4)
-> Aggregate (cost=1666.50..1666.51 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
InitPlan 2 (returns $1)
-> Index Scan using namespace_descendants_12_pkey on gitlab_partitions_static.namespace_descendants_12 namespace_descendants_1 (cost=0.14..3.16 rows=1 width=707) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespace_descendants_1.namespace_id = 9970)
Filter: (namespace_descendants_1.outdated_at IS NULL)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
InitPlan 3 (returns $2)
-> Aggregate (cost=1627.13..1627.14 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Bitmap Heap Scan on public.namespaces (cost=79.66..1622.24 rows=978 width=28) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan using index_namespaces_on_traversal_ids_for_groups (cost=0.00..79.42 rows=978 width=0) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces.traversal_ids @> '{9970}'::integer[])
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.72..35.41 rows=316 width=4) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=0.15..0.26 rows=10 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Group Key: namespaces_1.id
I/O Timings: read=0.000 write=0.000
-> Function Scan on unnest namespaces_1 (cost=0.03..0.13 rows=10 width=8) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_projects_on_namespace_id_and_id on public.projects projects_1 (cost=0.56..3.20 rows=32 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (projects_1.namespace_id = namespaces_1.id)
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.44..62.48 rows=31 width=61) (actual time=2.212..4.137 rows=10 loops=1)
Buffers: shared hit=132 read=3
I/O Timings: read=1.739 write=0.000
-> HashAggregate (cost=0.15..0.26 rows=10 width=8) (actual time=2.197..2.205 rows=42 loops=1)
Group Key: projects.id
Buffers: shared hit=41
I/O Timings: read=0.000 write=0.000
-> Function Scan on unnest projects (cost=0.03..0.13 rows=10 width=8) (actual time=0.539..0.806 rows=5914 loops=1)
Buffers: shared hit=41
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_sop_configurations_project_id_policy_project_id on public.security_orchestration_policy_configurations (cost=0.29..6.19 rows=3 width=61) (actual time=0.046..0.046 rows=0 loops=42)
Index Cond: (security_orchestration_policy_configurations.security_policy_management_project_id = projects.id)
Buffers: shared hit=91 read=3
I/O Timings: read=1.739 write=0.000
Settings: effective_cache_size = '472585MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'
Time: 10.348 ms
- planning: 5.901 ms
- execution: 4.447 ms
- I/O read: 1.739 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 132 (~1.00 MiB) from the buffer pool
- reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0References
Screenshots or screen recordings
| Before | After |
|---|---|
 |
 |
How to set up and validate locally
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Related to #560859 (closed)
Edited by Sashi Kumar Kumaresan