Update packages finder helper to use Namespace#all_projects
🌵 Context
In https://gitlab.com/gitlab-com/gl-infra/production/-/issues/3894, what seems to be a ~bug was discovered on Namespace#self_and_descendants.
Namespace.all_projects must be used instead of Namespace#self_and_descendants. This global effort is tracked in #324220 (closed).
This MR deals with nuget API endpoints.
Closes #325274 (closed)
What does this MR do?
- Updates the finder helper used by nuget services/finders.
This helper is used in:
- the nuget package finder
-
the nuget search service
-
⚠ This service triggers 2 queries:- one to get the total number of the results
- one to get the given page of results
-
Screenshots (strongly suggested)
N / A
Does this MR meet the acceptance criteria?
Conformity
-
📋 Does this MR need a changelog?-
I have included a changelog entry. -
I have not included a changelog entry because _____.
-
- [-] Documentation (if required)
-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides - [-] Separation of EE specific content
Availability and Testing
- [-] Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
- [-] Tested in all supported browsers
- [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
- [-] Label as security and @ mention
@gitlab-com/gl-security/appsec - [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
- [-] Security reports checked/validated by a reviewer from the AppSec team
Database review
Nuget package finder
Before this MR
https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/2688/commands/8289
SQL query
SELECT "packages_packages".*
FROM "packages_packages"
WHERE "packages_packages"."project_id" IN (
SELECT "projects"."id"
FROM "projects"
WHERE "projects"."namespace_id" IN (
WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 785414 ) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "id"
FROM "base_and_descendants" AS "namespaces"
)
AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 3983112 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10,20))
)
AND "packages_packages"."package_type" = 4
AND "packages_packages"."version" IS NOT NULL
AND ("packages_packages"."package_type" != 4 OR "packages_packages"."name" != 'NuGet.Temporary.Package')
AND "packages_packages"."name" ILIKE 'Bitwolf'
ORDER BY created_at DESC
LIMIT 300explain plan
Limit (cost=11637.19..11637.20 rows=1 width=84) (actual time=482.250..482.260 rows=10 loops=1)
Buffers: shared hit=227 read=395
I/O Timings: read=470.706
-> Sort (cost=11637.19..11637.20 rows=1 width=84) (actual time=482.248..482.257 rows=10 loops=1)
Sort Key: packages_packages.created_at DESC
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=227 read=395
I/O Timings: read=470.706
-> Nested Loop Semi Join (cost=1974.34..11637.18 rows=1 width=84) (actual time=464.418..482.188 rows=10 loops=1)
Buffers: shared hit=224 read=395
I/O Timings: read=470.706
-> Bitmap Heap Scan on public.packages_packages (cost=397.97..502.34 rows=7 width=84) (actual time=272.106..289.407 rows=10 loops=1)
Filter: ((packages_packages.version IS NOT NULL) AND (packages_packages.package_type = 4) AND ((packages_packages.package_type <> 4) OR ((packages_packages.name)::text <> 'NuGet.Temporary.Package'::text)))
Rows Removed by Filter: 0
Buffers: shared hit=37 read=291
I/O Timings: read=281.048
-> Bitmap Index Scan using index_packages_packages_on_name_trigram (cost=0.00..397.96 rows=62 width=0) (actual time=263.035..263.036 rows=13 loops=1)
Index Cond: ((packages_packages.name)::text ~~* 'Bitwolf'::text)
Buffers: shared hit=37 read=279
I/O Timings: read=255.027
-> Nested Loop Semi Join (cost=1576.37..1590.68 rows=1 width=4) (actual time=19.274..19.275 rows=1 loops=10)
Buffers: shared hit=187 read=104
I/O Timings: read=189.658
-> Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects (cost=0.56..7.18 rows=1 width=8) (actual time=2.235..2.235 rows=1 loops=10)
Index Cond: (projects.id = packages_packages.project_id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=82 read=9
I/O Timings: read=22.011
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=1.477..1.477 rows=1 loops=10)
Index Cond: ((project_authorizations.user_id = 3983112) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 0
Buffers: shared hit=36 read=5
I/O Timings: read=14.613
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..887.19 rows=3108 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 3983112) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> CTE Scan on base_and_descendants namespaces (cost=1575.81..1579.43 rows=181 width=4) (actual time=1.108..17.025 rows=49 loops=10)
Buffers: shared hit=105 read=95
I/O Timings: read=167.647
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=11.073..169.750 rows=49 loops=1)
Buffers: shared hit=105 read=95
I/O Timings: read=167.647
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=11.059..11.061 rows=1 loops=1)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 785414))
Buffers: shared read=4
I/O Timings: read=10.942
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=4.383..79.104 rows=24 loops=2)
Buffers: shared hit=105 read=91
I/O Timings: read=156.704
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.018 rows=18 loops=2)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.65 rows=2 width=344) (actual time=1.650..4.269 rows=1 loops=37)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=105 read=91
I/O Timings: read=156.704With this MR (feature flag of !56078 (merged) disabled)
https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/2691/commands/8293
SQL query
SELECT "packages_packages".*
FROM "packages_packages"
WHERE "packages_packages"."project_id" IN (
SELECT "projects"."id"
FROM "projects"
WHERE "projects"."namespace_id" IN (
WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 785414) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT id
FROM "base_and_descendants" AS "namespaces"
)
AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 3983112 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10,20))
)
AND "packages_packages"."package_type" = 4
AND "packages_packages"."version" IS NOT NULL
AND ("packages_packages"."package_type" != 4 OR "packages_packages"."name" != 'NuGet.Temporary.Package')
AND "packages_packages"."name" ILIKE 'Bitwolf'
ORDER BY created_at DESC
LIMIT 300explain plan
Limit (cost=11302.96..11302.96 rows=1 width=84) (actual time=17.248..17.259 rows=10 loops=1)
Buffers: shared hit=227 read=171
I/O Timings: read=13.778
-> Sort (cost=11302.96..11302.96 rows=1 width=84) (actual time=17.245..17.254 rows=10 loops=1)
Sort Key: packages_packages.created_at DESC
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=227 read=171
I/O Timings: read=13.778
-> Nested Loop Semi Join (cost=1638.34..11302.95 rows=1 width=84) (actual time=16.117..17.194 rows=10 loops=1)
Buffers: shared hit=224 read=171
I/O Timings: read=13.778
-> Bitmap Heap Scan on public.packages_packages (cost=61.97..168.10 rows=7 width=84) (actual time=4.412..5.165 rows=10 loops=1)
Filter: ((packages_packages.version IS NOT NULL) AND (packages_packages.package_type = 4) AND ((packages_packages.package_type <> 4) OR ((packages_packages.name)::text <> 'NuGet.Temporary.Package'::text)))
Rows Removed by Filter: 0
Buffers: shared hit=37 read=67
I/O Timings: read=3.403
-> Bitmap Index Scan using index_packages_packages_on_name_trigram (cost=0.00..61.97 rows=63 width=0) (actual time=4.259..4.260 rows=13 loops=1)
Index Cond: ((packages_packages.name)::text ~~* 'Bitwolf'::text)
Buffers: shared hit=37 read=55
I/O Timings: read=2.651
-> Nested Loop Semi Join (cost=1576.37..1590.68 rows=1 width=4) (actual time=1.200..1.201 rows=1 loops=10)
Buffers: shared hit=187 read=104
I/O Timings: read=10.375
-> Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects (cost=0.56..7.18 rows=1 width=8) (actual time=0.146..0.146 rows=1 loops=10)
Index Cond: (projects.id = packages_packages.project_id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=82 read=9
I/O Timings: read=1.263
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.089..0.089 rows=1 loops=10)
Index Cond: ((project_authorizations.user_id = 3983112) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 0
Buffers: shared hit=36 read=5
I/O Timings: read=0.790
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..1060.31 rows=3120 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 3983112) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> CTE Scan on base_and_descendants namespaces (cost=1575.81..1579.43 rows=181 width=4) (actual time=0.059..1.044 rows=49 loops=10)
Buffers: shared hit=105 read=95
I/O Timings: read=9.112
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=0.581..10.184 rows=49 loops=1)
Buffers: shared hit=105 read=95
I/O Timings: read=9.112
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.566..0.568 rows=1 loops=1)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 785414))
Buffers: shared read=4
I/O Timings: read=0.526
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=0.293..4.650 rows=24 loops=2)
Buffers: shared hit=105 read=91
I/O Timings: read=8.585
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.009 rows=18 loops=2)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.65 rows=2 width=344) (actual time=0.084..0.248 rows=1 loops=37)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=105 read=91
I/O Timings: read=8.585With this MR (feature flag of !56078 (merged) enabled)
https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/2695/commands/8298
SQL query
SELECT "packages_packages".*
FROM "packages_packages"
WHERE "packages_packages"."project_id" IN (
SELECT "projects"."id"
FROM "projects"
INNER JOIN (
WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 785414) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "id"
FROM "base_and_descendants" AS "namespaces"
) namespaces
ON namespaces.id=projects.namespace_id
WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 3983112 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10,20))
)
AND "packages_packages"."package_type" = 4
AND "packages_packages"."version" IS NOT NULL
AND ("packages_packages"."package_type" != 4 OR "packages_packages"."name" != 'NuGet.Temporary.Package')
AND "packages_packages"."name" ILIKE 'Bitwolf'
ORDER BY created_at DESC
LIMIT 300explain plan
Limit (cost=1794.02..1794.02 rows=1 width=84) (actual time=261.439..261.452 rows=10 loops=1)
Buffers: shared hit=227 read=171
I/O Timings: read=255.206
-> Sort (cost=1794.02..1794.02 rows=1 width=84) (actual time=261.437..261.448 rows=10 loops=1)
Sort Key: packages_packages.created_at DESC
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=227 read=171
I/O Timings: read=255.206
-> Nested Loop Semi Join (cost=1644.97..1794.01 rows=1 width=84) (actual time=244.878..261.373 rows=10 loops=1)
Buffers: shared hit=224 read=171
I/O Timings: read=255.206
-> Bitmap Heap Scan on public.packages_packages (cost=61.97..168.10 rows=7 width=84) (actual time=64.696..80.655 rows=10 loops=1)
Filter: ((packages_packages.version IS NOT NULL) AND (packages_packages.package_type = 4) AND ((packages_packages.package_type <> 4) OR ((packages_packages.name)::text <> 'NuGet.Temporary.Package'::text)))
Rows Removed by Filter: 0
Buffers: shared hit=37 read=67
I/O Timings: read=77.452
-> Bitmap Index Scan using index_packages_packages_on_name_trigram (cost=0.00..61.97 rows=63 width=0) (actual time=62.698..62.699 rows=13 loops=1)
Index Cond: ((packages_packages.name)::text ~~* 'Bitwolf'::text)
Buffers: shared hit=37 read=55
I/O Timings: read=59.813
-> Hash Join (cost=1583.00..1589.12 rows=1 width=4) (actual time=18.065..18.065 rows=1 loops=10)
Hash Cond: (namespaces.id = projects.namespace_id)
Buffers: shared hit=187 read=104
I/O Timings: read=177.754
-> CTE Scan on base_and_descendants namespaces (cost=1575.81..1579.43 rows=181 width=4) (actual time=0.693..15.886 rows=49 loops=10)
Buffers: shared hit=105 read=95
I/O Timings: read=156.632
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=6.916..158.550 rows=49 loops=1)
Buffers: shared hit=105 read=95
I/O Timings: read=156.632
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=6.896..6.899 rows=1 loops=1)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 785414))
Buffers: shared read=4
I/O Timings: read=6.823
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=5.123..75.610 rows=24 loops=2)
Buffers: shared hit=105 read=91
I/O Timings: read=149.810
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.003..0.019 rows=18 loops=2)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.65 rows=2 width=344) (actual time=1.663..4.080 rows=1 loops=37)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=105 read=91
I/O Timings: read=149.810
-> Hash (cost=7.18..7.18 rows=1 width=8) (actual time=2.158..2.158 rows=1 loops=10)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=82 read=9
I/O Timings: read=21.122
-> Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects (cost=0.56..7.18 rows=1 width=8) (actual time=2.152..2.153 rows=1 loops=10)
Index Cond: (projects.id = packages_packages.project_id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=82 read=9
I/O Timings: read=21.122
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=1.302..1.302 rows=1 loops=10)
Index Cond: ((project_authorizations.user_id = 3983112) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 0
Buffers: shared hit=36 read=5
I/O Timings: read=12.799
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..1060.31 rows=3120 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 3983112) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0Conclusions
- I see strange variances on the timings. From 17ms to 480ms.
- Cost seems to be reduced with the feature flag enabled
✅
Nuget search service count
Before this MR
https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/2696/commands/8299
SQL query
WITH "project_ids" AS (
SELECT "projects"."id"
FROM "projects"
WHERE "projects"."namespace_id" IN (
WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 785414) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "id"
FROM "base_and_descendants" AS "namespaces"
)
AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 3983112 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10,20))
) SELECT COUNT(DISTINCT "packages_packages"."name")
FROM "packages_packages"
WHERE "packages_packages"."package_type" = 4
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."name" != 'NuGet.Temporary.Package'
AND "packages_packages"."project_id" IN (
SELECT "id"
FROM "project_ids"
)
AND "packages_packages"."name" ILIKE '%itwol%'explain plan
Aggregate (cost=19523.83..19523.84 rows=1 width=8) (actual time=3202.569..3202.583 rows=1 loops=1)
Buffers: shared hit=7416 read=1985 dirtied=56
I/O Timings: read=3143.970
CTE project_ids
-> Nested Loop (cost=1582.13..19333.79 rows=1979 width=4) (actual time=244.990..3152.676 rows=1656 loops=1)
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=3102.228
-> HashAggregate (cost=1581.69..1583.50 rows=181 width=4) (actual time=210.638..210.823 rows=60 loops=1)
Group Key: namespaces_2.id
Buffers: shared hit=184 read=119
I/O Timings: read=207.710
-> CTE Scan on base_and_descendants namespaces_2 (cost=1575.81..1579.43 rows=181 width=4) (actual time=6.778..210.503 rows=60 loops=1)
Buffers: shared hit=184 read=119
I/O Timings: read=207.710
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=6.774..210.269 rows=60 loops=1)
Buffers: shared hit=184 read=119
I/O Timings: read=207.710
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces (cost=0.43..3.45 rows=1 width=344) (actual time=6.760..6.763 rows=1 loops=1)
Index Cond: (((namespaces.type)::text = 'Group'::text) AND (namespaces.id = 785414))
Buffers: shared read=4
I/O Timings: read=6.694
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=4.208..50.739 rows=15 loops=4)
Buffers: shared hit=184 read=115
I/O Timings: read=201.016
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.013 rows=15 loops=4)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_1 (cost=0.56..15.65 rows=2 width=344) (actual time=1.519..3.377 rows=1 loops=60)
Index Cond: (namespaces_1.parent_id = base_and_descendants.id)
Filter: ((namespaces_1.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=184 read=115
I/O Timings: read=201.016
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..97.96 rows=11 width=8) (actual time=5.254..49.006 rows=28 loops=60)
Index Cond: (projects.namespace_id = namespaces_2.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=7218 read=1835 dirtied=56
I/O Timings: read=2894.518
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.111..0.111 rows=1 loops=1656)
Index Cond: ((project_authorizations.user_id = 3983112) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 506
Buffers: shared hit=7102 read=110 dirtied=13
I/O Timings: read=163.634
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..1060.31 rows=3120 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 3983112) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> Hash Join (cost=84.00..190.04 rows=4 width=34) (actual time=3183.489..3202.476 rows=10 loops=1)
Hash Cond: (packages_packages.project_id = project_ids.id)
Buffers: shared hit=7406 read=1985 dirtied=56
I/O Timings: read=3143.970
-> Bitmap Heap Scan on public.packages_packages (cost=34.97..140.95 rows=7 width=38) (actual time=23.458..42.418 rows=10 loops=1)
Filter: ((packages_packages.version IS NOT NULL) AND ((packages_packages.name)::text <> 'NuGet.Temporary.Package'::text) AND (packages_packages.package_type = 4))
Rows Removed by Filter: 3
Buffers: shared hit=4 read=31
I/O Timings: read=41.742
-> Bitmap Index Scan using index_packages_packages_on_name_trigram (cost=0.00..34.97 rows=63 width=0) (actual time=21.069..21.070 rows=13 loops=1)
Index Cond: ((packages_packages.name)::text ~~* '%itwol%'::text)
Buffers: shared hit=4 read=19
I/O Timings: read=20.725
-> Hash (cost=46.53..46.53 rows=200 width=4) (actual time=3159.968..3159.969 rows=1656 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 75kB
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=3102.228
-> HashAggregate (cost=44.53..46.53 rows=200 width=4) (actual time=3159.216..3159.572 rows=1656 loops=1)
Group Key: project_ids.id
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=3102.228
-> CTE Scan on project_ids (cost=0.00..39.58 rows=1979 width=4) (actual time=245.021..3155.527 rows=1656 loops=1)
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=3102.228With this MR (feature flag of !56078 (merged) disabled)
https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/2698/commands/8301
SQL query
WITH "project_ids" AS (
SELECT "projects"."id"
FROM "projects"
WHERE "projects"."namespace_id" IN (
WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 785414) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT id
FROM "base_and_descendants" AS "namespaces"
)
AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 3983112 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10,20))
) SELECT COUNT(DISTINCT "packages_packages"."name")
FROM "packages_packages"
WHERE "packages_packages"."package_type" = 4
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."name" != 'NuGet.Temporary.Package'
AND "packages_packages"."project_id" IN (
SELECT "id"
FROM "project_ids"
)
AND "packages_packages"."name" ILIKE '%itwol%'explain plan
Aggregate (cost=19523.83..19523.84 rows=1 width=8) (actual time=2702.987..2703.011 rows=1 loops=1)
Buffers: shared hit=7416 read=1985 dirtied=56
I/O Timings: read=2642.236
CTE project_ids
-> Nested Loop (cost=1582.13..19333.79 rows=1979 width=4) (actual time=210.671..2655.995 rows=1656 loops=1)
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=2604.496
-> HashAggregate (cost=1581.69..1583.50 rows=181 width=4) (actual time=186.858..187.009 rows=60 loops=1)
Group Key: namespaces_2.id
Buffers: shared hit=184 read=119
I/O Timings: read=183.777
-> CTE Scan on base_and_descendants namespaces_2 (cost=1575.81..1579.43 rows=181 width=4) (actual time=8.173..186.728 rows=60 loops=1)
Buffers: shared hit=184 read=119
I/O Timings: read=183.777
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=8.170..186.501 rows=60 loops=1)
Buffers: shared hit=184 read=119
I/O Timings: read=183.777
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces (cost=0.43..3.45 rows=1 width=344) (actual time=8.155..8.158 rows=1 loops=1)
Index Cond: (((namespaces.type)::text = 'Group'::text) AND (namespaces.id = 785414))
Buffers: shared read=4
I/O Timings: read=8.084
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=3.341..44.442 rows=15 loops=4)
Buffers: shared hit=184 read=115
I/O Timings: read=175.693
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.012 rows=15 loops=4)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_1 (cost=0.56..15.65 rows=2 width=344) (actual time=1.314..2.958 rows=1 loops=60)
Index Cond: (namespaces_1.parent_id = base_and_descendants.id)
Filter: ((namespaces_1.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=184 read=115
I/O Timings: read=175.693
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..97.96 rows=11 width=8) (actual time=4.149..41.126 rows=28 loops=60)
Index Cond: (projects.namespace_id = namespaces_2.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=7218 read=1835 dirtied=56
I/O Timings: read=2420.719
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.105..0.105 rows=1 loops=1656)
Index Cond: ((project_authorizations.user_id = 3983112) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 506
Buffers: shared hit=7102 read=110 dirtied=13
I/O Timings: read=153.545
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..1060.31 rows=3120 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 3983112) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> Hash Join (cost=84.00..190.04 rows=4 width=34) (actual time=2689.998..2702.874 rows=10 loops=1)
Hash Cond: (packages_packages.project_id = project_ids.id)
Buffers: shared hit=7406 read=1985 dirtied=56
I/O Timings: read=2642.236
-> Bitmap Heap Scan on public.packages_packages (cost=34.97..140.95 rows=7 width=38) (actual time=25.507..38.359 rows=10 loops=1)
Filter: ((packages_packages.version IS NOT NULL) AND ((packages_packages.name)::text <> 'NuGet.Temporary.Package'::text) AND (packages_packages.package_type = 4))
Rows Removed by Filter: 3
Buffers: shared hit=4 read=31
I/O Timings: read=37.740
-> Bitmap Index Scan using index_packages_packages_on_name_trigram (cost=0.00..34.97 rows=63 width=0) (actual time=22.938..22.939 rows=13 loops=1)
Index Cond: ((packages_packages.name)::text ~~* '%itwol%'::text)
Buffers: shared hit=4 read=19
I/O Timings: read=22.572
-> Hash (cost=46.53..46.53 rows=200 width=4) (actual time=2664.447..2664.449 rows=1656 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 75kB
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=2604.496
-> HashAggregate (cost=44.53..46.53 rows=200 width=4) (actual time=2663.166..2663.838 rows=1656 loops=1)
Group Key: project_ids.id
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=2604.496
-> CTE Scan on project_ids (cost=0.00..39.58 rows=1979 width=4) (actual time=210.676..2659.284 rows=1656 loops=1)
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=2604.496With this MR (feature flag of !56078 (merged) enabled)
https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/2699/commands/8304
SQL query
WITH "project_ids" AS (
SELECT "projects"."id"
FROM "projects"
INNER JOIN (
WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 785414) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "id"
FROM "base_and_descendants" AS "namespaces"
) namespaces
ON namespaces.id=projects.namespace_id
WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 3983112 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10,20))
) SELECT COUNT(DISTINCT "packages_packages"."name")
FROM "packages_packages"
WHERE "packages_packages"."package_type" = 4
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."name" != 'NuGet.Temporary.Package'
AND "packages_packages"."project_id" IN (
SELECT "id"
FROM "project_ids"
)
AND "packages_packages"."name" ILIKE '%itwol%'explain plan
Aggregate (cost=19521.57..19521.58 rows=1 width=8) (actual time=2733.501..2733.512 rows=1 loops=1)
Buffers: shared hit=7408 read=1985 dirtied=56
I/O Timings: read=2678.397
CTE project_ids
-> Nested Loop (cost=1576.25..19331.52 rows=1979 width=4) (actual time=27.859..2686.706 rows=1656 loops=1)
Buffers: shared hit=7394 read=1954 dirtied=56
I/O Timings: read=2639.382
-> CTE Scan on base_and_descendants namespaces_2 (cost=1575.81..1579.43 rows=181 width=4) (actual time=8.300..223.644 rows=60 loops=1)
Buffers: shared hit=184 read=119
I/O Timings: read=220.139
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=8.294..223.232 rows=60 loops=1)
Buffers: shared hit=184 read=119
I/O Timings: read=220.139
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces (cost=0.43..3.45 rows=1 width=344) (actual time=8.277..8.280 rows=1 loops=1)
Index Cond: (((namespaces.type)::text = 'Group'::text) AND (namespaces.id = 785414))
Buffers: shared read=4
I/O Timings: read=8.212
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=3.752..53.561 rows=15 loops=4)
Buffers: shared hit=184 read=115
I/O Timings: read=211.927
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.014 rows=15 loops=4)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_1 (cost=0.56..15.65 rows=2 width=344) (actual time=1.581..3.561 rows=1 loops=60)
Index Cond: (namespaces_1.parent_id = base_and_descendants.id)
Filter: ((namespaces_1.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=184 read=115
I/O Timings: read=211.927
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..97.96 rows=11 width=8) (actual time=3.651..41.027 rows=28 loops=60)
Index Cond: (projects.namespace_id = namespaces_2.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=7210 read=1835 dirtied=56
I/O Timings: read=2419.243
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.110..0.110 rows=1 loops=1656)
Index Cond: ((project_authorizations.user_id = 3983112) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 506
Buffers: shared hit=7094 read=110 dirtied=13
I/O Timings: read=165.319
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..1060.31 rows=3120 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 3983112) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> Hash Join (cost=84.00..190.04 rows=4 width=34) (actual time=2719.757..2733.352 rows=10 loops=1)
Hash Cond: (packages_packages.project_id = project_ids.id)
Buffers: shared hit=7398 read=1985 dirtied=56
I/O Timings: read=2678.397
-> Bitmap Heap Scan on public.packages_packages (cost=34.97..140.95 rows=7 width=38) (actual time=26.064..39.637 rows=10 loops=1)
Filter: ((packages_packages.version IS NOT NULL) AND ((packages_packages.name)::text <> 'NuGet.Temporary.Package'::text) AND (packages_packages.package_type = 4))
Rows Removed by Filter: 3
Buffers: shared hit=4 read=31
I/O Timings: read=39.015
-> Bitmap Index Scan using index_packages_packages_on_name_trigram (cost=0.00..34.97 rows=63 width=0) (actual time=23.394..23.394 rows=13 loops=1)
Index Cond: ((packages_packages.name)::text ~~* '%itwol%'::text)
Buffers: shared hit=4 read=19
I/O Timings: read=23.032
-> Hash (cost=46.53..46.53 rows=200 width=4) (actual time=2693.660..2693.662 rows=1656 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 75kB
Buffers: shared hit=7394 read=1954 dirtied=56
I/O Timings: read=2639.382
-> HashAggregate (cost=44.53..46.53 rows=200 width=4) (actual time=2692.610..2693.156 rows=1656 loops=1)
Group Key: project_ids.id
Buffers: shared hit=7394 read=1954 dirtied=56
I/O Timings: read=2639.382
-> CTE Scan on project_ids (cost=0.00..39.58 rows=1979 width=4) (actual time=27.864..2689.302 rows=1656 loops=1)
Buffers: shared hit=7394 read=1954 dirtied=56
I/O Timings: read=2639.382Conclusions
- I don't see an improvement here
🤔
Nuget search service results
Before this MR
https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/2697/commands/8300
SQL query
WITH "project_ids" AS (
SELECT "projects"."id"
FROM "projects"
WHERE "projects"."namespace_id" IN (
WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 785414) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "id"
FROM "base_and_descendants" AS "namespaces"
)
AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 3983112 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10,20))
) SELECT partition_subquery.id,
partition_subquery.project_id,
partition_subquery.created_at,
partition_subquery.updated_at,
partition_subquery.name,
partition_subquery.version,
partition_subquery.package_type,
partition_subquery.creator_id,
partition_subquery.status
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY packages_packages.name ORDER BY packages_packages.created_at DESC) AS row_number,
packages_packages.*
FROM "packages_packages"
WHERE "packages_packages"."package_type" = 4
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."name" != 'NuGet.Temporary.Package'
AND "packages_packages"."name" IN (
SELECT DISTINCT "packages_packages"."name"
FROM "packages_packages"
WHERE "packages_packages"."package_type" = 4
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."name" != 'NuGet.Temporary.Package'
AND "packages_packages"."project_id" IN (
SELECT "id"
FROM "project_ids"
)
AND "packages_packages"."name" ILIKE '%itwol%'
ORDER BY name ASC
LIMIT 20
OFFSET 0
)
AND "packages_packages"."project_id" IN (
SELECT "id"
FROM "project_ids"
)
) partition_subquery
WHERE "partition_subquery"."row_number" <= 10explain plan
Subquery Scan on partition_subquery (cost=19708.47..19708.60 rows=1 width=84) (actual time=628.338..628.395 rows=10 loops=1)
Filter: (partition_subquery.row_number <= 10)
Rows Removed by Filter: 0
Buffers: shared hit=7421 read=1989 dirtied=56
I/O Timings: read=595.628
CTE project_ids
-> Nested Loop (cost=1582.13..19333.79 rows=1979 width=4) (actual time=78.863..604.553 rows=1656 loops=1)
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=578.367
-> HashAggregate (cost=1581.69..1583.50 rows=181 width=4) (actual time=67.665..67.749 rows=60 loops=1)
Group Key: namespaces_2.id
Buffers: shared hit=184 read=119
I/O Timings: read=65.742
-> CTE Scan on base_and_descendants namespaces_2 (cost=1575.81..1579.43 rows=181 width=4) (actual time=4.564..67.575 rows=60 loops=1)
Buffers: shared hit=184 read=119
I/O Timings: read=65.742
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=4.561..67.393 rows=60 loops=1)
Buffers: shared hit=184 read=119
I/O Timings: read=65.742
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces (cost=0.43..3.45 rows=1 width=344) (actual time=4.534..4.536 rows=1 loops=1)
Index Cond: (((namespaces.type)::text = 'Group'::text) AND (namespaces.id = 785414))
Buffers: shared read=4
I/O Timings: read=4.485
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=1.131..15.599 rows=15 loops=4)
Buffers: shared hit=184 read=115
I/O Timings: read=61.257
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.006 rows=15 loops=4)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_1 (cost=0.56..15.65 rows=2 width=344) (actual time=0.274..1.037 rows=1 loops=60)
Index Cond: (namespaces_1.parent_id = base_and_descendants.id)
Filter: ((namespaces_1.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=184 read=115
I/O Timings: read=61.257
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..97.96 rows=11 width=8) (actual time=1.187..8.934 rows=28 loops=60)
Index Cond: (projects.namespace_id = namespaces_2.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=7218 read=1835 dirtied=56
I/O Timings: read=512.626
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=1656)
Index Cond: ((project_authorizations.user_id = 3983112) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 506
Buffers: shared hit=7102 read=110 dirtied=13
I/O Timings: read=19.800
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..1060.31 rows=3120 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 3983112) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> WindowAgg (cost=374.69..374.77 rows=4 width=92) (actual time=628.335..628.375 rows=10 loops=1)
Buffers: shared hit=7421 read=1989 dirtied=56
I/O Timings: read=595.628
-> Sort (cost=374.69..374.70 rows=4 width=84) (actual time=628.316..628.330 rows=10 loops=1)
Sort Key: packages_packages.name, packages_packages.created_at DESC
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=7421 read=1989 dirtied=56
I/O Timings: read=595.628
-> Hash Join (cost=239.53..374.65 rows=4 width=84) (actual time=625.948..628.261 rows=10 loops=1)
Hash Cond: (packages_packages.project_id = project_ids.id)
Buffers: shared hit=7415 read=1989 dirtied=56
I/O Timings: read=595.628
-> Nested Loop (cost=190.50..325.55 rows=9 width=84) (actual time=17.146..19.448 rows=10 loops=1)
Buffers: shared hit=13 read=35
I/O Timings: read=17.260
-> Limit (cost=190.08..190.10 rows=4 width=34) (actual time=12.688..12.700 rows=1 loops=1)
Buffers: shared hit=4 read=31
I/O Timings: read=10.610
-> Unique (cost=190.08..190.10 rows=4 width=34) (actual time=12.685..12.697 rows=1 loops=1)
Buffers: shared hit=4 read=31
I/O Timings: read=10.610
-> Sort (cost=190.08..190.09 rows=4 width=34) (actual time=12.684..12.690 rows=10 loops=1)
Sort Key: packages_packages_1.name
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=4 read=31
I/O Timings: read=10.610
-> Hash Join (cost=84.00..190.04 rows=4 width=34) (actual time=9.610..12.656 rows=10 loops=1)
Hash Cond: (packages_packages_1.project_id = project_ids_1.id)
Buffers: shared hit=4 read=31
I/O Timings: read=10.610
-> Bitmap Heap Scan on public.packages_packages packages_packages_1 (cost=34.97..140.95 rows=7 width=38) (actual time=7.990..11.026 rows=10 loops=1)
Filter: ((packages_packages_1.version IS NOT NULL) AND ((packages_packages_1.name)::text <> 'NuGet.Temporary.Package'::text) AND (packages_packages_1.package_type = 4))
Rows Removed by Filter: 3
Buffers: shared hit=4 read=31
I/O Timings: read=10.610
-> Bitmap Index Scan using index_packages_packages_on_name_trigram (cost=0.00..34.97 rows=63 width=0) (actual time=6.255..6.255 rows=13 loops=1)
Index Cond: ((packages_packages_1.name)::text ~~* '%itwol%'::text)
Buffers: shared hit=4 read=19
I/O Timings: read=5.981
-> Hash (cost=46.53..46.53 rows=200 width=4) (actual time=1.588..1.589 rows=1656 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 75kB
-> HashAggregate (cost=44.53..46.53 rows=200 width=4) (actual time=0.886..1.173 rows=1656 loops=1)
Group Key: project_ids_1.id
-> CTE Scan on project_ids project_ids_1 (cost=0.00..39.58 rows=1979 width=4) (actual time=0.002..0.243 rows=1656 loops=1)
-> Index Scan using package_name_index on public.packages_packages (cost=0.42..33.83 rows=2 width=84) (actual time=4.452..6.735 rows=10 loops=1)
Index Cond: ((packages_packages.name)::text = (packages_packages_1.name)::text)
Filter: ((packages_packages.version IS NOT NULL) AND ((packages_packages.name)::text <> 'NuGet.Temporary.Package'::text) AND (packages_packages.package_type = 4))
Rows Removed by Filter: 0
Buffers: shared hit=9 read=4
I/O Timings: read=6.650
-> Hash (cost=46.53..46.53 rows=200 width=4) (actual time=608.777..608.779 rows=1656 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 75kB
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=578.367
-> HashAggregate (cost=44.53..46.53 rows=200 width=4) (actual time=607.916..608.359 rows=1656 loops=1)
Group Key: project_ids.id
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=578.367
-> CTE Scan on project_ids (cost=0.00..39.58 rows=1979 width=4) (actual time=78.866..605.974 rows=1656 loops=1)
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=578.367With this MR (feature flag of !56078 (merged) disabled)
https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/2699/commands/8302
SQL query
WITH "project_ids" AS (
SELECT "projects"."id"
FROM "projects"
WHERE "projects"."namespace_id" IN (
WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 785414) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT id
FROM "base_and_descendants" AS "namespaces"
)
AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 3983112 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10,20))
) SELECT partition_subquery.id,
partition_subquery.project_id,
partition_subquery.created_at,
partition_subquery.updated_at,
partition_subquery.name,
partition_subquery.version,
partition_subquery.package_type,
partition_subquery.creator_id,
partition_subquery.status
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY packages_packages.name ORDER BY packages_packages.created_at DESC) AS row_number,
packages_packages.*
FROM "packages_packages"
WHERE "packages_packages"."package_type" = 4
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."name" != 'NuGet.Temporary.Package'
AND "packages_packages"."name" IN (
SELECT DISTINCT "packages_packages"."name"
FROM "packages_packages"
WHERE "packages_packages"."package_type" = 4
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."name" != 'NuGet.Temporary.Package'
AND "packages_packages"."project_id" IN (
SELECT "id"
FROM "project_ids"
)
AND "packages_packages"."name" ILIKE '%itwol%'
ORDER BY name ASC
LIMIT 20
OFFSET 0
)
AND "packages_packages"."project_id" IN (
SELECT "id"
FROM "project_ids"
)
) partition_subquery
WHERE "partition_subquery"."row_number" <= 10explain plan
Subquery Scan on partition_subquery (cost=19708.47..19708.60 rows=1 width=84) (actual time=2682.791..2682.830 rows=10 loops=1)
Filter: (partition_subquery.row_number <= 10)
Rows Removed by Filter: 0
Buffers: shared hit=7421 read=1989 dirtied=56
I/O Timings: read=2615.684
CTE project_ids
-> Nested Loop (cost=1582.13..19333.79 rows=1979 width=4) (actual time=193.139..2622.406 rows=1656 loops=1)
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=2567.150
-> HashAggregate (cost=1581.69..1583.50 rows=181 width=4) (actual time=172.479..172.644 rows=60 loops=1)
Group Key: namespaces_2.id
Buffers: shared hit=184 read=119
I/O Timings: read=170.435
-> CTE Scan on base_and_descendants namespaces_2 (cost=1575.81..1579.43 rows=181 width=4) (actual time=7.571..172.381 rows=60 loops=1)
Buffers: shared hit=184 read=119
I/O Timings: read=170.435
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=7.567..172.216 rows=60 loops=1)
Buffers: shared hit=184 read=119
I/O Timings: read=170.435
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces (cost=0.43..3.45 rows=1 width=344) (actual time=7.544..7.547 rows=1 loops=1)
Index Cond: (((namespaces.type)::text = 'Group'::text) AND (namespaces.id = 785414))
Buffers: shared read=4
I/O Timings: read=7.480
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=3.022..41.061 rows=15 loops=4)
Buffers: shared hit=184 read=115
I/O Timings: read=162.954
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.009 rows=15 loops=4)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_1 (cost=0.56..15.65 rows=2 width=344) (actual time=1.132..2.734 rows=1 loops=60)
Index Cond: (namespaces_1.parent_id = base_and_descendants.id)
Filter: ((namespaces_1.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=184 read=115
I/O Timings: read=162.954
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..97.96 rows=11 width=8) (actual time=4.308..40.800 rows=28 loops=60)
Index Cond: (projects.namespace_id = namespaces_2.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=7218 read=1835 dirtied=56
I/O Timings: read=2396.716
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.134..0.134 rows=1 loops=1656)
Index Cond: ((project_authorizations.user_id = 3983112) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 506
Buffers: shared hit=7102 read=110 dirtied=13
I/O Timings: read=198.786
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..1060.31 rows=3120 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 3983112) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> WindowAgg (cost=374.69..374.77 rows=4 width=92) (actual time=2682.788..2682.809 rows=10 loops=1)
Buffers: shared hit=7421 read=1989 dirtied=56
I/O Timings: read=2615.684
-> Sort (cost=374.69..374.70 rows=4 width=84) (actual time=2682.768..2682.777 rows=10 loops=1)
Sort Key: packages_packages.name, packages_packages.created_at DESC
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=7421 read=1989 dirtied=56
I/O Timings: read=2615.684
-> Hash Join (cost=239.53..374.65 rows=4 width=84) (actual time=2678.370..2682.724 rows=10 loops=1)
Hash Cond: (packages_packages.project_id = project_ids.id)
Buffers: shared hit=7415 read=1989 dirtied=56
I/O Timings: read=2615.684
-> Nested Loop (cost=190.50..325.55 rows=9 width=84) (actual time=47.322..51.664 rows=10 loops=1)
Buffers: shared hit=13 read=35
I/O Timings: read=48.534
-> Limit (cost=190.08..190.10 rows=4 width=34) (actual time=42.361..42.374 rows=1 loops=1)
Buffers: shared hit=4 read=31
I/O Timings: read=39.357
-> Unique (cost=190.08..190.10 rows=4 width=34) (actual time=42.359..42.371 rows=1 loops=1)
Buffers: shared hit=4 read=31
I/O Timings: read=39.357
-> Sort (cost=190.08..190.09 rows=4 width=34) (actual time=42.357..42.364 rows=10 loops=1)
Sort Key: packages_packages_1.name
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=4 read=31
I/O Timings: read=39.357
-> Hash Join (cost=84.00..190.04 rows=4 width=34) (actual time=27.344..42.325 rows=10 loops=1)
Hash Cond: (packages_packages_1.project_id = project_ids_1.id)
Buffers: shared hit=4 read=31
I/O Timings: read=39.357
-> Bitmap Heap Scan on public.packages_packages packages_packages_1 (cost=34.97..140.95 rows=7 width=38) (actual time=25.100..40.050 rows=10 loops=1)
Filter: ((packages_packages_1.version IS NOT NULL) AND ((packages_packages_1.name)::text <> 'NuGet.Temporary.Package'::text) AND (packages_packages_1.package_type = 4))
Rows Removed by Filter: 3
Buffers: shared hit=4 read=31
I/O Timings: read=39.357
-> Bitmap Index Scan using index_packages_packages_on_name_trigram (cost=0.00..34.97 rows=63 width=0) (actual time=22.529..22.529 rows=13 loops=1)
Index Cond: ((packages_packages_1.name)::text ~~* '%itwol%'::text)
Buffers: shared hit=4 read=19
I/O Timings: read=22.140
-> Hash (cost=46.53..46.53 rows=200 width=4) (actual time=2.206..2.208 rows=1656 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 75kB
-> HashAggregate (cost=44.53..46.53 rows=200 width=4) (actual time=1.181..1.644 rows=1656 loops=1)
Group Key: project_ids_1.id
-> CTE Scan on project_ids project_ids_1 (cost=0.00..39.58 rows=1979 width=4) (actual time=0.002..0.394 rows=1656 loops=1)
-> Index Scan using package_name_index on public.packages_packages (cost=0.42..33.83 rows=2 width=84) (actual time=4.951..9.276 rows=10 loops=1)
Index Cond: ((packages_packages.name)::text = (packages_packages_1.name)::text)
Filter: ((packages_packages.version IS NOT NULL) AND ((packages_packages.name)::text <> 'NuGet.Temporary.Package'::text) AND (packages_packages.package_type = 4))
Rows Removed by Filter: 0
Buffers: shared hit=9 read=4
I/O Timings: read=9.177
-> Hash (cost=46.53..46.53 rows=200 width=4) (actual time=2631.027..2631.028 rows=1656 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 75kB
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=2567.150
-> HashAggregate (cost=44.53..46.53 rows=200 width=4) (actual time=2629.937..2630.474 rows=1656 loops=1)
Group Key: project_ids.id
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=2567.150
-> CTE Scan on project_ids (cost=0.00..39.58 rows=1979 width=4) (actual time=193.142..2625.491 rows=1656 loops=1)
Buffers: shared hit=7402 read=1954 dirtied=56
I/O Timings: read=2567.150With this MR (feature flag of !56078 (merged) enabled)
SQL query
WITH "project_ids" AS (
SELECT "projects"."id"
FROM "projects"
INNER JOIN (
WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 785414) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "id"
FROM "base_and_descendants" AS "namespaces"
) namespaces
ON namespaces.id=projects.namespace_id
WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" =3983112 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10,20))
) SELECT partition_subquery.id,
partition_subquery.project_id,
partition_subquery.created_at,
partition_subquery.updated_at,
partition_subquery.name,
partition_subquery.version,
partition_subquery.package_type,
partition_subquery.creator_id,
partition_subquery.status
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY packages_packages.name ORDER BY packages_packages.created_at DESC) AS row_number,
packages_packages.*
FROM "packages_packages"
WHERE "packages_packages"."package_type" = 4
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."name" != 'NuGet.Temporary.Package'
AND "packages_packages"."name" IN (
SELECT DISTINCT "packages_packages"."name"
FROM "packages_packages"
WHERE "packages_packages"."package_type" = 4
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."name" != 'NuGet.Temporary.Package'
AND "packages_packages"."project_id" IN (
SELECT "id"
FROM "project_ids"
)
AND "packages_packages"."name" ILIKE '%itwol%'
ORDER BY name ASC
LIMIT 20
OFFSET 0
)
AND "packages_packages"."project_id" IN (
SELECT "id"
FROM "project_ids"
)
) partition_subquery
WHERE "partition_subquery"."row_number" <= 10explain plan
Subquery Scan on partition_subquery (cost=19706.21..19706.34 rows=1 width=84) (actual time=2422.876..2422.906 rows=10 loops=1)
Filter: (partition_subquery.row_number <= 10)
Rows Removed by Filter: 0
Buffers: shared hit=7413 read=1989 dirtied=56
I/O Timings: read=2362.120
CTE project_ids
-> Nested Loop (cost=1576.25..19331.52 rows=1979 width=4) (actual time=26.487..2376.503 rows=1656 loops=1)
Buffers: shared hit=7394 read=1954 dirtied=56
I/O Timings: read=2325.466
-> CTE Scan on base_and_descendants namespaces_2 (cost=1575.81..1579.43 rows=181 width=4) (actual time=9.048..165.005 rows=60 loops=1)
Buffers: shared hit=184 read=119
I/O Timings: read=162.622
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=9.039..164.789 rows=60 loops=1)
Buffers: shared hit=184 read=119
I/O Timings: read=162.622
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces (cost=0.43..3.45 rows=1 width=344) (actual time=9.026..9.028 rows=1 loops=1)
Index Cond: (((namespaces.type)::text = 'Group'::text) AND (namespaces.id = 785414))
Buffers: shared read=4
I/O Timings: read=8.942
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=2.740..38.801 rows=15 loops=4)
Buffers: shared hit=184 read=115
I/O Timings: read=153.679
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.012 rows=15 loops=4)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_1 (cost=0.56..15.65 rows=2 width=344) (actual time=1.129..2.582 rows=1 loops=60)
Index Cond: (namespaces_1.parent_id = base_and_descendants.id)
Filter: ((namespaces_1.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=184 read=115
I/O Timings: read=153.679
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..97.96 rows=11 width=8) (actual time=2.957..36.835 rows=28 loops=60)
Index Cond: (projects.namespace_id = namespaces_2.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=7210 read=1835 dirtied=56
I/O Timings: read=2162.844
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.094..0.094 rows=1 loops=1656)
Index Cond: ((project_authorizations.user_id = 3983112) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 506
Buffers: shared hit=7094 read=110 dirtied=13
I/O Timings: read=135.391
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..1060.31 rows=3120 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 3983112) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> WindowAgg (cost=374.69..374.77 rows=4 width=92) (actual time=2422.874..2422.890 rows=10 loops=1)
Buffers: shared hit=7413 read=1989 dirtied=56
I/O Timings: read=2362.120
-> Sort (cost=374.69..374.70 rows=4 width=84) (actual time=2422.848..2422.855 rows=10 loops=1)
Sort Key: packages_packages.name, packages_packages.created_at DESC
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=7413 read=1989 dirtied=56
I/O Timings: read=2362.120
-> Hash Join (cost=239.53..374.65 rows=4 width=84) (actual time=2421.224..2422.781 rows=10 loops=1)
Hash Cond: (packages_packages.project_id = project_ids.id)
Buffers: shared hit=7407 read=1989 dirtied=56
I/O Timings: read=2362.120
-> Nested Loop (cost=190.50..325.55 rows=9 width=84) (actual time=37.326..38.874 rows=10 loops=1)
Buffers: shared hit=13 read=35
I/O Timings: read=36.654
-> Limit (cost=190.08..190.10 rows=4 width=34) (actual time=33.051..33.061 rows=1 loops=1)
Buffers: shared hit=4 read=31
I/O Timings: read=30.925
-> Unique (cost=190.08..190.10 rows=4 width=34) (actual time=33.049..33.059 rows=1 loops=1)
Buffers: shared hit=4 read=31
I/O Timings: read=30.925
-> Sort (cost=190.08..190.09 rows=4 width=34) (actual time=33.048..33.053 rows=10 loops=1)
Sort Key: packages_packages_1.name
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=4 read=31
I/O Timings: read=30.925
-> Hash Join (cost=84.00..190.04 rows=4 width=34) (actual time=21.668..33.023 rows=10 loops=1)
Hash Cond: (packages_packages_1.project_id = project_ids_1.id)
Buffers: shared hit=4 read=31
I/O Timings: read=30.925
-> Bitmap Heap Scan on public.packages_packages packages_packages_1 (cost=34.97..140.95 rows=7 width=38) (actual time=20.096..31.431 rows=10 loops=1)
Filter: ((packages_packages_1.version IS NOT NULL) AND ((packages_packages_1.name)::text <> 'NuGet.Temporary.Package'::text) AND (packages_packages_1.package_type = 4))
Rows Removed by Filter: 3
Buffers: shared hit=4 read=31
I/O Timings: read=30.925
-> Bitmap Index Scan using index_packages_packages_on_name_trigram (cost=0.00..34.97 rows=63 width=0) (actual time=17.701..17.702 rows=13 loops=1)
Index Cond: ((packages_packages_1.name)::text ~~* '%itwol%'::text)
Buffers: shared hit=4 read=19
I/O Timings: read=17.400
-> Hash (cost=46.53..46.53 rows=200 width=4) (actual time=1.541..1.543 rows=1656 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 75kB
-> HashAggregate (cost=44.53..46.53 rows=200 width=4) (actual time=0.879..1.148 rows=1656 loops=1)
Group Key: project_ids_1.id
-> CTE Scan on project_ids project_ids_1 (cost=0.00..39.58 rows=1979 width=4) (actual time=0.002..0.225 rows=1656 loops=1)
-> Index Scan using package_name_index on public.packages_packages (cost=0.42..33.83 rows=2 width=84) (actual time=4.269..5.803 rows=10 loops=1)
Index Cond: ((packages_packages.name)::text = (packages_packages_1.name)::text)
Filter: ((packages_packages.version IS NOT NULL) AND ((packages_packages.name)::text <> 'NuGet.Temporary.Package'::text) AND (packages_packages.package_type = 4))
Rows Removed by Filter: 0
Buffers: shared hit=9 read=4
I/O Timings: read=5.729
-> Hash (cost=46.53..46.53 rows=200 width=4) (actual time=2383.868..2383.869 rows=1656 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 75kB
Buffers: shared hit=7394 read=1954 dirtied=56
I/O Timings: read=2325.466
-> HashAggregate (cost=44.53..46.53 rows=200 width=4) (actual time=2383.083..2383.442 rows=1656 loops=1)
Group Key: project_ids.id
Buffers: shared hit=7394 read=1954 dirtied=56
I/O Timings: read=2325.466
-> CTE Scan on project_ids (cost=0.00..39.58 rows=1979 width=4) (actual time=26.491..2379.263 rows=1656 loops=1)
Buffers: shared hit=7394 read=1954 dirtied=56
I/O Timings: read=2325.466Conclusions
- I don't see an improvement here
🤔
Edited by David Fernandez