Product list page query becomes extremely slow

[DarkR0ast]

The Product list view becomes very slow when loading products with findings and location-related annotations enabled.

In my DefectDojo instance, opening the Product/Asset list page results in a database query taking around 66 seconds.

EXPLAIN ANALYZE shows that expensive annotations are being evaluated before pagination. The queryset performs correlated subqueries for finding counts, which are executed repeatedly for intermediate rows created by location joins.

Observed query details:

• ~110 products
• ~60,000 dojo_locationproductreference records
• ~11,700 findings
• ~600 tests

Relevant query plan observations:

• Execution time: ~66 seconds
• dojo_test sequential scan loops: 60,168
• Finding count subqueries executed: 60,168 times
• Shared buffer hits: ~35 million

No application error is shown; the issue is excessive query execution time.

Steps to reproduce
Steps to reproduce the behavior:

1. Populate DefectDojo with a dataset containing a significant number of products, findings, tests, and locations.
2. Enable product location functionality (V3_FEATURE_LOCATIONS).
3. Go to Products.
4. Load the product list page.
5. Observe that the page takes a long time to load.
6. Capture the SQL query with EXPLAIN (ANALYZE, BUFFERS) and observe repeated finding count subquery execution.

Expected behavior
The Product list page should load within a reasonable time even with larger datasets.

Expensive calculations such as finding counts and engagement counts should not be executed thousands of times for intermediate rows when only 25 products are displayed per page.

Deployment method (select with an X)

• Docker Compose
• Kubernetes
• GoDojo

Environment information

• Docker Compose version: v2.35.1
• DefectDojo version: v. 3.1.300
• Database: PostgreSQL 18.4

Logs
No application error logs are generated. The issue is database query performance.