Skip to content

Improve query to retrieve job artifacts with files stored locally

What does this MR do?

Remove the check for file_store being null since we already have a default value set for the file_store column in the ci_job_artifacts table see !34568 (merged).

Query plan

  • Before

    SELECT
    	"ci_job_artifacts".*
    FROM
    	"ci_job_artifacts"
    WHERE ("ci_job_artifacts"."file_store" = 1
    	OR "ci_job_artifacts"."file_store" IS NULL);

    Plan with execution: https://explain.depesz.com/s/T83b

    Summary:

    Time: 5.015 min
      - planning: 0.169 ms
      - execution: 5.015 min
        - I/O read: 4.933 min
        - I/O write: 0.000 ms
    
    Shared buffers:
      - hits: 31371 (~245.10 MiB) from the buffer pool
      - reads: 249009 (~1.90 GiB) from the OS file cache, including disk I/O
      - dirtied: 96808 (~756.30 MiB)
      - writes: 0
  • After

    SELECT
    	"ci_job_artifacts".*
    FROM
    	"ci_job_artifacts"
    WHERE
    	"ci_job_artifacts"."file_store" = 1;

    Plan with execution: https://explain.depesz.com/s/F4TS

    Summary:

    Time: 43.056 s
      - planning: 0.208 ms
      - execution: 43.056 s
        - I/O read: 41.242 s
        - I/O write: 237.318 ms
    
    Shared buffers:
      - hits: 294283 (~2.20 GiB) from the buffer pool
      - reads: 142276 (~1.10 GiB) from the OS file cache, including disk I/O
      - dirtied: 21674 (~169.30 MiB)
      - writes: 6882 (~53.80 MiB)

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

Closes #223788 (closed)

Edited by Lucas Charles

Merge request reports

Loading