Investigate 'Top-15 Queries by total_time' from pg checkup report

Why?

Created as part of: gitlab-org/database-team/team-tasks#390 (closed). To improve the top-5 time taking queries from primary and ci databases.

Reference: https://gitlab.com/gitlab-com/gl-infra/reliability/-/snippets/

Queries (ordered by total_time):

Primary:

  1. Ci::CancelRedundantPipelinesWorker

    /*application:sidekiq,correlation_id:4903664cd899a2e6459889d2e5942782,jid:3877f490d204cc2a7424835a,endpoint_id:Ci::CancelRedundantPipelinesWorker,db_config_name:ci*/
SELECT
    "ci_pipelines"."id"
FROM
    "ci_pipelines"
WHERE
    "ci_pipelines"."project_id" = $1
    AND "ci_pipelines"."created_at" > $2
ORDER BY
    "ci_pipelines"."status" ASC,
    "ci_pipelines"."created_at" ASC
LIMIT $3 OFFSET $4
    query id Calls Total Time Rows
    -7670991836542115000 114,547
    61.61/sec
    1.00/call
    0.48%    		 1,426,494.91 ms
    767.260 ms/sec
    12.453 ms/call
    22.58%    		 5,918,229
    3.19K/sec
    51.67/call
    10.20%

    Related issue: Infradev: Improve job duration of urgent hourly... (#430782 - closed)

  2. Ci::CancelRedundantPipelinesWorker

    /*application:sidekiq,correlation_id:90a0cf3fd71d68a702e347bc8f47af0b,jid:7d616b3736fd30e6ec3a95f6,endpoint_id:Ci::CancelRedundantPipelinesWorker,db_config_name:ci*/
WITH RECURSIVE "base_and_descendants" AS (
(
        SELECT
            "ci_pipelines"."id",
            "ci_pipelines"."ref",
            "ci_pipelines"."sha",
            "ci_pipelines"."before_sha",
            "ci_pipelines"."created_at",
            "ci_pipelines"."updated_at",
            "ci_pipelines"."tag",
            "ci_pipelines"."yaml_errors",
            "ci_pipelines"."committed_at",
            "ci_pipelines"."project_id",
            "ci_pipelines"."status",
            "ci_pipelines"."started_at",
            "ci_pipelines"."finished_at",
            "ci_pipelines"."duration",
            "ci_pipelines"."user_id",
            "ci_pipelines"."lock_version",
            "ci_pipelines"."pipeline_schedule_id",
            "ci_pipelines"."source",
            "ci_pipelines"."protected",
            "ci_pipelines"."config_source",
            "ci_pipelines"."failure_reason",
            "ci_pipelines"."iid",
            "ci_pipelines"."merge_request_id",
            "ci_pipelines"."source_sha",
            "ci_pipelines"."target_sha",
            "ci_pipelines"."external_pull_request_id",
            "ci_pipelines"."ci_ref_id",
            "ci_pipe...
    query id Calls Total Time Rows
    2352091117404650500 51,717
    27.82/sec
    1.00/call
    0.22%    		 449,246.05 ms
    241.633 ms/sec
    8.687 ms/call
    7.11%    		 7,722
    4.15/sec
    0.15/call
    0.01%

    Related issue: Infradev: Improve job duration of urgent hourly... (#430782 - closed)

  3. Selecting token_encrypted from ci_builds

    /*application:web,correlation_id:01HD276ZGFKESPAMFH7VDFVKDW,db_config_name:ci*/
SELECT
    "p_ci_builds".*
FROM
    "p_ci_builds"
WHERE
    "p_ci_builds"."type" = $1
    AND "p_ci_builds"."token_encrypted" IN ($2, $3)
LIMIT $4
    query id Calls Total Time Rows
    -108547014002301630 1,200,139
    645.51/sec
    1.00/call
    4.99%    		 242,272.65 ms
    130.310 ms/sec
    1.389 ms/call
    3.84%    		 173,848
    93.51/sec
    1.00/call
    0.30%

  4. Ci::InitialPipelineProcessWorker

    /*application:sidekiq,correlation_id:01HFECZZ5VKWBCRQ3EQA058TH5,jid:3ab78f69f52fad7e3056b872,endpoint_id:Ci::InitialPipelineProcessWorker,db_config_name:ci*/
SELECT
    array_agg(id),
    "p_ci_builds"."protected",
    (
        SELECT
            COALESCE(array_agg(tags.name ORDER BY name), ARRAY[]::text[])
        FROM
            "taggings"
            INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id"
        WHERE (taggings.taggable_id = "p_ci_builds".id)
        AND "taggings"."context" = $1
        AND "taggings"."taggable_type" = $2)
FROM
    "p_ci_builds"
WHERE
    "p_ci_builds"."type" = $3
    AND "p_ci_builds"."commit_id" = $4
    AND "p_ci_builds"."partition_id" = $5
    AND ("p_ci_builds"."retried" = $6
        OR "p_ci_builds"."retried" IS NULL)
GROUP BY
    "p_ci_builds"."protected",
    (
        SELECT
            COALESCE(array_agg(tags.name ORDER BY name), ARRAY[]::text[])
        FROM
            "taggings"
            INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id"
        WHERE (taggings.taggable_id = "p_ci_builds".id)
        AND "taggings"."context" = 'tags'
        AND "taggings"."taggable_type" = 'CommitStatus')
    query id Calls Total Time Rows
    8173290708538855000 36,538
    19.65/sec
    1.00/call
    0.15%    		 247,674.22 ms
    133.215 ms/sec
    6.779 ms/call
    3.92%    		 50,263
    27.03/sec
    1.38/call
    0.09%

    Related issue: #354472

  5. Ci::InitialPipelineProcessWorker

    /*application:sidekiq,correlation_id:01HD0NK0SSRP503WQSWQ8N5G83,jid:c3cda9812a1b82312fdadd92,endpoint_id:Ci::InitialPipelineProcessWorker,db_config_name:ci*/
UPDATE
    "p_ci_builds"
SET
    "status" = $1,
    "updated_at" = $2,
    "queued_at" = $3,
    "token_encrypted" = $4,
    "processed" = $5,
    "lock_version" = $6
WHERE
    "p_ci_builds"."id" = $7
    AND "p_ci_builds"."lock_version" = $8
    query id Calls Total Time Rows
    7005076941864743000 174,429
    93.82/sec
    1.00/call
    0.73%    		 242,272.65 ms
    130.310 ms/sec
    1.389 ms/call
    3.84%    		 173,848
    93.51/sec
    1.00/call
    0.30%

Ci

  1. PUT /api/:version/projects/:id/issues/:issue_iid

    * application:web,
correlation_id:758674cb7542400b456ddfc5a3057ea8,
endpoint_id:PUT / api / :version / projects / :id / issues / :issue_iid,
db_config_name:main /
SELECT
    $1 AS one
FROM
    "notes"
WHERE
    "notes"."noteable_id" = $2
    AND "notes"."noteable_type" = $3
    AND "notes"."system" = $4
    AND "notes"."note" IN ($5, $6)
LIMIT $7
    query id Calls Total Time Rows
    -1087689967750282400 7,970
    4.14/sec
    1.00/call
    0.02%    		 3,954,740.26 ms
    2055.322 ms/sec
    496.203 ms/call
    46.77%    		 2,687
    1.40/sec
    0.34/call
    0.01%

  2. PostReceive

    /*application:sidekiq,correlation_id:01H9XAAT4M1Z7XBZ61YNNK44DH,jid:5a2b96bbcf75058437819f27,endpoint_id:PostReceive,db_config_name:main*/
UPDATE
    "projects"
SET
    "updated_at" = $1,
    "last_activity_at" = $2
WHERE
    "projects"."id" = $3
    AND (last_activity_at <= $4)
    query id Calls Total Time Rows
    3004991215575469600 22,744
    11.82/sec
    1.00/call
    0.06%    		 248,295.07 ms
    129.042 ms/sec
    10.917 ms/call
    2.94%    		 22,492
    11.69/sec
    0.99/call
    0.04%

    Related issue: #402254 (closed)

  3. PipelineProcessWorker

    /*application:sidekiq,correlation_id:c3b711eac93b9b8b57a7759fd338043f,jid:dc2b517cdbc6080b89fa80e5,endpoint_id:PipelineProcessWorker,db_config_name:main*/
SELECT
    "merge_requests".*
FROM
    "merge_requests"
WHERE
    "merge_requests"."source_project_id" = $1
    AND "merge_requests"."source_branch" = $2
    AND (EXISTS (
            SELECT
                $3
            FROM
                "merge_request_diffs"
                INNER JOIN "merge_request_diff_commits" ON "merge_request_diff_commits"."merge_request_diff_id" = "merge_request_diffs"."id"
            WHERE (merge_requests.latest_merge_request_diff_id = merge_request_diffs.id)
            AND "merge_request_diff_commits"."sha" = $4))
    query id Calls Total Time Rows
    5309363526308371000 321,772
    167.23/sec
    1.00/call
    0.88%    		 233,438.10 ms
    121.320 ms/sec
    0.725 ms/call
    2.76%    		 68,251
    35.47/sec
    0.21/call
    0.13%

    Related issue: #199259 (closed)

  4. PostReceive (Insertion)

    /*application:sidekiq,correlation_id:01H9XAARW96WFX5ASD4GAQYSQY,jid:2179f4b3f86c68f592221d15,endpoint_id:PostReceive,db_config_name:main*/
INSERT INTO "events" ("project_id", "author_id", "created_at", "updated_at", "action")
    VALUES (46538149, 7765857, '2023-09-09 15:53:41.720372', '2023-09-09 15:53:41.720372', 5)
RETURNING
    "id"
    query id Calls Total Time Rows
    4779199603619064000 60,857
    31.63/sec
    1.00/call
    0.17%    		 188,981.80 ms
    98.216 ms/sec
    3.105 ms/call
    2.24%    		 60,857
    31.63/sec
    1.00/call
    0.12%

  5. NewIssueWorker

    /*application:sidekiq,correlation_id:01H9XAB514Y4B2EPJVD97BCW0E,jid:e3d6dfc2331f2eac565a71c3,endpoint_id:NewIssueWorker,db_config_name:main*/
SELECT
    "notes"."note",
    "notes"."noteable_type",
    "notes"."author_id",
    "notes"."created_at",
    "notes"."updated_at",
    "notes"."project_id",
    "notes"."attachment",
    "notes"."line_code",
    "notes"."commit_id",
    "notes"."noteable_id",
    "notes"."st_diff",
    "notes"."system",
    "notes"."updated_by_id",
    "notes"."type",
    "notes"."position",
    "notes"."original_position",
    "notes"."resolved_at",
    "notes"."resolved_by_id",
    "notes"."discussion_id",
    "notes"."note_html",
    "notes"."cached_markdown_version",
    "notes"."change_position",
    "notes"."resolved_by_push",
    "notes"."review_id",
    "notes"."confidential",
    "notes"."last_edited_at",
    "notes"."internal",
    "notes"."id",
    "notes"."namespace_id"
FROM
    "notes"
WHERE
    "notes"."noteable_id" = $1
    AND "notes"."noteable_type" = $2
    query id Calls Total Time Rows
    676942159902281700 68,274
    35.48/sec
    1.00/call
    0.19%    		 162,176.22 ms
    84.285 ms/sec
    2.375 ms/call
    1.92%    		 1,939,344
    1.01K/sec
    28.41/call
    3.78%

    Related issue: https://gitlab.com/gitlab-org/gitlab/-/issues/357657 and https://gitlab.com/gitlab-org/gitlab/-/issues/429445

Deliverables:

  • Investigate each query and come up with ways to improve them.
  • Create follow-up issues for the queries that can be improved and assign the feature owning group if requried.
Edited by Prabakaran Murugesan