running perf tests see some sql queries 'spilling to disk'

[rh-jfuller]

Running standard perf tests I see a few queries spilling over to disk - this is due to our postgres work_mem=4MB which is quite low.

| 32429 | postgres | postgres | 185.61.86.229 | 0:14:49.989925 | IO              | BuffileRead | SELECT "qualified_purl"."id", "qualified_purl"."versioned_purl_id", "qualified_purl"."qualifiers", "qualified_purl"."purl" FROM "qualified_purl" WHERE (TRUE OR TRUE) AND "qualified_purl"."id" IN (SELECT DISTINCT "sbom_node_purl_ref"."qualified_purl_id" FROM "sbom_node_purl_ref" INNER JOIN "sbom_node" ON "sbom_node_purl_ref"."sbom_id" = "sbom_node"."sbom_id" AND "sbom_node_purl_ref"."node_id" = "sbom_node"."node_id" INNER JOIN "sbom_package_license" ON "sbom_node"."sbom_id" = "sbom_package_lic... |
| 32452 | postgres | postgres | 185.61.86.229 | 0:14:46.497376 | IO              | BuffileRead | SELECT "qualified_purl"."id", "qualified_purl"."versioned_purl_id", "qualified_purl"."qualifiers", "qualified_purl"."purl" FROM "qualified_purl" WHERE (TRUE OR TRUE) AND "qualified_purl"."id" IN (SELECT DISTINCT "sbom_node_purl_ref"."qualified_purl_id" FROM "sbom_node_purl_ref" INNER JOIN "sbom_node" ON "sbom_node_purl_ref"."sbom_id" = "sbom_node"."sbom_id" AND "sbom_node_purl_ref"."node_id" = "sbom_node"."node_id" INNER JOIN "sbom_package_license" ON "sbom_node"."sbom_id" = "sbom_package_lic... |

These kinds of queries should be resolving in ms times ... I suspect there are some indexes that will help

CREATE INDEX CONCURRENTLY tmp_idx_purl_status_vuln_base_purl
ON purl_status (vulnerability_id, base_purl_id);

CREATE INDEX CONCURRENTLY tmp_idx_sbom_node_purl_ref_qualified_sbom
ON sbom_node_purl_ref (qualified_purl_id, sbom_id);

which we can experiment first with before changing on pg config reccs.

[rh-jfuller]

tracked as TC-5200