Skip to content

Draft: Optimize query to get all project related to a security policy configuration

Andy Schoenen requested to merge 407269-use-in-operator-optimization into master

What does this MR do and why?

Use the InOperatorOptimization helper to optimize querying all projects for a OrchestrationPolicyConfiguration.

💾 Database

Old query

Query 
SELECT
    projects.id,
    projects.name,
    projects.path,
    projects.description,
    projects.created_at,
    projects.updated_at,
    projects.creator_id,
    projects.namespace_id,
    projects.last_activity_at,
    projects.import_url,
    projects.visibility_level,
    projects.archived,
    projects.avatar,
    projects.merge_requests_template,
    projects.star_count,
    projects.merge_requests_rebase_enabled,
    projects.import_type,
    projects.import_source,
    projects.approvals_before_merge,
    projects.reset_approvals_on_push,
    projects.merge_requests_ff_only_enabled,
    projects.issues_template,
    projects.mirror,
    projects.mirror_last_update_at,
    projects.mirror_last_successful_update_at,
    projects.mirror_user_id,
    projects.shared_runners_enabled,
    projects.runners_token,
    projects.build_allow_git_fetch,
    projects.build_timeout,
    projects.mirror_trigger_builds,
    projects.pending_delete,
    projects.public_builds,
    projects.last_repository_check_failed,
    projects.last_repository_check_at,
    projects.only_allow_merge_if_pipeline_succeeds,
    projects.has_external_issue_tracker,
    projects.repository_storage,
    projects.repository_read_only,
    projects.request_access_enabled,
    projects.has_external_wiki,
    projects.ci_config_path,
    projects.lfs_enabled,
    projects.description_html,
    projects.only_allow_merge_if_all_discussions_are_resolved,
    projects.repository_size_limit,
    projects.printing_merge_request_link_enabled,
    projects.auto_cancel_pending_pipelines,
    projects.service_desk_enabled,
    projects.cached_markdown_version,
    projects.delete_error,
    projects.last_repository_updated_at,
    projects.disable_overriding_approvers_per_merge_request,
    projects.storage_version,
    projects.resolve_outdated_diff_discussions,
    projects.remote_mirror_available_overridden,
    projects.only_mirror_protected_branches,
    projects.pull_mirror_available_overridden,
    projects.jobs_cache_index,
    projects.external_authorization_classification_label,
    projects.mirror_overwrites_diverged_branches,
    projects.pages_https_only,
    projects.external_webhook_token,
    projects.packages_enabled,
    projects.merge_requests_author_approval,
    projects.pool_repository_id,
    projects.runners_token_encrypted,
    projects.bfg_object_map,
    projects.detected_repository_languages,
    projects.merge_requests_disable_committers_approval,
    projects.require_password_to_approve,
    projects.max_pages_size,
    projects.max_artifacts_size,
    projects.pull_mirror_branch_prefix,
    projects.remove_source_branch_after_merge,
    projects.marked_for_deletion_at,
    projects.marked_for_deletion_by_user_id,
    projects.autoclose_referenced_issues,
    projects.suggestion_commit_message,
    projects.project_namespace_id,
    projects.hidden,
    projects.organization_id
FROM
    projects
WHERE
    projects.namespace_id IN (
        SELECT
            namespaces.traversal_ids[array_length(
                namespaces.traversal_ids,
                1
            )] AS id
        FROM
            namespaces
        WHERE
            namespaces.type = 'Group' AND
            traversal_ids @> '{64772824}'
    );

Query plan

Time: 11.911 ms
  - planning: 9.157 ms
  - execution: 2.754 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

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

New query

Query 
SELECT
    *
FROM (WITH RECURSIVE "array_cte" AS MATERIALIZED (
        SELECT
            namespaces.traversal_ids[array_length(
                namespaces.traversal_ids, 1
)] AS id
        FROM
            "namespaces"
        WHERE
            "namespaces"."type" = 'Group'
            AND (
                traversal_ids @> (
                    '{64772824}'
)
)
), "recursive_keyset_cte" AS ((
                SELECT
                    NULL::integer AS id, array_cte_id_array, projects_id_array, 0::bigint AS count
                FROM (
                    SELECT
                        ARRAY_AGG("array_cte".id) AS array_cte_id_array,
                        ARRAY_AGG("projects"."id") AS projects_id_array
                    FROM (
                        SELECT
                            "array_cte".id
                        FROM
                            array_cte) array_cte
                    LEFT JOIN LATERAL (
                        SELECT
                            "projects"."id" AS id
                        FROM
                            "projects"
                        WHERE
                            "projects"."namespace_id" = "array_cte".id
                            AND "projects"."marked_for_deletion_at" IS NULL
                            AND "projects"."pending_delete" = FALSE
                        ORDER BY
                            "projects"."id" ASC
                        LIMIT 1) projects ON TRUE
                WHERE
                    "projects"."id" IS NOT NULL) array_scope_lateral_query
            LIMIT 1)
    UNION ALL (
        SELECT
            recursive_keyset_cte.projects_id_array[position],
            array_cte_id_array,
            recursive_keyset_cte.projects_id_array[:position_query.position - 1] || next_cursor_values.id || recursive_keyset_cte.projects_id_array[position_query.position + 1:],
            recursive_keyset_cte.count + 1
        FROM
            recursive_keyset_cte,
            LATERAL (
                SELECT
                    id,
                    position
                FROM
                    UNNEST(projects_id_array
)
                    WITH ORDINALITY AS u (id,
                        position)
                WHERE
                    id IS NOT NULL
                ORDER BY
                    1 ASC
                LIMIT 1) AS position_query,
            LATERAL (
                SELECT
                    "record"."id"
                FROM (
                    VALUES (NULL)) AS nulls
                LEFT JOIN (
                    SELECT
                        "projects"."id" AS id
                    FROM
                        "projects"
                    WHERE
                        "projects"."namespace_id" = recursive_keyset_cte.array_cte_id_array[position]
                        AND "projects"."marked_for_deletion_at" IS NULL
                        AND "projects"."pending_delete" = FALSE
                        AND ("projects"."id" > recursive_keyset_cte.projects_id_array[position])
                    ORDER BY
                        "projects"."id" ASC
                    LIMIT 1) record ON TRUE
            LIMIT 1) AS next_cursor_values))
SELECT
    id
FROM
    "recursive_keyset_cte" AS "projects"
WHERE (count <> 0)
) projects

Query plan

Time: 13.860 ms
  - planning: 9.565 ms
  - execution: 4.295 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

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

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before After

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #407269 (closed)

Edited by Andy Schoenen

Merge request reports