Skip to content

Update packages finder helper to use Namespace#all_projects

David Fernandez requested to merge 324220-10io-update-nuget-finders into master

🌵 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:

Screenshots (strongly suggested)

N / A

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

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 300
explain 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.704
With 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 300
explain 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.585
With 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 300
explain 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: 0
Conclusions
  • 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.228
With 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.496
With 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.382
Conclusions
  • 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" <= 10
explain 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.367
With 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" <= 10
explain 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.150
With 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" <= 10
explain 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.466
Conclusions
  • I don't see an improvement here 🤔
Edited by David Fernandez

Merge request reports