Skip to content

Add option to skip ordering when using DISTINCT

Heinrich Lee Yu requested to merge 325725-skip-ordering-for-ci-shared-runners-cte-query into master

What does this MR do?

When using the DISTINCT workaround, we had to reorder the results because some instances depended on the breadth-first ordering of results and DISTINCT reorders the result set.

This is not required in all cases so we add an option to skip this so that we can optimize some queries that are executed very frequently.

This also enables the option on the CI query for shared runners that query for root ancestors.

Related to #325725 (closed)

Old query

https://explain.depesz.com/s/GTyf

Time: 772.052 ms  
  - planning: 6.445 ms  
  - execution: 765.607 ms  
    - I/O read: N/A  
    - I/O write: N/A  
  
Shared buffers:  
  - hits: 329955 (~2.50 GiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

New query

https://explain.depesz.com/s/CobB

Time: 582.476 ms  
  - planning: 4.949 ms  
  - execution: 577.527 ms  
    - I/O read: N/A  
    - I/O write: N/A  
  
Shared buffers:  
  - hits: 292515 (~2.20 GiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • 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
Edited by Heinrich Lee Yu

Merge request reports