Tie-breaker in Deployment Finder should respect the original sort direction

What does this MR do?

While I was testing the feature flag, I realized that the following API request to Deployment API results in a slow query:

curl -H 'Private-token: [REDACTED]' "https://gitlab.com/api/v4/projects/278964/deployments?environment=gprd&updated_after=2021-01-01&order_by=id"

This executes a query like this

Query

explain SELECT "deployments".* FROM "deployments" WHERE "deployments"."project_id" = 278964 AND "deployments"."updated_at" >= '2021-01-01' ORDER BY "deployments"."updated_at" ASC, "deployments"."id" DESC LIMIT 100 OFFSET 100

Notice that the sort direction is opposite. updated_at is ASC, but id (for tie-breaker) is DESC. Therefore, the index_deployments_on_project_id_and_updated_at_and_id index is NOT used.

Timing

Time: 14.400 s
  - planning: 4.349 ms
  - execution: 14.395 s (estimated* for prod: 0.291...13.118 s)
    - I/O read: 42.518 s
    - I/O write: N/A

Shared buffers:
  - hits: 7688 (~60.10 MiB) from the buffer pool
  - reads: 55695 (~435.10 MiB) from the OS file cache, including disk I/O
  - dirtied: 741 (~5.80 MiB)
  - writes: 0

Plan

 Limit  (cost=101820.66..101832.33 rows=100 width=138) (actual time=14394.963..14395.313 rows=100 loops=1)
   Buffers: shared hit=7688 read=55695 dirtied=741
   I/O Timings: read=42518.401
   ->  Gather Merge  (cost=101809.00..109470.80 rows=65668 width=138) (actual time=14394.893..14395.293 rows=200 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=7688 read=55695 dirtied=741
         I/O Timings: read=42518.401
         ->  Sort  (cost=100808.97..100891.06 rows=32834 width=138) (actual time=14365.666..14365.697 rows=160 loops=3)
               Sort Key: deployments.updated_at, deployments.id DESC
               Sort Method: top-N heapsort  Memory: 131kB
               Buffers: shared hit=7688 read=55695 dirtied=741
               I/O Timings: read=42518.401
               ->  Parallel Index Scan using index_deployments_on_project_id_and_updated_at_and_id on public.deployments  (cost=0.57..99389.91 rows=32834 width=138) (actual time=10.297..14321.364 rows=21443 loops=3)
                     Index Cond: ((deployments.project_id = 278964) AND (deployments.updated_at >= '2021-01-01 00:00:00'::timestamp without time zone))
                     Buffers: shared hit=7650 read=55695 dirtied=741
                     I/O Timings: read=42518.401

The updated_at and id should specify the same sort direction. If the params[:sort] is :asc, both should be ASC. If the params[:sort] is :desc, both should be DESC.

In fact, this query is significantly faster:

Query

explain SELECT "deployments".* FROM "deployments" WHERE "deployments"."project_id" = 278964 AND "deployments"."updated_at" >= '2021-01-01' ORDER BY "deployments"."updated_at" ASC, "deployments"."id" ASC LIMIT 100 OFFSET 100

Timing

Time: 3.562 ms
  - planning: 0.416 ms
  - execution: 3.146 ms
    - I/O read: 1.910 ms
    - I/O write: N/A

Shared buffers:
  - hits: 208 (~1.60 MiB) from the buffer pool
  - reads: 1 (~8.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Plan

 Limit  (cost=127.28..253.99 rows=100 width=138) (actual time=2.817..3.109 rows=100 loops=1)
   Buffers: shared hit=208 read=1
   I/O Timings: read=1.910
   ->  Index Scan using index_deployments_on_project_id_and_updated_at_and_id on public.deployments  (cost=0.57..99849.58 rows=78801 width=138) (actual time=2.098..3.085 rows=200 loops=1)
         Index Cond: ((deployments.project_id = 278964) AND (deployments.updated_at >= '2021-01-01 00:00:00'::timestamp without time zone))
         Buffers: shared hit=208 read=1
         I/O Timings: read=1.910

Related #325627 (closed)

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

• I have included a changelog entry, or it's not needed. (Does this MR need a changelog?)
• I have added/updated documentation, or it's not needed. (Is documentation required?)
• I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?)
• I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?)
• I have self-reviewed this MR per code review guidelines.
• This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines)
• I have followed the style guides.

Availability and Testing

• I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.)
• I have tested this MR in all supported browsers, or it's not needed.
• I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

• Label as security and @ mention @gitlab-com/gl-security/appsec
• The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• Security reports checked/validated by a reviewer from the AppSec team