Filter by most recent in DastSiteValidationsFinder
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
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entrybehind feature flag -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
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
Edited by Philip Cunningham