Query Performance Investigation - Query ID 6886381646110482618 and 8100380766120221092 (projects and project_statistics tables)

Description

This query is frequently reported as top 10 in total time taken during the monitoring period.

SELECT SUM((("project_statistics"."repository_size" + "project_statistics"."lfs_objects_size") - "projects"."repository_size_limit"))
FROM "projects" INNER JOIN "project_statistics" ON "project_statistics"."project_id" = "projects"."id"
WHERE "projects"."namespace_id" IN (
WITH RECURSIVE "base_and_descendants" AS
    (
    (SELECT "namespaces".*
    FROM "namespaces"
    WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2)
UNION
    (SELECT "namespaces".*
    FROM "namespaces", "base_and_descendants"
    WHERE "namespaces"."type" = $3 AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT id
FROM "base_and_descendants" AS "namespaces"
) AND
("project_statistics"."repository_size" + "project_statistics"."lfs_objects_size") > "projects"."repository_size_limit" AND "projects"."repository_size_limit" != $4
--application:web,controller:issues,action:show,correlation_id:01EYWYN9F4E5VQXCX17AVF3Z7E/ 

Requested Data points

Please provide as many of these fields as possible when submitting a query performance report.

• Queries per second (on average or peak)
• Number of calls per second and relative to total number of calls
• Query timings (on average or peak)
• Database time relative to total database time
• Source of calls (Sidekiq, WebAPI, etc) --application:web,controller:issues,action:show,correlation_id:01EYWYN9F4E5VQXCX17AVF3Z7E/
• Query ID - 6886381646110482618
• Query Plan
• Query Example
• Total number of calls (relative)
• % of Total time

Impact

A small gain that the query takes ~30ms instead of ~37ms, see details below #325344 (comment 536399756).