django-DefectDojo
django-DefectDojo copied to clipboard
DefectDojo
Optimize queryset annotations & prefetches to cut DB time for test / finding / product views (issue #12575)
Fixes DefectDojo #12575
This PR is a pure-Python refactor that keeps every public API and template untouched while eliminating the two main performance bottlenecks reported in the issue: • Correlated sub-queries per row → replaced with a single Subquery aggregation + Coalesce helper (build_count_subquery) • Many small “counter” queries → moved to conditional Count() annotations executed once per list view
:red_circle: Risk threshold exceeded.
This pull request contains multiple sensitive file edits across various Django view and utility files, with potential risks including a subquery field name injection vulnerability, incorrect subquery aggregation logic, and performance risks in complex database queries, though none of the findings are blocking and most are considered low-risk.
:red_circle: Configured Codepaths Edit in dojo/engagement/views.py
| Vulnerability | Configured Codepaths Edit |
|---|---|
| Description | Sensitive edits detected for this file. Sensitive file paths and allowed authors can be configured in .dryrunsecurity.yaml. |
:red_circle: Configured Codepaths Edit in dojo/finding/views.py
| Vulnerability | Configured Codepaths Edit |
|---|---|
| Description | Sensitive edits detected for this file. Sensitive file paths and allowed authors can be configured in .dryrunsecurity.yaml. |
:red_circle: Configured Codepaths Edit in dojo/product/views.py
| Vulnerability | Configured Codepaths Edit |
|---|---|
| Description | Sensitive edits detected for this file. Sensitive file paths and allowed authors can be configured in .dryrunsecurity.yaml. |
:red_circle: Configured Codepaths Edit in dojo/product_type/views.py
| Vulnerability | Configured Codepaths Edit |
|---|---|
| Description | Sensitive edits detected for this file. Sensitive file paths and allowed authors can be configured in .dryrunsecurity.yaml. |
:red_circle: Configured Codepaths Edit in dojo/utils.py
| Vulnerability | Configured Codepaths Edit |
|---|---|
| Description | Sensitive edits detected for this file. Sensitive file paths and allowed authors can be configured in .dryrunsecurity.yaml. |
:red_circle: Configured Codepaths Edit in dojo/utils.py
| Vulnerability | Configured Codepaths Edit |
|---|---|
| Description | Sensitive edits detected for this file. Sensitive file paths and allowed authors can be configured in .dryrunsecurity.yaml. |
:red_circle: Configured Codepaths Edit in dojo/engagement/views.py
| Vulnerability | Configured Codepaths Edit |
|---|---|
| Description | Sensitive edits detected for this file. Sensitive file paths and allowed authors can be configured in .dryrunsecurity.yaml. |
:red_circle: Configured Codepaths Edit in dojo/templates/dojo/engagement.html
| Vulnerability | Configured Codepaths Edit |
|---|---|
| Description | Sensitive edits detected for this file. Sensitive file paths and allowed authors can be configured in .dryrunsecurity.yaml. |
Subquery Field Name Injection Risk in dojo/utils.py
| Vulnerability | Subquery Field Name Injection Risk |
|---|---|
| Description | The build_count_subquery function uses an unvalidated group_field parameter directly in Django ORM queries. While Django's ORM provides some protections, passing unvalidated field names could potentially lead to unexpected query behavior. The risk is low because Django's ORM typically sanitizes field names, but it's worth adding input validation to prevent any potential manipulation. |
https://github.com/DefectDojo/django-DefectDojo/blob/f6f89c12a27e14744ef5a4234684150445e5485e/dojo/utils.py#L28-L34
Incorrect Subquery Aggregation Logic in dojo/utils.py
| Vulnerability | Incorrect Subquery Aggregation Logic |
|---|---|
| Description | The build_count_subquery function uses [:1] to slice the subquery result, which contradicts its docstring of returning 'one aggregated count per group_field'. This could lead to incomplete or incorrect data aggregation, potentially causing unexpected application behavior. The [:1] slice means only the first group's count is returned, not counts for all distinct groups. |
https://github.com/DefectDojo/django-DefectDojo/blob/f6f89c12a27e14744ef5a4234684150445e5485e/dojo/utils.py#L2671-L2681
Performance Risk in Complex Subqueries in dojo/finding/views.py
| Vulnerability | Performance Risk in Complex Subqueries |
|---|---|
| Description | The introduced subqueries in the finding views (active_endpoint_count and mitigated_endpoint_count) use complex database operations with OuterRef, Coalesce, and custom build_count_subquery. While not a direct security vulnerability, these could potentially cause performance degradation or resource exhaustion under high load or with large datasets. |
https://github.com/DefectDojo/django-DefectDojo/blob/f6f89c12a27e14744ef5a4234684150445e5485e/dojo/finding/views.py#L6-L12
We've notified @mtesauro.
All finding details can be found in the DryRun Security Dashboard.
![dryrunsecurity[bot] avatar](https://avatars.githubusercontent.com/in/377039?v=4 "Published by a pub.dev verified publisher"){kind=small}
Thank you for the PR, finally someone who dived into the Django ORM deeper to optimize these queries. I am going to take a close look soon. For those who are interested but don't have much time it could be helpful to add the "before" SQL query and "after" SQL to also see the difference at the actual DB query level.
Over the weekend, I will try to add SQL queries and execution times to our data set.
The queries generated by Django on our dataset
1) Engagements
Before
Execution time: ~40 s
SELECT "dojo_test"."id",
"dojo_test"."engagement_id",
"dojo_test"."lead_id",
"dojo_test"."test_type_id",
"dojo_test"."scan_type",
"dojo_test"."title",
"dojo_test"."description",
"dojo_test"."target_start",
"dojo_test"."target_end",
"dojo_test"."estimated_time",
"dojo_test"."actual_time",
"dojo_test"."percent_complete",
"dojo_test"."environment_id",
"dojo_test"."updated",
"dojo_test"."created",
"dojo_test"."version",
"dojo_test"."build_id",
"dojo_test"."commit_hash",
"dojo_test"."branch_tag",
"dojo_test"."api_scan_configuration_id",
COUNT(DISTINCT "dojo_finding"."id") AS "count_findings_test_all",
COUNT(DISTINCT "dojo_finding"."id")
FILTER (WHERE "dojo_finding"."active") AS "count_findings_test_active",
COUNT(DISTINCT "dojo_finding"."id")
FILTER (WHERE ("dojo_finding"."active" AND "dojo_finding"."verified")) AS "count_findings_test_active_verified",
COUNT(DISTINCT "dojo_finding"."id")
FILTER (WHERE "dojo_finding"."is_mitigated") AS "count_findings_test_mitigated",
COUNT(DISTINCT "dojo_finding"."id")
FILTER (WHERE "dojo_finding"."duplicate") AS "count_findings_test_dups",
COUNT(DISTINCT "dojo_test_import"."id")
FILTER (WHERE "dojo_test_import"."type" = 'reimport') AS "total_reimport_count",
"auth_user"."id",
"auth_user"."password",
"auth_user"."last_login",
"auth_user"."is_superuser",
"auth_user"."username",
"auth_user"."first_name",
"auth_user"."last_name",
"auth_user"."email",
"auth_user"."is_staff",
"auth_user"."is_active",
"auth_user"."date_joined"
FROM "dojo_test"
LEFT OUTER JOIN "dojo_finding" ON ("dojo_test"."id" = "dojo_finding"."test_id")
LEFT OUTER JOIN "dojo_test_import" ON ("dojo_test"."id" = "dojo_test_import"."test_id")
LEFT OUTER JOIN "auth_user" ON ("dojo_test"."lead_id" = "auth_user"."id")
INNER JOIN "dojo_test_type" ON ("dojo_test"."test_type_id" = "dojo_test_type"."id")
WHERE "dojo_test"."engagement_id" = 2104
GROUP BY "dojo_test"."id", "auth_user"."id", "dojo_test_type"."name"
ORDER BY "dojo_test_type"."name" ASC, "dojo_test"."updated" DESC
LIMIT 2;
After
Execution time: ~400 ms
SELECT "dojo_test"."id",
"dojo_test"."engagement_id",
"dojo_test"."lead_id",
"dojo_test"."test_type_id",
"dojo_test"."scan_type",
"dojo_test"."title",
"dojo_test"."description",
"dojo_test"."target_start",
"dojo_test"."target_end",
"dojo_test"."estimated_time",
"dojo_test"."actual_time",
"dojo_test"."percent_complete",
"dojo_test"."environment_id",
"dojo_test"."updated",
"dojo_test"."created",
"dojo_test"."version",
"dojo_test"."build_id",
"dojo_test"."commit_hash",
"dojo_test"."branch_tag",
"dojo_test"."api_scan_configuration_id",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_finding" U0
WHERE U0."test_id" = ("dojo_test"."id")
GROUP BY U0."test_id"
LIMIT 1), 0) AS "count_findings_test_all",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_finding" U0
WHERE (U0."test_id" = ("dojo_test"."id") AND U0."active")
GROUP BY U0."test_id"
LIMIT 1), 0) AS "count_findings_test_active",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_finding" U0
WHERE (U0."test_id" = ("dojo_test"."id") AND U0."active" AND U0."verified")
GROUP BY U0."test_id"
LIMIT 1), 0) AS "count_findings_test_active_verified",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_finding" U0
WHERE (U0."test_id" = ("dojo_test"."id") AND U0."is_mitigated")
GROUP BY U0."test_id"
LIMIT 1), 0) AS "count_findings_test_mitigated",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_finding" U0
WHERE (U0."test_id" = ("dojo_test"."id") AND U0."duplicate")
GROUP BY U0."test_id"
LIMIT 1), 0) AS "count_findings_test_dups",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_test_import" U0
WHERE (U0."test_id" = ("dojo_test"."id") AND U0."type" = 'reimport')
GROUP BY U0."test_id"
LIMIT 1), 0) AS "total_reimport_count",
"auth_user"."id",
"auth_user"."password",
"auth_user"."last_login",
"auth_user"."is_superuser",
"auth_user"."username",
"auth_user"."first_name",
"auth_user"."last_name",
"auth_user"."email",
"auth_user"."is_staff",
"auth_user"."is_active",
"auth_user"."date_joined",
"dojo_test_type"."id",
"dojo_test_type"."name",
"dojo_test_type"."static_tool",
"dojo_test_type"."dynamic_tool",
"dojo_test_type"."active",
"dojo_test_type"."dynamically_generated"
FROM "dojo_test"
LEFT OUTER JOIN "auth_user" ON ("dojo_test"."lead_id" = "auth_user"."id")
INNER JOIN "dojo_test_type" ON ("dojo_test"."test_type_id" = "dojo_test_type"."id")
WHERE "dojo_test"."engagement_id" = 2104
ORDER BY "dojo_test_type"."name" ASC, "dojo_test"."updated" DESC
LIMIT 2
2) Findings
Before
Execution time: ~5 s
SELECT "dojo_finding"."id",
"dojo_finding"."title",
"dojo_finding"."date",
"dojo_finding"."sla_start_date",
"dojo_finding"."sla_expiration_date",
"dojo_finding"."cwe",
"dojo_finding"."cve",
"dojo_finding"."epss_score",
"dojo_finding"."epss_percentile",
"dojo_finding"."cvssv3",
"dojo_finding"."cvssv3_score",
"dojo_finding"."url",
"dojo_finding"."severity",
"dojo_finding"."description",
"dojo_finding"."mitigation",
"dojo_finding"."impact",
"dojo_finding"."steps_to_reproduce",
"dojo_finding"."severity_justification",
"dojo_finding"."refs",
"dojo_finding"."test_id",
"dojo_finding"."active",
"dojo_finding"."verified",
"dojo_finding"."false_p",
"dojo_finding"."duplicate",
"dojo_finding"."duplicate_finding_id",
"dojo_finding"."out_of_scope",
"dojo_finding"."risk_accepted",
"dojo_finding"."under_review",
"dojo_finding"."last_status_update",
"dojo_finding"."review_requested_by_id",
"dojo_finding"."under_defect_review",
"dojo_finding"."defect_review_requested_by_id",
"dojo_finding"."is_mitigated",
"dojo_finding"."thread_id",
"dojo_finding"."mitigated",
"dojo_finding"."mitigated_by_id",
"dojo_finding"."reporter_id",
"dojo_finding"."numerical_severity",
"dojo_finding"."last_reviewed",
"dojo_finding"."last_reviewed_by_id",
"dojo_finding"."param",
"dojo_finding"."payload",
"dojo_finding"."hash_code",
"dojo_finding"."line",
"dojo_finding"."file_path",
"dojo_finding"."component_name",
"dojo_finding"."component_version",
"dojo_finding"."static_finding",
"dojo_finding"."dynamic_finding",
"dojo_finding"."created",
"dojo_finding"."scanner_confidence",
"dojo_finding"."sonarqube_issue_id",
"dojo_finding"."unique_id_from_tool",
"dojo_finding"."vuln_id_from_tool",
"dojo_finding"."sast_source_object",
"dojo_finding"."sast_sink_object",
"dojo_finding"."sast_source_line",
"dojo_finding"."sast_source_file_path",
"dojo_finding"."nb_occurences",
"dojo_finding"."publish_date",
"dojo_finding"."service",
"dojo_finding"."planned_remediation_date",
"dojo_finding"."planned_remediation_version",
"dojo_finding"."effort_for_fixing",
COUNT("dojo_endpoint_status"."id")
FILTER (WHERE NOT "dojo_endpoint_status"."mitigated") AS "active_endpoint_count",
COUNT("dojo_endpoint_status"."id")
FILTER (WHERE "dojo_endpoint_status"."mitigated") AS "mitigated_endpoint_count"
FROM "dojo_finding"
LEFT OUTER JOIN "dojo_endpoint_status" ON ("dojo_finding"."id" = "dojo_endpoint_status"."finding_id")
GROUP BY "dojo_finding"."id"
ORDER BY "dojo_finding"."numerical_severity" ASC
LIMIT 25;
After
Execution time: ~500 ms Looks like i brike some default ordering.
SELECT "dojo_finding"."id",
"dojo_finding"."title",
"dojo_finding"."date",
"dojo_finding"."sla_start_date",
"dojo_finding"."sla_expiration_date",
"dojo_finding"."cwe",
"dojo_finding"."cve",
"dojo_finding"."epss_score",
"dojo_finding"."epss_percentile",
"dojo_finding"."cvssv3",
"dojo_finding"."cvssv3_score",
"dojo_finding"."url",
"dojo_finding"."severity",
"dojo_finding"."description",
"dojo_finding"."mitigation",
"dojo_finding"."impact",
"dojo_finding"."steps_to_reproduce",
"dojo_finding"."severity_justification",
"dojo_finding"."refs",
"dojo_finding"."test_id",
"dojo_finding"."active",
"dojo_finding"."verified",
"dojo_finding"."false_p",
"dojo_finding"."duplicate",
"dojo_finding"."duplicate_finding_id",
"dojo_finding"."out_of_scope",
"dojo_finding"."risk_accepted",
"dojo_finding"."under_review",
"dojo_finding"."last_status_update",
"dojo_finding"."review_requested_by_id",
"dojo_finding"."under_defect_review",
"dojo_finding"."defect_review_requested_by_id",
"dojo_finding"."is_mitigated",
"dojo_finding"."thread_id",
"dojo_finding"."mitigated",
"dojo_finding"."mitigated_by_id",
"dojo_finding"."reporter_id",
"dojo_finding"."numerical_severity",
"dojo_finding"."last_reviewed",
"dojo_finding"."last_reviewed_by_id",
"dojo_finding"."param",
"dojo_finding"."payload",
"dojo_finding"."hash_code",
"dojo_finding"."line",
"dojo_finding"."file_path",
"dojo_finding"."component_name",
"dojo_finding"."component_version",
"dojo_finding"."static_finding",
"dojo_finding"."dynamic_finding",
"dojo_finding"."created",
"dojo_finding"."scanner_confidence",
"dojo_finding"."sonarqube_issue_id",
"dojo_finding"."unique_id_from_tool",
"dojo_finding"."vuln_id_from_tool",
"dojo_finding"."sast_source_object",
"dojo_finding"."sast_sink_object",
"dojo_finding"."sast_source_line",
"dojo_finding"."sast_source_file_path",
"dojo_finding"."nb_occurences",
"dojo_finding"."publish_date",
"dojo_finding"."service",
"dojo_finding"."planned_remediation_date",
"dojo_finding"."planned_remediation_version",
"dojo_finding"."effort_for_fixing",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_endpoint_status" U0
WHERE (U0."finding_id" = ("dojo_finding"."id") AND NOT U0."mitigated")
GROUP BY U0."finding_id"
LIMIT 1), 0) AS "active_endpoint_count",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_endpoint_status" U0
WHERE (U0."finding_id" = ("dojo_finding"."id") AND U0."mitigated")
GROUP BY U0."finding_id"
LIMIT 1), 0) AS "mitigated_endpoint_count"
FROM "dojo_finding"
ORDER BY "dojo_finding"."numerical_severity" ASC
LIMIT 25;
3) Product
Before
Execution time: ~3 s
SELECT "dojo_product"."id",
"dojo_product"."name",
"dojo_product"."description",
"dojo_product"."product_manager_id",
"dojo_product"."technical_contact_id",
"dojo_product"."team_manager_id",
"dojo_product"."created",
"dojo_product"."prod_type_id",
"dojo_product"."updated",
"dojo_product"."sla_configuration_id",
"dojo_product"."tid",
"dojo_product"."prod_numeric_grade",
"dojo_product"."business_criticality",
"dojo_product"."platform",
"dojo_product"."lifecycle",
"dojo_product"."origin",
"dojo_product"."user_records",
"dojo_product"."revenue",
"dojo_product"."external_audience",
"dojo_product"."internet_accessible",
"dojo_product"."enable_product_tag_inheritance",
"dojo_product"."enable_simple_risk_acceptance",
"dojo_product"."enable_full_risk_acceptance",
"dojo_product"."disable_sla_breach_notifications",
"dojo_product"."async_updating",
COUNT("dojo_finding"."id") FILTER (WHERE "dojo_finding"."active") AS "findings_count",
COUNT("dojo_engagement"."id")
FILTER (WHERE "dojo_engagement"."active") AS "active_engagement_count",
COUNT("dojo_engagement"."id")
FILTER (WHERE NOT "dojo_engagement"."active") AS "closed_engagement_count",
MAX("dojo_engagement"."target_start") AS "last_engagement_date",
COUNT("dojo_finding"."id") FILTER (WHERE "dojo_finding"."active") AS "active_finding_count",
COUNT("dojo_finding"."id")
FILTER (WHERE ("dojo_finding"."active" AND "dojo_finding"."verified")) AS "active_verified_finding_count"
FROM "dojo_product"
LEFT OUTER JOIN "dojo_engagement" ON ("dojo_product"."id" = "dojo_engagement"."product_id")
LEFT OUTER JOIN "dojo_test" ON ("dojo_engagement"."id" = "dojo_test"."engagement_id")
LEFT OUTER JOIN "dojo_finding" ON ("dojo_test"."id" = "dojo_finding"."test_id")
GROUP BY "dojo_product"."id"
ORDER BY "dojo_product"."name" ASC
LIMIT 25;
After
Execution time: ~400 ms
SELECT "dojo_product"."id",
"dojo_product"."name",
"dojo_product"."description",
"dojo_product"."product_manager_id",
"dojo_product"."technical_contact_id",
"dojo_product"."team_manager_id",
"dojo_product"."created",
"dojo_product"."prod_type_id",
"dojo_product"."updated",
"dojo_product"."sla_configuration_id",
"dojo_product"."tid",
"dojo_product"."prod_numeric_grade",
"dojo_product"."business_criticality",
"dojo_product"."platform",
"dojo_product"."lifecycle",
"dojo_product"."origin",
"dojo_product"."user_records",
"dojo_product"."revenue",
"dojo_product"."external_audience",
"dojo_product"."internet_accessible",
"dojo_product"."enable_product_tag_inheritance",
"dojo_product"."enable_simple_risk_acceptance",
"dojo_product"."enable_full_risk_acceptance",
"dojo_product"."disable_sla_breach_notifications",
"dojo_product"."async_updating",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_finding" U0
INNER JOIN "dojo_test" U1 ON (U0."test_id" = U1."id")
INNER JOIN "dojo_engagement" U2 ON (U1."engagement_id" = U2."id")
WHERE (U0."active" AND U2."product_id" = ("dojo_product"."id"))
GROUP BY U2."product_id"
LIMIT 1), 0) AS "findings_count",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_engagement" U0
WHERE (U0."product_id" = ("dojo_product"."id") AND U0."active")
GROUP BY U0."product_id"
LIMIT 1), 0) AS "active_engagement_count",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_engagement" U0
WHERE (U0."product_id" = ("dojo_product"."id") AND NOT U0."active")
GROUP BY U0."product_id"
LIMIT 1), 0) AS "closed_engagement_count",
(SELECT U0."target_start"
FROM "dojo_engagement" U0
WHERE U0."product_id" = ("dojo_product"."id")
ORDER BY U0."target_start" DESC
LIMIT 1) AS "last_engagement_date",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_finding" U0
INNER JOIN "dojo_test" U1 ON (U0."test_id" = U1."id")
INNER JOIN "dojo_engagement" U2 ON (U1."engagement_id" = U2."id")
WHERE (U2."product_id" = ("dojo_product"."id") AND U0."active")
GROUP BY U2."product_id"
LIMIT 1), 0) AS "active_finding_count",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_finding" U0
INNER JOIN "dojo_test" U1 ON (U0."test_id" = U1."id")
INNER JOIN "dojo_engagement" U2 ON (U1."engagement_id" = U2."id")
WHERE (U2."product_id" = ("dojo_product"."id") AND U0."active" AND U0."verified")
GROUP BY U2."product_id"
LIMIT 1), 0) AS "active_verified_finding_count",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_test_import" U0
INNER JOIN "dojo_test" U1 ON (U0."test_id" = U1."id")
INNER JOIN "dojo_engagement" U2 ON (U1."engagement_id" = U2."id")
WHERE (U2."product_id" = ("dojo_product"."id") AND U0."type" = 'reimport')
GROUP BY U2."product_id"
LIMIT 1), 0) AS "total_reimport_count",
"auth_user"."id",
"auth_user"."password",
"auth_user"."last_login",
"auth_user"."is_superuser",
"auth_user"."username",
"auth_user"."first_name",
"auth_user"."last_name",
"auth_user"."email",
"auth_user"."is_staff",
"auth_user"."is_active",
"auth_user"."date_joined",
T3."id",
T3."password",
T3."last_login",
T3."is_superuser",
T3."username",
T3."first_name",
T3."last_name",
T3."email",
T3."is_staff",
T3."is_active",
T3."date_joined",
T4."id",
T4."password",
T4."last_login",
T4."is_superuser",
T4."username",
T4."first_name",
T4."last_name",
T4."email",
T4."is_staff",
T4."is_active",
T4."date_joined"
FROM "dojo_product"
LEFT OUTER JOIN "auth_user" ON ("dojo_product"."product_manager_id" = "auth_user"."id")
LEFT OUTER JOIN "auth_user" T3 ON ("dojo_product"."technical_contact_id" = T3."id")
LEFT OUTER JOIN "auth_user" T4 ON ("dojo_product"."team_manager_id" = T4."id")
ORDER BY "dojo_product"."name" ASC
LIMIT 25;
4) Product Type
Before
Execution time: ~3 s
SELECT "dojo_product_type"."id",
"dojo_product_type"."name",
"dojo_product_type"."description",
"dojo_product_type"."critical_product",
"dojo_product_type"."key_product",
"dojo_product_type"."updated",
"dojo_product_type"."created",
COUNT("dojo_finding"."id") FILTER (WHERE "dojo_finding"."active") AS "active_findings_count",
COUNT("dojo_finding"."id")
FILTER (WHERE ("dojo_finding"."active" AND "dojo_finding"."verified")) AS "active_verified_findings_count",
COUNT(DISTINCT "dojo_product"."id") AS "prod_count"
FROM "dojo_product_type"
LEFT OUTER JOIN "dojo_product" ON ("dojo_product_type"."id" = "dojo_product"."prod_type_id")
LEFT OUTER JOIN "dojo_engagement" ON ("dojo_product"."id" = "dojo_engagement"."product_id")
LEFT OUTER JOIN "dojo_test" ON ("dojo_engagement"."id" = "dojo_test"."engagement_id")
LEFT OUTER JOIN "dojo_finding" ON ("dojo_test"."id" = "dojo_finding"."test_id")
GROUP BY "dojo_product_type"."id"
ORDER BY "dojo_product_type"."name" ASC
LIMIT 25;
After
Execution time: ~400 ms
SELECT "dojo_product_type"."id",
"dojo_product_type"."name",
"dojo_product_type"."description",
"dojo_product_type"."critical_product",
"dojo_product_type"."key_product",
"dojo_product_type"."updated",
"dojo_product_type"."created",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_product" U0
WHERE U0."prod_type_id" = ("dojo_product_type"."id")
GROUP BY U0."prod_type_id"
LIMIT 1), 0) AS "prod_count",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_finding" U0
INNER JOIN "dojo_test" U1 ON (U0."test_id" = U1."id")
INNER JOIN "dojo_engagement" U2 ON (U1."engagement_id" = U2."id")
INNER JOIN "dojo_product" U3 ON (U2."product_id" = U3."id")
WHERE (U3."prod_type_id" = ("dojo_product_type"."id") AND U0."active")
GROUP BY U3."prod_type_id"
LIMIT 1), 0) AS "active_findings_count",
COALESCE((SELECT COUNT(*) AS "c"
FROM "dojo_finding" U0
INNER JOIN "dojo_test" U1 ON (U0."test_id" = U1."id")
INNER JOIN "dojo_engagement" U2 ON (U1."engagement_id" = U2."id")
INNER JOIN "dojo_product" U3 ON (U2."product_id" = U3."id")
WHERE (U3."prod_type_id" = ("dojo_product_type"."id") AND U0."active" AND U0."verified")
GROUP BY U3."prod_type_id"
LIMIT 1), 0) AS "active_verified_findings_count"
FROM "dojo_product_type"
ORDER BY "dojo_product_type"."name" ASC
LIMIT 25;
Thanks for the great PR. I remember when we made the improvement from 1+N queries to 1 annotated query. That was really making things a lot faster. Until the Defect Dojo instance gets a little bigger. Now we have this nice improvement with corralated subqueries. I do remember some earlier attempts, but we got stuck at MySQL not supporting LIMIT in subqueries.
We could probably enhance this in more places. I wouldn't dare to ask you to do all of them.... but there's one place I think would be nice to improve here as well:
https://github.com/DefectDojo/django-DefectDojo/blob/be90ad9aa429271548a2d2b6ff764c03a5274066/dojo/engagement/views.py#L178-L190
The soluation chosen there was to execute a separate query to retrieve the counts. But the same approach as this PR could be used there to retrieve those with a subquery as well?
Execution time: ~500 ms Looks like i brike some default ordering.
I did a quick check and for me the findings are still ordered by numerical severity, which looks correct to me? This is in a small instance though. Did you see something weird in a bigger instance?
This pull request has conflicts, please resolve those before we can evaluate the pull request.
![github-actions[bot] avatar](https://avatars.githubusercontent.com/in/15368?v=4 "Published by a pub.dev verified publisher"){kind=small}
Sry, I have been on the vacation, will continue working this PR this week.
- About this (https://github.com/DefectDojo/django-DefectDojo/pull/12603#issuecomment-2982864693) - I mean the secondary ordering, i see it's different from initial implementation. BTW the first ordering is still ordered by numerical severity, you are right
- About this(https://github.com/DefectDojo/django-DefectDojo/pull/12603#issuecomment-2982860429) - I know that there much more places, but for our dataset, I did not saw any signficant improvements and changed the thing where we see the problem. I can take a look on the place you show and try to make the same thing. Soon I will receive a set of data from another team of ours, where we already know about other problem areas. I planned to open them with additional PR, cause do not know when I can get this dataset (maybe tomorrow, maybe few weeks)
Conflicts have been resolved. A maintainer will review the pull request shortly.
valentijnscholten
I have added new commit with changes you asked before
@DenysMoskalenko We have a report of someone who's product page has become very slow since the 2.48.0 release. Not sure if it's related to this PR, but these are the query detail:
SELECT "dojo_product"."id",
"dojo_product"."name",
"dojo_product"."description",
"dojo_product"."product_manager_id",
"dojo_product"."technical_contact_id",
"dojo_product"."team_manager_id",
"dojo_product"."created",
"dojo_product"."prod_type_id",
"dojo_product"."updated",
"dojo_product"."sla_configuration_id",
"dojo_product"."tid",
"dojo_product"."prod_numeric_grade",
"dojo_product"."business_criticality",
"dojo_product"."platform",
"dojo_product"."lifecycle",
"dojo_product"."origin",
"dojo_product"."user_records",
"dojo_product"."revenue",
"dojo_product"."external_audience",
"dojo_product"."internet_accessible",
"dojo_product"."enable_product_tag_inheritance",
"dojo_product"."enable_simple_risk_acceptance",
"dojo_product"."enable_full_risk_acceptance",
"dojo_product"."disable_sla_breach_notifications",
"dojo_product"."async_updating",
Coalesce((SELECT Count(*) AS "c"
FROM "dojo_finding" U0
INNER JOIN "dojo_test" U1
ON ( U0."test_id" = U1."id" )
INNER JOIN "dojo_engagement" U2
ON ( U1."engagement_id" = U2."id" )
WHERE ( U0."active"
AND U2."product_id" = ( "dojo_product"."id" ) )
GROUP BY U2."product_id"
LIMIT 1), 0) AS "findings_count",
Coalesce((SELECT Count(*) AS "c"
FROM "dojo_engagement" U0
WHERE ( U0."product_id" = ( "dojo_product"."id" )
AND U0."active" )
GROUP BY U0."product_id"
LIMIT 1), 0) AS "active_engagement_count",
Coalesce((SELECT Count(*) AS "c"
FROM "dojo_engagement" U0
WHERE ( U0."product_id" = ( "dojo_product"."id" )
AND NOT U0."active" )
GROUP BY U0."product_id"
LIMIT 1), 0) AS "closed_engagement_count",
(SELECT U0."target_start"
FROM "dojo_engagement" U0
WHERE U0."product_id" = ( "dojo_product"."id" )
ORDER BY U0."target_start" DESC
LIMIT 1) AS "last_engagement_date",
Coalesce((SELECT Count(*) AS "c"
FROM "dojo_finding" U0
INNER JOIN "dojo_test" U1
ON ( U0."test_id" = U1."id" )
INNER JOIN "dojo_engagement" U2
ON ( U1."engagement_id" = U2."id" )
WHERE ( U2."product_id" = ( "dojo_product"."id" )
AND U0."active" )
GROUP BY U2."product_id"
LIMIT 1), 0) AS "active_finding_count",
Coalesce((SELECT Count(*) AS "c"
FROM "dojo_finding" U0
INNER JOIN "dojo_test" U1
ON ( U0."test_id" = U1."id" )
INNER JOIN "dojo_engagement" U2
ON ( U1."engagement_id" = U2."id" )
WHERE ( U2."product_id" = ( "dojo_product"."id" )
AND U0."active"
AND U0."verified" )
GROUP BY U2."product_id"
LIMIT 1), 0) AS "active_verified_finding_count",
Coalesce((SELECT Count(*) AS "c"
FROM "dojo_test_import" U0
INNER JOIN "dojo_test" U1
ON ( U0."test_id" = U1."id" )
INNER JOIN "dojo_engagement" U2
ON ( U1."engagement_id" = U2."id" )
WHERE ( U2."product_id" = ( "dojo_product"."id" )
AND U0."type" = 'reimport' )
GROUP BY U2."product_id"
LIMIT 1), 0) AS "total_reimport_count",
"auth_user"."id",
"auth_user"."password",
"auth_user"."last_login",
"auth_user"."is_superuser",
"auth_user"."username",
"auth_user"."first_name",
"auth_user"."last_name",
"auth_user"."email",
"auth_user"."is_staff",
"auth_user"."is_active",
"auth_user"."date_joined",
T3."id",
T3."password",
T3."last_login",
T3."is_superuser",
T3."username",
T3."first_name",
T3."last_name",
T3."email",
T3."is_staff",
T3."is_active",
T3."date_joined",
T4."id",
T4."password",
T4."last_login",
T4."is_superuser",
T4."username",
T4."first_name",
T4."last_name",
T4."email",
T4."is_staff",
T4."is_active",
T4."date_joined"
FROM "dojo_product"
LEFT OUTER JOIN "auth_user"
ON ( "dojo_product"."product_manager_id" =
"auth_user"."id" )
LEFT OUTER JOIN "auth_user" T3
ON ( "dojo_product"."technical_contact_id" = T3."id" )
LEFT OUTER JOIN "auth_user" T4
ON ( "dojo_product"."team_manager_id" = T4."id" )
ORDER BY "dojo_product"."name" ASC
LIMIT 25;
and a explain of the query:
Limit (cost=0.14..1177719.79 rows=25 width=778)
-> Nested Loop Left Join (cost=0.14..4569552.37 rows=97 width=778)
Join Filter: (dojo_product.team_manager_id = t4.id)
-> Nested Loop Left Join (cost=0.14..1196.35 rows=97 width=584)
Join Filter: (dojo_product.technical_contact_id = t3.id)
-> Nested Loop Left Join (cost=0.14..781.32 rows=97 width=442)
Join Filter: (dojo_product.product_manager_id = auth_user.id)
-> Index Scan using dojo_product_name_key on dojo_product (cost=0.14..366.29 rows=97 width=300)
-> Materialize (cost=0.00..14.14 rows=276 width=142)
-> Seq Scan on auth_user (cost=0.00..12.76 rows=276 width=142)
-> Materialize (cost=0.00..14.14 rows=276 width=142)
-> Seq Scan on auth_user t3 (cost=0.00..12.76 rows=276 width=142)
-> Materialize (cost=0.00..14.14 rows=276 width=142)
-> Seq Scan on auth_user t4 (cost=0.00..12.76 rows=276 width=142)
SubPlan 1
-> Limit (cost=4.37..135.80 rows=1 width=12)
-> GroupAggregate (cost=4.37..1318.60 rows=10 width=12)
Group Key: u2.product_id
-> Nested Loop (cost=4.37..1317.43 rows=214 width=4)
-> Nested Loop (cost=3.95..634.74 rows=156 width=8)
-> Index Scan using dojo_engagement_product_id_c46b1b11 on dojo_engagement u2 (cost=0.28..48.32 rows=11 width=8)
Index Cond: (product_id = dojo_product.id)
-> Bitmap Heap Scan on dojo_test u1 (cost=3.67..53.16 rows=15 width=8)
Recheck Cond: (engagement_id = u2.id)
-> Bitmap Index Scan on dojo_test_engagement_id_46ff752d (cost=0.00..3.67 rows=15 width=0)
Index Cond: (engagement_id = u2.id)
-> Index Only Scan using dojo_findin_test_id_bfc47c_idx on dojo_finding u0 (cost=0.42..4.35 rows=3 width=4)
Index Cond: ((test_id = u1.id) AND (active = true))
SubPlan 2
-> Limit (cost=0.28..1.11 rows=1 width=12)
-> GroupAggregate (cost=0.28..8.65 rows=10 width=12)
Group Key: u0_1.product_id
-> Index Only Scan using dojo_engage_product_16b039_idx on dojo_engagement u0_1 (cost=0.28..8.50 rows=11 width=4)
Index Cond: ((product_id = dojo_product.id) AND (active = true))
SubPlan 3
-> Limit (cost=0.28..8.30 rows=1 width=12)
-> GroupAggregate (cost=0.28..8.30 rows=1 width=12)
Group Key: u0_2.product_id
-> Index Only Scan using dojo_engage_product_16b039_idx on dojo_engagement u0_2 (cost=0.28..8.29 rows=1 width=4)
Index Cond: ((product_id = dojo_product.id) AND (active = false))
SubPlan 4
-> Limit (cost=39.06..39.07 rows=1 width=4)
-> Sort (cost=39.06..39.09 rows=11 width=4)
Sort Key: u0_3.target_start DESC
-> Bitmap Heap Scan on dojo_engagement u0_3 (cost=4.36..39.01 rows=11 width=4)
Recheck Cond: (product_id = dojo_product.id)
-> Bitmap Index Scan on dojo_engage_product_16b039_idx (cost=0.00..4.36 rows=11 width=0)
Index Cond: (product_id = dojo_product.id)
SubPlan 5
-> Limit (cost=4.37..135.80 rows=1 width=12)
-> GroupAggregate (cost=4.37..1318.60 rows=10 width=12)
Group Key: u2_1.product_id
-> Nested Loop (cost=4.37..1317.43 rows=214 width=4)
-> Nested Loop (cost=3.95..634.74 rows=156 width=8)
-> Index Scan using dojo_engagement_product_id_c46b1b11 on dojo_engagement u2_1 (cost=0.28..48.32 rows=11 width=8)
Index Cond: (product_id = dojo_product.id)
-> Bitmap Heap Scan on dojo_test u1_1 (cost=3.67..53.16 rows=15 width=8)
Recheck Cond: (engagement_id = u2_1.id)
-> Bitmap Index Scan on dojo_test_engagement_id_46ff752d (cost=0.00..3.67 rows=15 width=0)
Index Cond: (engagement_id = u2_1.id)
-> Index Only Scan using dojo_findin_test_id_bfc47c_idx on dojo_finding u0_4 (cost=0.42..4.35 rows=3 width=4)
Index Cond: ((test_id = u1_1.id) AND (active = true))
SubPlan 6
-> Limit (cost=4.37..135.91 rows=1 width=12)
-> GroupAggregate (cost=4.37..1319.74 rows=10 width=12)
Group Key: u2_2.product_id
-> Nested Loop (cost=4.37..1318.60 rows=208 width=4)
-> Nested Loop (cost=3.95..634.74 rows=156 width=8)
-> Index Scan using dojo_engagement_product_id_c46b1b11 on dojo_engagement u2_2 (cost=0.28..48.32 rows=11 width=8)
Index Cond: (product_id = dojo_product.id)
-> Bitmap Heap Scan on dojo_test u1_2 (cost=3.67..53.16 rows=15 width=8)
Recheck Cond: (engagement_id = u2_2.id)
-> Bitmap Index Scan on dojo_test_engagement_id_46ff752d (cost=0.00..3.67 rows=15 width=0)
Index Cond: (engagement_id = u2_2.id)
-> Index Only Scan using dojo_findin_test_id_bfc47c_idx on dojo_finding u0_5 (cost=0.42..4.35 rows=3 width=4)
Index Cond: ((test_id = u1_2.id) AND (active = true) AND (verified = true))
SubPlan 7
-> Limit (cost=0.44..46636.19 rows=1 width=12)
-> GroupAggregate (cost=0.44..466357.92 rows=10 width=12)
Group Key: u2_3.product_id
-> Nested Loop (cost=0.44..465133.50 rows=244864 width=4)
-> Nested Loop (cost=0.00..3312.86 rows=156 width=8)
Join Filter: (u1_3.engagement_id = u2_3.id)
-> Seq Scan on dojo_engagement u2_3 (cost=0.00..145.97 rows=11 width=8)
Filter: (product_id = dojo_product.id)
-> Materialize (cost=0.00..771.15 rows=14743 width=8)
-> Seq Scan on dojo_test u1_3 (cost=0.00..697.43 rows=14743 width=8)
-> Index Scan using dojo_test_import_test_id_e8dc3f37 on dojo_test_import u0_6 (cost=0.44..2933.00 rows=2739 width=4)
Index Cond: (test_id = u1_3.id)
Filter: ((type)::text = 'reimport'::text)
overall findings we've got about 400k (active, inactive, accepted or mitigated) in our productive instance
I just run the query on our dataset (We have 387 products with total ~1.2kk findings overall, few products has 250k and 150k findings, other 100-7k If we open the pduct page we see the overal timing ~600ms, and see the improvemets (before we had more than 3 seconds)
I also provide our Explain+analyze for you:
Limit (cost=0.27..28814.35 rows=25 width=723) (actual time=25.892..246.073 rows=25 loops=1)
Output: dojo_product.id, dojo_product.name, dojo_product.description, dojo_product.product_manager_id, dojo_product.technical_contact_id, dojo_product.team_manager_id, dojo_product.created, dojo_product.prod_type_id, dojo_product.updated, dojo_product.sla_configuration_id, dojo_product.tid, dojo_product.prod_numeric_grade, dojo_product.business_criticality, dojo_product.platform, dojo_product.lifecycle, dojo_product.origin, dojo_product.user_records, dojo_product.revenue, dojo_product.external_audience, dojo_product.internet_accessible, dojo_product.enable_product_tag_inheritance, dojo_product.enable_simple_risk_acceptance, dojo_product.enable_full_risk_acceptance, dojo_product.disable_sla_breach_notifications, dojo_product.async_updating, (COALESCE((SubPlan 1), '0'::bigint)), (COALESCE((SubPlan 2), '0'::bigint)), (COALESCE((SubPlan 3), '0'::bigint)), ((SubPlan 4)), (COALESCE((SubPlan 5), '0'::bigint)), (COALESCE((SubPlan 6), '0'::bigint)), (COALESCE((SubPlan 7), '0'::bigint)), auth_user.id, auth_user.password, auth_user.last_login, auth_user.is_superuser, auth_user.username, auth_user.first_name, auth_user.last_name, auth_user.email, auth_user.is_staff, auth_user.is_active, auth_user.date_joined, t3.id, t3.password, t3.last_login, t3.is_superuser, t3.username, t3.first_name, t3.last_name, t3.email, t3.is_staff, t3.is_active, t3.date_joined, t4.id, t4.password, t4.last_login, t4.is_superuser, t4.username, t4.first_name, t4.last_name, t4.email, t4.is_staff, t4.is_active, t4.date_joined
-> Nested Loop Left Join (cost=0.27..446042.21 rows=387 width=723) (actual time=25.891..246.068 rows=25 loops=1)
Output: dojo_product.id, dojo_product.name, dojo_product.description, dojo_product.product_manager_id, dojo_product.technical_contact_id, dojo_product.team_manager_id, dojo_product.created, dojo_product.prod_type_id, dojo_product.updated, dojo_product.sla_configuration_id, dojo_product.tid, dojo_product.prod_numeric_grade, dojo_product.business_criticality, dojo_product.platform, dojo_product.lifecycle, dojo_product.origin, dojo_product.user_records, dojo_product.revenue, dojo_product.external_audience, dojo_product.internet_accessible, dojo_product.enable_product_tag_inheritance, dojo_product.enable_simple_risk_acceptance, dojo_product.enable_full_risk_acceptance, dojo_product.disable_sla_breach_notifications, dojo_product.async_updating, COALESCE((SubPlan 1), '0'::bigint), COALESCE((SubPlan 2), '0'::bigint), COALESCE((SubPlan 3), '0'::bigint), (SubPlan 4), COALESCE((SubPlan 5), '0'::bigint), COALESCE((SubPlan 6), '0'::bigint), COALESCE((SubPlan 7), '0'::bigint), auth_user.id, auth_user.password, auth_user.last_login, auth_user.is_superuser, auth_user.username, auth_user.first_name, auth_user.last_name, auth_user.email, auth_user.is_staff, auth_user.is_active, auth_user.date_joined, t3.id, t3.password, t3.last_login, t3.is_superuser, t3.username, t3.first_name, t3.last_name, t3.email, t3.is_staff, t3.is_active, t3.date_joined, t4.id, t4.password, t4.last_login, t4.is_superuser, t4.username, t4.first_name, t4.last_name, t4.email, t4.is_staff, t4.is_active, t4.date_joined
Inner Unique: true
Join Filter: (dojo_product.team_manager_id = t4.id)
Rows Removed by Join Filter: 2675
-> Nested Loop Left Join (cost=0.27..1333.21 rows=387 width=515) (actual time=0.386..0.838 rows=25 loops=1)
Output: dojo_product.id, dojo_product.name, dojo_product.description, dojo_product.product_manager_id, dojo_product.technical_contact_id, dojo_product.team_manager_id, dojo_product.created, dojo_product.prod_type_id, dojo_product.updated, dojo_product.sla_configuration_id, dojo_product.tid, dojo_product.prod_numeric_grade, dojo_product.business_criticality, dojo_product.platform, dojo_product.lifecycle, dojo_product.origin, dojo_product.user_records, dojo_product.revenue, dojo_product.external_audience, dojo_product.internet_accessible, dojo_product.enable_product_tag_inheritance, dojo_product.enable_simple_risk_acceptance, dojo_product.enable_full_risk_acceptance, dojo_product.disable_sla_breach_notifications, dojo_product.async_updating, auth_user.id, auth_user.password, auth_user.last_login, auth_user.is_superuser, auth_user.username, auth_user.first_name, auth_user.last_name, auth_user.email, auth_user.is_staff, auth_user.is_active, auth_user.date_joined, t3.id, t3.password, t3.last_login, t3.is_superuser, t3.username, t3.first_name, t3.last_name, t3.email, t3.is_staff, t3.is_active, t3.date_joined
Inner Unique: true
Join Filter: (dojo_product.technical_contact_id = t3.id)
Rows Removed by Join Filter: 2594
-> Nested Loop Left Join (cost=0.27..707.74 rows=387 width=359) (actual time=0.190..0.458 rows=25 loops=1)
Output: dojo_product.id, dojo_product.name, dojo_product.description, dojo_product.product_manager_id, dojo_product.technical_contact_id, dojo_product.team_manager_id, dojo_product.created, dojo_product.prod_type_id, dojo_product.updated, dojo_product.sla_configuration_id, dojo_product.tid, dojo_product.prod_numeric_grade, dojo_product.business_criticality, dojo_product.platform, dojo_product.lifecycle, dojo_product.origin, dojo_product.user_records, dojo_product.revenue, dojo_product.external_audience, dojo_product.internet_accessible, dojo_product.enable_product_tag_inheritance, dojo_product.enable_simple_risk_acceptance, dojo_product.enable_full_risk_acceptance, dojo_product.disable_sla_breach_notifications, dojo_product.async_updating, auth_user.id, auth_user.password, auth_user.last_login, auth_user.is_superuser, auth_user.username, auth_user.first_name, auth_user.last_name, auth_user.email, auth_user.is_staff, auth_user.is_active, auth_user.date_joined
Inner Unique: true
Join Filter: (dojo_product.product_manager_id = auth_user.id)
Rows Removed by Join Filter: 2594
-> Index Scan using dojo_product_name_key on public.dojo_product (cost=0.27..82.27 rows=387 width=203) (actual time=0.139..0.159 rows=25 loops=1)
Output: dojo_product.id, dojo_product.name, dojo_product.description, dojo_product.created, dojo_product.updated, dojo_product.tid, dojo_product.prod_numeric_grade, dojo_product.business_criticality, dojo_product.platform, dojo_product.lifecycle, dojo_product.origin, dojo_product.user_records, dojo_product.revenue, dojo_product.external_audience, dojo_product.internet_accessible, dojo_product.prod_type_id, dojo_product.product_manager_id, dojo_product.team_manager_id, dojo_product.technical_contact_id, dojo_product.enable_full_risk_acceptance, dojo_product.enable_simple_risk_acceptance, dojo_product.sla_configuration_id, dojo_product.disable_sla_breach_notifications, dojo_product.enable_product_tag_inheritance, dojo_product.async_updating
-> Materialize (cost=0.00..4.61 rows=107 width=156) (actual time=0.002..0.006 rows=104 loops=25)
Output: auth_user.id, auth_user.password, auth_user.last_login, auth_user.is_superuser, auth_user.username, auth_user.first_name, auth_user.last_name, auth_user.email, auth_user.is_staff, auth_user.is_active, auth_user.date_joined
-> Seq Scan on public.auth_user (cost=0.00..4.07 rows=107 width=156) (actual time=0.020..0.030 rows=107 loops=1)
Output: auth_user.id, auth_user.password, auth_user.last_login, auth_user.is_superuser, auth_user.username, auth_user.first_name, auth_user.last_name, auth_user.email, auth_user.is_staff, auth_user.is_active, auth_user.date_joined
-> Materialize (cost=0.00..4.61 rows=107 width=156) (actual time=0.000..0.011 rows=104 loops=25)
Output: t3.id, t3.password, t3.last_login, t3.is_superuser, t3.username, t3.first_name, t3.last_name, t3.email, t3.is_staff, t3.is_active, t3.date_joined
-> Seq Scan on public.auth_user t3 (cost=0.00..4.07 rows=107 width=156) (actual time=0.004..0.009 rows=107 loops=1)
Output: t3.id, t3.password, t3.last_login, t3.is_superuser, t3.username, t3.first_name, t3.last_name, t3.email, t3.is_staff, t3.is_active, t3.date_joined
-> Materialize (cost=0.00..4.61 rows=107 width=156) (actual time=0.001..0.013 rows=107 loops=25)
Output: t4.id, t4.password, t4.last_login, t4.is_superuser, t4.username, t4.first_name, t4.last_name, t4.email, t4.is_staff, t4.is_active, t4.date_joined
-> Seq Scan on public.auth_user t4 (cost=0.00..4.07 rows=107 width=156) (actual time=0.005..0.014 rows=107 loops=1)
Output: t4.id, t4.password, t4.last_login, t4.is_superuser, t4.username, t4.first_name, t4.last_name, t4.email, t4.is_staff, t4.is_active, t4.date_joined
SubPlan 1
-> Limit (cost=0.99..299.83 rows=1 width=12) (actual time=2.883..2.883 rows=0 loops=25)
Output: (count(*)), u2.product_id
-> GroupAggregate (cost=0.99..299.83 rows=1 width=12) (actual time=2.882..2.882 rows=0 loops=25)
Output: count(*), u2.product_id
-> Nested Loop (cost=0.99..297.06 rows=1103 width=4) (actual time=0.265..2.707 rows=6562 loops=25)
Output: u2.product_id
-> Nested Loop (cost=0.57..181.62 rows=29 width=8) (actual time=0.136..1.072 rows=78 loops=25)
Output: u1.id, u2.product_id
-> Index Scan using dojo_engagement_product_id_c46b1b11 on public.dojo_engagement u2 (cost=0.28..49.77 rows=15 width=8) (actual time=0.084..0.354 rows=75 loops=25)
Output: u2.id, u2.name, u2.description, u2.version, u2.first_contacted, u2.target_start, u2.target_end, u2.reason, u2.updated, u2.created, u2.active, u2.tracker, u2.test_strategy, u2.threat_model, u2.api_test, u2.pen_test, u2.check_list, u2.status, u2.progress, u2.tmodel_path, u2.done_testing, u2.engagement_type, u2.build_id, u2.commit_hash, u2.branch_tag, u2.source_code_management_uri, u2.deduplication_on_engagement, u2.build_server_id, u2.lead_id, u2.orchestration_engine_id, u2.preset_id, u2.product_id, u2.report_type_id, u2.requester_id, u2.source_code_management_server_id
Index Cond: (u2.product_id = dojo_product.id)
-> Index Scan using dojo_test_engagement_id_46ff752d on public.dojo_test u1 (cost=0.29..8.77 rows=2 width=8) (actual time=0.009..0.009 rows=1 loops=1874)
Output: u1.id, u1.engagement_id
Index Cond: (u1.engagement_id = u2.id)
-> Index Only Scan using dojo_findin_test_id_bfc47c_idx on public.dojo_finding u0 (cost=0.43..2.91 rows=107 width=4) (actual time=0.013..0.017 rows=84 loops=1942)
Output: u0.test_id, u0.active, u0.verified
Index Cond: ((u0.test_id = u1.id) AND (u0.active = true))
Heap Fetches: 0
SubPlan 2
-> Limit (cost=0.28..4.63 rows=1 width=12) (actual time=0.039..0.039 rows=1 loops=25)
Output: (count(*)), u0_1.product_id
-> GroupAggregate (cost=0.28..4.63 rows=1 width=12) (actual time=0.039..0.039 rows=1 loops=25)
Output: count(*), u0_1.product_id
-> Index Only Scan using dojo_engage_product_16b039_idx on public.dojo_engagement u0_1 (cost=0.28..4.58 rows=15 width=4) (actual time=0.031..0.036 rows=75 loops=25)
Output: u0_1.product_id, u0_1.active
Index Cond: ((u0_1.product_id = dojo_product.id) AND (u0_1.active = true))
Heap Fetches: 0
SubPlan 3
-> Limit (cost=0.28..4.31 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=25)
Output: (count(*)), u0_2.product_id
-> GroupAggregate (cost=0.28..4.31 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=25)
Output: count(*), u0_2.product_id
-> Index Only Scan using dojo_engage_product_16b039_idx on public.dojo_engagement u0_2 (cost=0.28..4.30 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=25)
Output: u0_2.product_id, u0_2.active
Index Cond: ((u0_2.product_id = dojo_product.id) AND (u0_2.active = false))
Heap Fetches: 0
SubPlan 4
-> Limit (cost=49.85..49.85 rows=1 width=4) (actual time=0.082..0.082 rows=1 loops=25)
Output: u0_3.target_start
-> Sort (cost=49.85..49.89 rows=15 width=4) (actual time=0.074..0.074 rows=1 loops=25)
Output: u0_3.target_start
Sort Key: u0_3.target_start DESC
Sort Method: top-N heapsort Memory: 25kB
-> Index Scan using dojo_engagement_product_id_c46b1b11 on public.dojo_engagement u0_3 (cost=0.28..49.77 rows=15 width=4) (actual time=0.002..0.012 rows=75 loops=25)
Output: u0_3.target_start
Index Cond: (u0_3.product_id = dojo_product.id)
SubPlan 5
-> Limit (cost=0.99..299.83 rows=1 width=12) (actual time=0.953..0.953 rows=0 loops=25)
Output: (count(*)), u2_1.product_id
-> GroupAggregate (cost=0.99..299.83 rows=1 width=12) (actual time=0.953..0.953 rows=0 loops=25)
Output: count(*), u2_1.product_id
-> Nested Loop (cost=0.99..297.06 rows=1103 width=4) (actual time=0.111..0.788 rows=6562 loops=25)
Output: u2_1.product_id
-> Nested Loop (cost=0.57..181.62 rows=29 width=8) (actual time=0.055..0.133 rows=78 loops=25)
Output: u1_1.id, u2_1.product_id
-> Index Scan using dojo_engagement_product_id_c46b1b11 on public.dojo_engagement u2_1 (cost=0.28..49.77 rows=15 width=8) (actual time=0.053..0.061 rows=75 loops=25)
Output: u2_1.id, u2_1.name, u2_1.description, u2_1.version, u2_1.first_contacted, u2_1.target_start, u2_1.target_end, u2_1.reason, u2_1.updated, u2_1.created, u2_1.active, u2_1.tracker, u2_1.test_strategy, u2_1.threat_model, u2_1.api_test, u2_1.pen_test, u2_1.check_list, u2_1.status, u2_1.progress, u2_1.tmodel_path, u2_1.done_testing, u2_1.engagement_type, u2_1.build_id, u2_1.commit_hash, u2_1.branch_tag, u2_1.source_code_management_uri, u2_1.deduplication_on_engagement, u2_1.build_server_id, u2_1.lead_id, u2_1.orchestration_engine_id, u2_1.preset_id, u2_1.product_id, u2_1.report_type_id, u2_1.requester_id, u2_1.source_code_management_server_id
Index Cond: (u2_1.product_id = dojo_product.id)
-> Index Scan using dojo_test_engagement_id_46ff752d on public.dojo_test u1_1 (cost=0.29..8.77 rows=2 width=8) (actual time=0.001..0.001 rows=1 loops=1874)
Output: u1_1.id, u1_1.engagement_id
Index Cond: (u1_1.engagement_id = u2_1.id)
-> Index Only Scan using dojo_findin_test_id_bfc47c_idx on public.dojo_finding u0_4 (cost=0.43..2.91 rows=107 width=4) (actual time=0.001..0.005 rows=84 loops=1942)
Output: u0_4.test_id, u0_4.active, u0_4.verified
Index Cond: ((u0_4.test_id = u1_1.id) AND (u0_4.active = true))
Heap Fetches: 0
SubPlan 6
-> Limit (cost=0.99..204.93 rows=1 width=12) (actual time=0.136..0.136 rows=0 loops=25)
Output: (count(*)), u2_2.product_id
-> GroupAggregate (cost=0.99..204.93 rows=1 width=12) (actual time=0.136..0.136 rows=0 loops=25)
Output: count(*), u2_2.product_id
-> Nested Loop (cost=0.99..204.91 rows=1 width=4) (actual time=0.135..0.135 rows=0 loops=25)
Output: u2_2.product_id
-> Nested Loop (cost=0.57..181.62 rows=29 width=8) (actual time=0.004..0.076 rows=78 loops=25)
Output: u1_2.id, u2_2.product_id
-> Index Scan using dojo_engagement_product_id_c46b1b11 on public.dojo_engagement u2_2 (cost=0.28..49.77 rows=15 width=8) (actual time=0.002..0.009 rows=75 loops=25)
Output: u2_2.id, u2_2.name, u2_2.description, u2_2.version, u2_2.first_contacted, u2_2.target_start, u2_2.target_end, u2_2.reason, u2_2.updated, u2_2.created, u2_2.active, u2_2.tracker, u2_2.test_strategy, u2_2.threat_model, u2_2.api_test, u2_2.pen_test, u2_2.check_list, u2_2.status, u2_2.progress, u2_2.tmodel_path, u2_2.done_testing, u2_2.engagement_type, u2_2.build_id, u2_2.commit_hash, u2_2.branch_tag, u2_2.source_code_management_uri, u2_2.deduplication_on_engagement, u2_2.build_server_id, u2_2.lead_id, u2_2.orchestration_engine_id, u2_2.preset_id, u2_2.product_id, u2_2.report_type_id, u2_2.requester_id, u2_2.source_code_management_server_id
Index Cond: (u2_2.product_id = dojo_product.id)
-> Index Scan using dojo_test_engagement_id_46ff752d on public.dojo_test u1_2 (cost=0.29..8.77 rows=2 width=8) (actual time=0.001..0.001 rows=1 loops=1874)
Output: u1_2.id, u1_2.engagement_id
Index Cond: (u1_2.engagement_id = u2_2.id)
-> Index Only Scan using dojo_findin_test_id_bfc47c_idx on public.dojo_finding u0_5 (cost=0.43..0.79 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1942)
Output: u0_5.test_id, u0_5.active, u0_5.verified
Index Cond: ((u0_5.test_id = u1_2.id) AND (u0_5.active = true) AND (u0_5.verified = true))
Heap Fetches: 0
SubPlan 7
-> Limit (cost=0.86..284.12 rows=1 width=12) (actual time=5.688..5.688 rows=1 loops=25)
Output: (count(*)), u2_3.product_id
-> GroupAggregate (cost=0.86..284.12 rows=1 width=12) (actual time=5.688..5.688 rows=1 loops=25)
Output: count(*), u2_3.product_id
-> Nested Loop (cost=0.86..282.83 rows=511 width=4) (actual time=0.472..5.683 rows=54 loops=25)
Output: u2_3.product_id
-> Nested Loop (cost=0.57..181.62 rows=29 width=8) (actual time=0.066..0.179 rows=78 loops=25)
Output: u1_3.id, u2_3.product_id
-> Index Scan using dojo_engagement_product_id_c46b1b11 on public.dojo_engagement u2_3 (cost=0.28..49.77 rows=15 width=8) (actual time=0.001..0.014 rows=75 loops=25)
Output: u2_3.id, u2_3.name, u2_3.description, u2_3.version, u2_3.first_contacted, u2_3.target_start, u2_3.target_end, u2_3.reason, u2_3.updated, u2_3.created, u2_3.active, u2_3.tracker, u2_3.test_strategy, u2_3.threat_model, u2_3.api_test, u2_3.pen_test, u2_3.check_list, u2_3.status, u2_3.progress, u2_3.tmodel_path, u2_3.done_testing, u2_3.engagement_type, u2_3.build_id, u2_3.commit_hash, u2_3.branch_tag, u2_3.source_code_management_uri, u2_3.deduplication_on_engagement, u2_3.build_server_id, u2_3.lead_id, u2_3.orchestration_engine_id, u2_3.preset_id, u2_3.product_id, u2_3.report_type_id, u2_3.requester_id, u2_3.source_code_management_server_id
Index Cond: (u2_3.product_id = dojo_product.id)
-> Index Scan using dojo_test_engagement_id_46ff752d on public.dojo_test u1_3 (cost=0.29..8.77 rows=2 width=8) (actual time=0.002..0.002 rows=1 loops=1874)
Output: u1_3.id, u1_3.engagement_id
Index Cond: (u1_3.engagement_id = u2_3.id)
-> Index Scan using dojo_test_import_test_id_e8dc3f37 on public.dojo_test_import u0_6 (cost=0.29..3.07 rows=42 width=4) (actual time=0.049..0.071 rows=1 loops=1942)
Output: u0_6.id, u0_6.created, u0_6.modified, u0_6.import_settings, u0_6.version, u0_6.type, u0_6.test_id, u0_6.branch_tag, u0_6.build_id, u0_6.commit_hash
Index Cond: (u0_6.test_id = u1_3.id)
Filter: ((u0_6.type)::text = 'reimport'::text)
Rows Removed by Filter: 1
Planning Time: 22.318 ms
Execution Time: 250.439 ms
If you need something more from me - I am open to talk
hi @DenysMoskalenko ,
whats the reason for the total_reimport_count sub-query in the products select query (https://github.com/DefectDojo/django-DefectDojo/pull/12603#issuecomment-2973238685)? From the comparison this wasn't needed in this query before the optimization.
Due to many different sources importing findings into our instance the dojo_test_import table has over 23m rows of type reimport.
This might be the reason why the products page is loading that long in our case.
Hi @gietschess ,
Following up on the discussion in https://github.com/DefectDojo/django-DefectDojo/issues/12575: we’ve applied the same counter-optimisation technique to the products query. Although performance on that query wasn’t a major pain point for us, the change still produced a noticeable improvement in our dataset.