Increase sort options for issues list from updated_at and create_at,
to include more options close to what is required in actual issue list
UI.
This helps us to use REST API for issues list with sorting capabilities
https://gitlab.com/gitlab-org/gitlab-ce/issues/57402
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.
Sorting by "id" has the same effect as sorting by created_at while
performing far better and without the need of an extra index (in case
one wanted to speed up sorting by "created_at").
Sorting by "Recently updated" still uses the physical "updated_at"
column as there's no way to use the "id" column for this instead.
By moving the default sort order into a separate scope (and calling this
from the default scope) we can more easily re-apply a default order
without having to specify the exact column/ordering all over the place.
Sorting by both "created_at" and "id" in descending order is not needed
as simply sorting by "id" in descending order will already sort rows
from new to old. Depending on the query and data involved sorting twice
can also introduce significant overhead.
GitLab Bot
Alexandru Croitor
Sean McGivern
Jacopo
Stan Hu
Oswaldo Ferreira
gfyoung
Douglas Barbosa Alexandre
Harish Ved
Vitaliy @blackst0ne Klachkov
Tiago Botelho
Sean McGivern
Douwe Maan
Adam Niedzielski
Douwe Maan
Grzegorz Bizon
Alejandro Rodríguez
Felipe Artur
Yorick Peterse
Dmitriy Zaporozhets