• Stan Hu's avatar
    Add indexes on deployments to improve environments search · 92d3c08f
    Stan Hu authored
    To load diffs and other files, we make two SELECT calls in
    `MergeRequest#environments_for`: one for the source branch, and one for
    the target branch.
    
    The source branch query makes a subquery:
    
    ```sql
    SELECT environment_id FROM deployments WHERE project_id = 123 AND ref =
    'source_branch'
    ```
    
    The target branch makes this subquery:
    
    ```sql
    SELECT environment_id FROM deployments WHERE project_id = 123 AND (ref =
    'master' OR tag IS TRUE)
    ```
    
    Both subqueries were taking over 250 ms to execute. When we add an index
    for the deployments table on `(projects_id, ref)`, this execution drops
    down to under 5 ms since the table usually has a small number of rows
    for the source branch.
    
    Adding a partial index on `projects_id WHERE tag IS TRUE` allows
    PostgreSQL to perform two separate indexed scans: one for the ref, and
    one for the tag condition. However, this query still takes 60 ms to run
    for the gitlab-org/gitlab project because `master` has 10,000+ rows. We
    should consider adding a LIMIT in a future iteration.
    
    Closes https://gitlab.com/gitlab-org/gitlab/issues/55353
    92d3c08f
schema.rb 248 KB