Resolve "Number of snippets in the snippets page is wrong"

What does this MR do?

Adds a new Snippets service object (Snippets::CountService) to retrieve the categorised snippet counts for the current user's authorisation levels.

This addresses #21705 (closed) whereby the incorrect number of Snippets were being displayed in the snippets dashboard tabs due to the user having a private snippet belonging to a project they no longer have access to.

As a side effect, this will also resolve another unreported bug related to these counts, when a project with snippets has the snippet feature disabled.

This MR combines the counts into one query in an attempt to be more performant than doing individual auth checks / counts (speed reports below), as discussed in #36391 (closed). This MR will likely also resolve that issue, too.

New query for a user (including authorisation):

SQL

SELECT
    count(
        CASE WHEN snippets.visibility_level = 20
            AND snippets.secret IS FALSE THEN
            1
        ELSE
            NULL
        END) AS are_public,
    count(
        CASE WHEN snippets.visibility_level = 10 THEN
            1
        ELSE
            NULL
        END) AS are_internal,
    count(
        CASE WHEN snippets.visibility_level = 0 THEN
            1
        ELSE
            NULL
        END) AS are_private,
    count(
        CASE WHEN visibility_level = 20
            OR visibility_level = 10 THEN
            1
        ELSE
            NULL
        END) AS are_public_or_internal,
    count(*) AS total
FROM ((
        SELECT
            snippets.*
        FROM
            snippets
        WHERE
            snippets.author_id = 1562869
            AND snippets.project_id IS NULL)
    UNION (
        SELECT
            snippets.*
        FROM
            snippets
            INNER JOIN projects ON projects.id = snippets.project_id
            INNER JOIN project_features ON project_features.project_id = projects.id
        WHERE
            snippets.author_id = 1562869
            AND (projects.visibility_level IN (10, 20))
            AND project_features.snippets_access_level IN (20, 30))
    UNION (
        SELECT
            snippets.*
        FROM
            snippets
            INNER JOIN projects ON projects.id = snippets.project_id
            INNER JOIN project_features ON project_features.project_id = projects.id
        WHERE
            snippets.author_id = 1562869
            AND project_features.snippets_access_level IN (20, 30, 10)
            AND (EXISTS (
                    SELECT
                        1
                    FROM
                        project_authorizations
                    WHERE (project_id = snippets.project_id)
                    AND project_authorizations.user_id = 1562869)))) snippets
LIMIT 1

Summary

