Skip to content

Include namespace storage stats in root statistics

Francisco Javier López (ex-Gitlab) requested to merge 230465-fj-update-root-storage-stats-to-include-namespace-storage into master

What does this MR do?

In this MR we change the RootNamespaceStatistics query to also include (sum) from NamespaceStatistics, wiki_size and storage_size, when the namespace is a group.

Refs #230465 (closed)

Queries and plans

I've used the namespace id of the gitlab-org for the queries.

The existing query plan is in https://explain.depesz.com/s/FvHI and the SQL is:

WITH RECURSIVE "base_and_descendants" AS (
                                            (SELECT "namespaces".*
                                             FROM "namespaces"
                                             WHERE "namespaces"."type" = 'Group'
                                               AND "namespaces"."id" = 9970)
                                          UNION
                                            (SELECT "namespaces".*
                                             FROM "namespaces",
                                                  "base_and_descendants"
                                             WHERE "namespaces"."type" = 'Group'
                                               AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT COALESCE(SUM(ns.storage_size), 0) AS storage_size,
       COALESCE(SUM(ns.wiki_size), 0) AS wiki_size
FROM "base_and_descendants" AS "namespaces"
INNER JOIN namespace_statistics ns ON ns.namespace_id = namespaces.id

The execution times with warm caches are:

Time: 5.209 ms
  - planning: 0.863 ms
  - execution: 4.346 ms
    - I/O read: N/A
    - I/O write: N/A

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

I've tried other alternatives for that query like:

NamespaceStatistics
 .where(namespace_id: namespace.self_and_descendants.select(:id))
 .select('COALESCE(SUM(namespace_statistics.storage_size), 0) AS storage_size','COALESCE(SUM(namespace_statistics.wiki_size), 0) AS wiki_size')

The query plan for this query is in https://explain.depesz.com/s/WnxP and the generated SQL is:

SELECT COALESCE(SUM(namespace_statistics.storage_size), 0) AS storage_size,
       COALESCE(SUM(namespace_statistics.wiki_size), 0) AS wiki_size
FROM "namespace_statistics"
WHERE "namespace_statistics"."namespace_id" IN
    (WITH RECURSIVE "base_and_descendants" AS (
                                                 (SELECT "namespaces".*
                                                  FROM "namespaces"
                                                  WHERE "namespaces"."type" = 'Group'
                                                    AND "namespaces"."id" = 9970)
                                               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")

The execution times are:

Time: 6.085 ms
  - planning: 1.164 ms
  - execution: 4.921 ms
    - I/O read: N/A
    - I/O write: N/A

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

Does this MR meet the acceptance criteria?

Conformity

Related to #230465 (closed)

Edited by Francisco Javier López (ex-Gitlab)

Merge request reports