Filter by most recent in DastSiteValidationsFinder (!49663) · Merge requests · GitLab.org / GitLab

What does this MR do?

adds ability to filter by most_recent in DastSiteValidationsFinder and uses the finder in the graphql resolver.

Why?

more often than not you'll only want to know the most recent validation status and not historical values as well e.g. in the case of the frontend, which uses this query to determine the status of a given target.

database analysis

Setup

i've opted to seed my local environment because there aren't sufficient rows on https://gitlab.com to do any meaningful analysis.

[10] Project.count
=> 5
[11] DastSiteToken.count
=> 1000
[12] DastSiteValidation.count
=> 100000

projects = Array.new(5) { create(:project) }

dast_site_token_attrs = Array.new(1000) do
  build(:dast_site_token, project: projects.sample, created_at: Time.now.utc, updated_at: Time.now.utc).attributes.except('id')
end

result = DastSiteToken.insert_all(dast_site_token_attrs)

dast_site_token_ids = result.rows.map(&:first)

states = DastSiteValidation.state_machine.states.map(&:value)

urls = Array.new(20) { DastSiteValidation.get_normalized_url_base(generate(:url)) }

dast_site_validation_attrs = Array.new(100000) do
  build(:dast_site_validation, dast_site_token_id: dast_site_token_ids.sample, state: states.sample, url_base: urls.sample, created_at: Time.now.utc, updated_at: Time.now.utc)
    .attributes
    .except('id')
end

result = DastSiteValidation.insert_all(dast_site_validation_attrs)

Query

SELECT
   "dast_site_validations".*
FROM
   "dast_site_validations"
   INNER JOIN
      "dast_site_tokens"
      ON "dast_site_tokens"."id" = "dast_site_validations"."dast_site_token_id"
WHERE
   "dast_site_validations"."id" IN
   (
      SELECT
         MAX(id) AS id
      FROM
         "dast_site_validations"
      GROUP BY
         "dast_site_validations"."url_base"
   )
   AND "dast_site_tokens"."project_id" = 1
   AND "dast_site_validations"."url_base" IN
   (
      'http://example1001.test:80',
      'http://example1002.test:80',
      'http://example1003.test:80',
      'http://example1004.test:80',
      'http://example1005.test:80',
      'http://example1006.test:80',
      'http://example1007.test:80',
      'http://example1008.test:80',
      'http://example1009.test:80',
      'http://example1010.test:80'
   )
ORDER BY
   "dast_site_validations"."id" DESC

Analysis

Sort  (cost=3428.24..3428.24 rows=1 width=162) (actual time=36.292..36.294 rows=4 loops=1)
  Sort Key: dast_site_validations.id DESC
  Sort Method: quicksort  Memory: 26kB
  ->  Nested Loop  (cost=3023.83..3428.23 rows=1 width=162) (actual time=36.223..36.279 rows=4 loops=1)
        ->  Nested Loop  (cost=3023.55..3425.21 rows=10 width=162) (actual time=36.216..36.255 rows=10 loops=1)
              ->  HashAggregate  (cost=3023.26..3025.26 rows=200 width=8) (actual time=36.202..36.206 rows=20 loops=1)
                    Group Key: max(dast_site_validations_1.id)
                    ->  HashAggregate  (cost=3018.76..3020.76 rows=200 width=40) (actual time=36.191..36.195 rows=20 loops=1)
                          Group Key: dast_site_validations_1.url_base
                          ->  Seq Scan on dast_site_validations dast_site_validations_1  (cost=0.00..2629.84 rows=77784 width=40) (actual time=0.006..11.039 rows=100000 loops=1)
              ->  Index Scan using dast_site_validations_pkey on dast_site_validations  (cost=0.29..2.01 rows=1 width=162) (actual time=0.002..0.002 rows=0 loops=20)
                    Index Cond: (id = (max(dast_site_validations_1.id)))
                    Filter: (url_base = ANY ('{http://example1001.test:80,http://example1002.test:80,http://example1003.test:80,http://example1004.test:80,http://example1005.test:80,http://example1006.test:80,http://example1007.test:80,http://example1008.test:80,http://example1009.test:80,http://example1010.test:80}'::text[]))
                    Rows Removed by Filter: 0
        ->  Index Scan using dast_site_tokens_pkey on dast_site_tokens  (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=10)
              Index Cond: (id = dast_site_validations.dast_site_token_id)
              Filter: (project_id = 1)
              Rows Removed by Filter: 1

Planning Time: 0.353 ms
Execution Time: 36.338 ms

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

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

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

Filter by most recent in DastSiteValidationsFinder