Time: 43.086 ms
  - planning: 26.067 ms
  - execution: 17.019 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2255 (~17.60 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Full execution plan

 Limit  (cost=587.98..587.99 rows=1 width=40) (actual time=16.441..16.447 rows=1 loops=1)
   Buffers: shared hit=2255
   ->  Aggregate  (cost=587.98..587.99 rows=1 width=40) (actual time=16.441..16.441 rows=1 loops=1)
         Buffers: shared hit=2255
         ->  HashAggregate  (cost=585.91..586.37 rows=46 width=3293) (actual time=16.375..16.408 rows=56 loops=1)
               Group Key: snippets.id, snippets.title, snippets.content, snippets.author_id, snippets.project_id, snippets.created_at, snippets.updated_at, snippets.file_name, snippets.type, snippets.visibility_level, snippets.title_html, snippets.content_html, snippets.cached_markdown_version, snippets.description, snippets.description_html, snippets.encrypted_secret_token, snippets.encrypted_secret_token_iv, snippets.secret, snippets.repository_storage
               Buffers: shared hit=2255
               ->  Append  (cost=0.42..583.72 rows=46 width=3293) (actual time=0.074..5.469 rows=81 loops=1)
                     Buffers: shared hit=1831
                     ->  Index Scan using index_snippets_on_author_id on public.snippets  (cost=0.42..115.37 rows=38 width=2106) (actual time=0.073..0.993 rows=25 loops=1)
                           Index Cond: (snippets.author_id = 1562869)
                           Filter: (snippets.project_id IS NULL)
                           Rows Removed by Filter: 31
                           Buffers: shared hit=56
                     ->  Nested Loop  (cost=1.28..268.25 rows=7 width=2106) (actual time=0.112..0.903 rows=25 loops=1)
                           Buffers: shared hit=317
                           ->  Nested Loop  (cost=0.85..262.85 rows=8 width=2110) (actual time=0.090..0.612 rows=31 loops=1)
                                 Buffers: shared hit=193
                                 ->  Index Scan using index_snippets_on_author_id on public.snippets snippets_1  (cost=0.42..115.37 rows=56 width=2106) (actual time=0.007..0.114 rows=56 loops=1)
                                       Index Cond: (snippets_1.author_id = 1562869)
                                       Buffers: shared hit=56
                                 ->  Index Only Scan using index_projects_on_id_partial_for_visibility on public.projects  (cost=0.43..2.62 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=56)
                                       Index Cond: (projects.id = snippets_1.project_id)
                                       Heap Fetches: 22
                                       Buffers: shared hit=137
                           ->  Index Scan using index_project_features_on_project_id on public.project_features  (cost=0.43..0.66 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=31)
                                 Index Cond: (project_features.project_id = projects.id)
                                 Filter: (project_features.snippets_access_level = ANY ('{20,30}'::integer[]))
                                 Rows Removed by Filter: 0
                                 Buffers: shared hit=124
                     ->  Nested Loop  (cost=82.27..199.64 rows=1 width=2106) (actual time=2.863..3.537 rows=31 loops=1)
                           Buffers: shared hit=1458
                           ->  Nested Loop  (cost=81.84..199.14 rows=1 width=2114) (actual time=2.852..3.308 rows=31 loops=1)
                                 Buffers: shared hit=1334
                                 ->  Hash Join  (cost=81.40..196.50 rows=1 width=2110) (actual time=2.789..2.943 rows=31 loops=1)
                                       Hash Cond: (snippets_2.project_id = project_authorizations.project_id)
                                       Buffers: shared hit=1202
                                       ->  Index Scan using index_snippets_on_author_id on public.snippets snippets_2  (cost=0.42..115.37 rows=56 width=2106) (actual time=0.007..0.126 rows=56 loops=1)
                                             Index Cond: (snippets_2.author_id = 1562869)
                                             Buffers: shared hit=56
                                       ->  Hash  (cost=78.58..78.58 rows=192 width=4) (actual time=2.764..2.764 rows=2211 loops=1)
                                             Buckets: 4096  Batches: 1  Memory Usage: 110kB
                                             Buffers: shared hit=1146
                                             ->  Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations  (cost=0.56..78.58 rows=192 width=4) (actual time=0.074..2.173 rows=2211 loops=1)
                                                   Index Cond: (project_authorizations.user_id = 1562869)
                                                   Heap Fetches: 623
                                                   Buffers: shared hit=1146
                                 ->  Index Only Scan using projects_pkey on public.projects projects_1  (cost=0.43..2.63 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=31)
                                       Index Cond: (projects_1.id = snippets_2.project_id)
                                       Heap Fetches: 22
                                       Buffers: shared hit=132
                           ->  Index Scan using index_project_features_on_project_id on public.project_features project_features_1  (cost=0.43..0.49 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=31)
                                 Index Cond: (project_features_1.project_id = projects_1.id)
                                 Filter: (project_features_1.snippets_access_level = ANY ('{20,30,10}'::integer[]))
                                 Rows Removed by Filter: 0
                                 Buffers: shared hit=124

New query for a project

SQL

SELECT
            count(case when snippets.visibility_level=20 and snippets.secret is FALSE then 1 else null end) as are_public,
            count(case when snippets.visibility_level=10 then 1 else null end) as are_internal,
            count(case when snippets.visibility_level=0 then 1 else null end) as are_private,
            count(case when visibility_level=20 OR visibility_level=10 then 1 else null end) as are_public_or_internal,
            count(*) as total
           FROM "snippets" WHERE "snippets"."type" = 'ProjectSnippet' AND "snippets"."project_id" = 278964 AND "snippets"."visibility_level" IN (10, 20)

Summary

Time: 2.784 ms
  - planning: 1.042 ms
  - execution: 1.742 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 50 (~400.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Execution plan

Aggregate  (cost=55.59..55.60 rows=1 width=40) (actual time=1.636..1.636 rows=1 loops=1)
   Buffers: shared hit=50
   ->  Index Scan using index_snippets_on_project_id_and_visibility_level on public.snippets  (cost=0.42..55.39 rows=8 width=5) (actual time=0.111..1.586 rows=46 loops=1)
         Index Cond: ((snippets.project_id = 278964) AND (snippets.visibility_level = ANY ('{10,20}'::integer[])))
         Filter: ((snippets.type)::text = 'ProjectSnippet'::text)
         Rows Removed by Filter: 0
         Buffers: shared hit=50

Old queries (excluding authorisation):

All user snippets

SQL

SELECT COUNT(*) FROM "snippets" WHERE "snippets"."author_id" = 5249152

EXPLAIN summary

Time: 5.889 ms
  - planning: 0.871 ms
  - execution: 5.018 ms
    - I/O read: 4.750 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 0 from the buffer pool
  - reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Full execution plan

Aggregate  (cost=4.48..4.49 rows=1 width=8) (actual time=4.921..4.922 rows=1 loops=1)
   Buffers: shared read=3
   I/O Timings: read=4.750
   ->  Index Only Scan using index_snippets_on_author_id on public.snippets  (cost=0.42..4.47 rows=3 width=0) (actual time=4.911..4.911 rows=0 loops=1)
         Index Cond: (snippets.author_id = 5249152)
         Heap Fetches: 0
         Buffers: shared read=3
         I/O Timings: read=4.750

Public and internal snippets

SQL

SELECT COUNT(*) FROM "snippets" WHERE "snippets"."author_id" = 5249152 AND "snippets"."visibility_level" IN (20, 10)

EXPLAIN summary

Time: 1.748 ms
  - planning: 1.607 ms
  - execution: 0.141 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Full execution plan

 Aggregate  (cost=8.48..8.49 rows=1 width=8) (actual time=0.064..0.064 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using index_snippets_on_author_id on public.snippets  (cost=0.42..8.48 rows=1 width=0) (actual time=0.059..0.059 rows=0 loops=1)
         Index Cond: (snippets.author_id = 5249152)
         Filter: (snippets.visibility_level = ANY ('{20,10}'::integer[]))
         Rows Removed by Filter: 0
         Buffers: shared hit=3

Private snippets

SQL

SELECT COUNT(*) FROM "snippets" WHERE "snippets"."author_id" = 5249152 AND "snippets"."visibility_level" = 0

EXPLAIN summary

Time: 1.062 ms
  - planning: 0.960 ms
  - execution: 0.102 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Full execution plan

Aggregate  (cost=8.48..8.49 rows=1 width=8) (actual time=0.041..0.041 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using index_snippets_on_author_id on public.snippets  (cost=0.42..8.48 rows=2 width=0) (actual time=0.037..0.037 rows=0 loops=1)
         Index Cond: (snippets.author_id = 5249152)
         Filter: (snippets.visibility_level = 0)
         Rows Removed by Filter: 0
         Buffers: shared hit=3

Internal snippets

SQL

SELECT COUNT(*) FROM "snippets" WHERE "snippets"."author_id" = 5249152 AND "snippets"."visibility_level" = 10

EXPLAIN summary

Time: 0.993 ms
  - planning: 0.884 ms
  - execution: 0.109 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Full execution plan

Aggregate  (cost=8.48..8.49 rows=1 width=8) (actual time=0.039..0.039 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using index_snippets_on_author_id on public.snippets  (cost=0.42..8.48 rows=1 width=0) (actual time=0.036..0.036 rows=0 loops=1)
         Index Cond: (snippets.author_id = 5249152)
         Filter: (snippets.visibility_level = 10)
         Rows Removed by Filter: 0
         Buffers: shared hit=3

Public snippets

SQL

SELECT COUNT(*) FROM "snippets" WHERE "snippets"."author_id" = 5249152 AND "snippets"."visibility_level" = 20

EXPLAIN summary

Time: 3.242 ms
  - planning: 3.046 ms
  - execution: 0.196 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Full execution plan

Aggregate  (cost=8.48..8.49 rows=1 width=8) (actual time=0.063..0.063 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using index_snippets_on_author_id on public.snippets  (cost=0.42..8.48 rows=1 width=0) (actual time=0.060..0.060 rows=0 loops=1)
         Index Cond: (snippets.author_id = 5249152)
         Filter: (snippets.visibility_level = 20)
         Rows Removed by Filter: 0
         Buffers: shared hit=3

Although the above timings suggest the old queries are faster than the new query, it's worth noting they do not include any authorisation checks for the current user. If each of those queries were to include the same authorisation checks (via SnippetsFinder) they would each look something like:

Public snippet count with authorisation

SQL

SELECT
    COUNT(*)
FROM ((
        SELECT
            "snippets".*
        FROM
            "snippets"
        WHERE
            "snippets"."author_id" = 5249152
            AND "snippets"."project_id" IS NULL)
    UNION (
        SELECT
            "snippets".*
        FROM
            "snippets"
            INNER JOIN "projects" ON "projects"."id" = "snippets"."project_id"
            INNER JOIN "project_features" ON "project_features"."project_id" = "projects"."id"
        WHERE
            "snippets"."author_id" = 5249152
            AND (projects.visibility_level IN (10, 20))
            AND "project_features"."snippets_access_level" IN (20, 30))
    UNION (
        SELECT
            "snippets".*
        FROM
            "snippets"
            INNER JOIN "projects" ON "projects"."id" = "snippets"."project_id"
            INNER JOIN "project_features" ON "project_features"."project_id" = "projects"."id"
        WHERE
            "snippets"."author_id" = 5249152
            AND "project_features"."snippets_access_level" IN (20, 30, 10)
            AND (EXISTS (
                    SELECT
                        1
                    FROM
                        "project_authorizations"
                    WHERE (project_id = snippets.project_id)
                    AND "project_authorizations"."user_id" = 5249152)))) snippets
WHERE
    "snippets"."visibility_level" = 20

EXPLAIN Summary

Time: 17.607 ms
  - planning: 17.222 ms
  - execution: 0.385 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 9 (~72.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Full execution plan

 Aggregate  (cost=36.32..36.33 rows=1 width=8) (actual time=0.043..0.043 rows=1 loops=1)
   Buffers: shared hit=9
   ->  HashAggregate  (cost=36.25..36.28 rows=3 width=3293) (actual time=0.041..0.041 rows=0 loops=1)
         Group Key: snippets.id, snippets.title, snippets.content, snippets.author_id, snippets.project_id, snippets.created_at, snippets.updated_at, snippets.file_name, snippets.type, snippets.visibility_level, snippets.title_html, snippets.content_html, snippets.cached_markdown_version, snippets.description, snippets.description_html, snippets.encrypted_secret_token, snippets.encrypted_secret_token_iv, snippets.secret, snippets.repository_storage
         Buffers: shared hit=9
         ->  Append  (cost=0.42..36.11 rows=3 width=3293) (actual time=0.040..0.040 rows=0 loops=1)
               Buffers: shared hit=9
               ->  Index Scan using index_snippets_on_author_id on public.snippets  (cost=0.42..8.48 rows=1 width=2106) (actual time=0.035..0.036 rows=0 loops=1)
                     Index Cond: (snippets.author_id = 5249152)
                     Filter: ((snippets.project_id IS NULL) AND (snippets.visibility_level = 20))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=3
               ->  Nested Loop  (cost=1.28..13.41 rows=1 width=2106) (actual time=0.002..0.002 rows=0 loops=1)
                     Buffers: shared hit=3
                     ->  Nested Loop  (cost=0.85..12.94 rows=1 width=2110) (actual time=0.002..0.002 rows=0 loops=1)
                           Buffers: shared hit=3
                           ->  Index Scan using index_snippets_on_author_id on public.snippets snippets_1  (cost=0.42..8.48 rows=1 width=2106) (actual time=0.002..0.002 rows=0 loops=1)
                                 Index Cond: (snippets_1.author_id = 5249152)
                                 Filter: (snippets_1.visibility_level = 20)
                                 Rows Removed by Filter: 0
                                 Buffers: shared hit=3
                           ->  Index Scan using index_project_features_on_project_id on public.project_features  (cost=0.43..4.46 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (project_features.project_id = snippets_1.project_id)
                                 Filter: (project_features.snippets_access_level = ANY ('{20,30}'::integer[]))
                                 Rows Removed by Filter: 0
                     ->  Index Only Scan using index_projects_on_id_partial_for_visibility on public.projects  (cost=0.43..0.45 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: (projects.id = project_features.project_id)
                           Heap Fetches: 0
               ->  Nested Loop  (cost=1.85..14.19 rows=1 width=2106) (actual time=0.002..0.002 rows=0 loops=1)
                     Buffers: shared hit=3
                     ->  Nested Loop  (cost=1.29..13.43 rows=1 width=2114) (actual time=0.002..0.002 rows=0 loops=1)
                           Buffers: shared hit=3
                           ->  Nested Loop  (cost=0.85..12.94 rows=1 width=2110) (actual time=0.002..0.002 rows=0 loops=1)
                                 Buffers: shared hit=3
                                 ->  Index Scan using index_snippets_on_author_id on public.snippets snippets_2  (cost=0.42..8.48 rows=1 width=2106) (actual time=0.002..0.003 rows=0 loops=1)
                                       Index Cond: (snippets_2.author_id = 5249152)
                                       Filter: (snippets_2.visibility_level = 20)
                                       Rows Removed by Filter: 0
                                       Buffers: shared hit=3
                                 ->  Index Scan using index_project_features_on_project_id on public.project_features project_features_1  (cost=0.43..4.46 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
                                       Index Cond: (project_features_1.project_id = snippets_2.project_id)
                                       Filter: (project_features_1.snippets_access_level = ANY ('{20,30,10}'::integer[]))
                                       Rows Removed by Filter: 0
                           ->  Index Only Scan using projects_pkey on public.projects projects_1  (cost=0.43..0.47 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (projects_1.id = project_features_1.project_id)
                                 Heap Fetches: 0
                     ->  Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations  (cost=0.56..0.66 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((project_authorizations.user_id = 5249152) AND (project_authorizations.project_id = projects_1.id))
                           Heap Fetches: 0

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry
• 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

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