Long load times for Software -> Show versions page

[getvictor]

UPDATE: This issue does not include the complete fix for the /software/versions endpoint. Follow up issue at: #35799

Another related fix/optimization that may result in additional speed up: #35805

---

Fleet version:
4.75.0

💥  Actual behavior

Customer reports:

447.5k software version, takes about 45 seconds to load
29k vulnerabilities, also takes about 45 seconds to load.

This is a follow up to #32178

UPDATE: After #32178 the vulnerabilities load time is under 5 seconds in customer environment, so this issue will only deal with software versions page.

🛠️ To fix

For vulnerabiliites, the COUNT query can be optimized. From my loadtest:

For versions, the COUNT query can be optimized by dropping joins with CVEs. The main query can be optimized by removing a second sort when not sorting by CVE details.

These are not simple fixes (but not too bad) because of the number of parameters these API endpoints take.

🧑‍💻  Steps to reproduce

1. Use loadtest with large numbers of software and vulnerabilities.

🕯️ More info (optional)

Some ideas for improving versions: main...victor/34677-improve-versions-poc

#28091 will also help here by reducing the numbers of CVEs in the DB.

QA

Results from load test with ~300K software titles and ~100K hosts.
Using script: https://gist.github.com/getvictor/0ef1da8bf1b85a4a8a6d3ee94c254e08

Testing: All teams (global stats)
Running each query 5 times in random order...

Progress: 65/65 - Complete!

=== Performance Test Results: All teams (global stats) ===

Description               Average  Worst    Results
-----------               -------  -----    -------
Page 0, DESC order        441ms    506ms    20 items
Page 0, ASC order         1.099s   1.8s     20 items
Page 1000, DESC order     484ms    641ms    20 items
100 per_page              426ms    450ms    100 items
With CVE scores           467ms    630ms    20 items
Order by name, page 0     7.589s   7.812s   20 items
Order by name, page 1000  9.103s   9.656s   20 items
Vulnerable only           6.098s   6.34s    20 items
Search 'chrome'           14.305s  14.868s  20 items
Known exploit filter      20.253s  21.238s  20 items
Min CVSS score 7.0        33.743s  35.169s  20 items
Max CVSS score 8.0        39.825s  41.83s   20 items
CVSS range 7.0-9.0        42.556s  43.267s  20 items


Testing: Team ID 0
Running each query 5 times in random order...

Progress: 65/65 - Complete!

=== Performance Test Results: Team ID 0 ===

Description               Average  Worst    Results
-----------               -------  -----    -------
Page 0, DESC order        603ms    1.231s   20 items
Page 0, ASC order         988ms    1.217s   20 items
Page 1000, DESC order     548ms    921ms    20 items
100 per_page              459ms    522ms    100 items
With CVE scores           556ms    925ms    20 items
Order by name, page 0     7.605s   8.069s   20 items
Order by name, page 1000  8.889s   9.411s   20 items
Vulnerable only           6.242s   6.421s   20 items
Search 'chrome'           13.745s  14.154s  20 items
Known exploit filter      19.765s  21.414s  20 items
Min CVSS score 7.0        32.409s  33.095s  20 items
Max CVSS score 8.0        39.428s  41.161s  20 items
CVSS range 7.0-9.0        42.892s  43.778s  20 items

[sharon-fdm]

@getvictor, I assume this was not yet reproduced by us so added the label.

[getvictor]

@sharon-fdm I don't think we need a label since this issue is just the 2nd part of #32178