• Sean McGivern's avatar
    Extend CTE search optimisation to projects · 10ceb33b
    Sean McGivern authored
    When we use the `search` param on an `IssuableFinder`, we can run into
    issues. We have trigram indexes to support these searches. On
    GitLab.com, we often see Postgres's optimiser prioritise the (global)
    trigram indexes over the index on `project_id`. For group and project
    searches, we know that it will be quicker to filter by `project_id`
    first, as it returns fewer rows in most cases.
    
    For group issues search, we ran into this issue previously, and went
    through the following iterations:
    
    1. Use a CTE on the project IDs as an optimisation fence. This prevents
       the planner from disregarding the index on `project_id`.
       Unfortunately it breaks some types of sorting, like priority and
       popularity, as they sort on a joined table.
    2. Use a subquery for listing issues, and a CTE for counts. The subquery
       - in the case of group lists - didn't help as much as the CTE, but
       was faster than not including it. We can safely use a CTE for counts
       as they don't have sorting.
    
    Now, however, we're seeing the same issue in a project context. The
    subquery doesn't help at all there (it would only return one row, after
    all). In an attempt to keep total code complexity under control, this
    commit removes the subquery optimisation and applies the CTE
    optimisation only for sorts we know that are safe.
    
    This means that for more complicated sorts (like priority and
    popularity), the search will continue to be very slow. If this is a
    high-priority issue, we can consider introducing further optimisations,
    but this finder is already very complicated and additional complexity
    has a cost.
    
    The group CTE optimisation is controlled by the same feature flag as
    before, `attempt_group_search_optimizations`, which is enabled by
    default. The new project CTE optimisation is controlled by a new feature
    flag, `attempt_project_search_optimizations`, which is disabled by
    default.
    10ceb33b
extend-cte-optimisations-to-projects.yml 106 Bytes