JimFuller-RedHat
commented
1 month ago query plan looks quite chunky ... the grouping causes the sorting, which hits disk especially for highly used packages ... making things sluggish ... when we do https://github.com/trustification/trustify/issues/771 this query plan hopefully will get significantly simpler (and better performing).
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| GroupAggregate (cost=103110.40..106505.59 rows=113173 width=737) (actual time=2873.068..3005.622 rows=62887 loops=1) |
| Group Key: context_cpe.id, sbom.sbom_id, sbom_node.node_id, purl_status.advisory_id, cpe.id |
| -> Sort (cost=103110.40..103393.34 rows=113173 width=716) (actual time=2873.026..2946.514 rows=302432 loops=1) |
| Sort Key: context_cpe.id, sbom.sbom_id, sbom_node.node_id, purl_status.advisory_id, cpe.id, status.slug |
| Sort Method: external merge Disk: 207976kB |
| -> Hash Join (cost=44664.14..51466.67 rows=113173 width=716) (actual time=1476.102..2094.196 rows=302432 loops=1) |
| Hash Cond: (sbom_package_purl_ref.sbom_id = sbom.sbom_id) |
| -> Nested Loop (cost=689.63..6198.17 rows=9272 width=98) (actual time=5.225..190.874 rows=8416 loops=1) |
| -> Nested Loop (cost=689.20..3774.40 rows=2447 width=98) (actual time=5.207..168.819 rows=3149 loops=1) |
| -> Hash Join (cost=688.78..2886.05 rows=1672 width=98) (actual time=5.190..158.664 rows=1578 loops=1) |
| Hash Cond: (purl_status.version_range_id = version_range.id) |
| Join Filter: version_matches((versioned_purl.version)::text, version_range.*) |
| Rows Removed by Join Filter: 6101 |
| -> Nested Loop (cost=32.49..2216.59 rows=5017 width=134) (actual time=0.458..19.209 rows=7679 loops=1) |
| -> Hash Left Join (cost=32.06..1367.71 rows=880 width=130) (actual time=0.449..6.447 rows=880 loops=1) |
| Hash Cond: (purl_status.context_cpe_id = context_cpe.id) |
| -> Hash Join (cost=1.56..1334.89 rows=880 width=91) (actual time=0.306..6.084 rows=880 loops=1) |
| Hash Cond: (purl_status.status_id = status.id) |
| -> Nested Loop (cost=0.43..1329.81 rows=880 width=96) (actual time=0.293..5.888 rows=880 loops=1) |
| -> Seq Scan on purl_status (cost=0.00..496.94 rows=880 width=80) (actual time=0.281..2.098 rows=880 loops=1) |
| Filter: ((vulnerability_id)::text = 'CVE-2023-28867'::text) |
| Rows Removed by Filter: 15755 |
| -> Memoize (cost=0.43..1.04 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=880) |
| Cache Key: purl_status.base_purl_id |
| Cache Mode: logical |
| Hits: 1 Misses: 879 Evictions: 0 Overflows: 0 Memory Usage: 110kB |
| -> Index Only Scan using package_pkey on base_purl (cost=0.42..1.03 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=879) |
| Index Cond: (id = purl_status.base_purl_id) |
| Heap Fetches: 0 |
| -> Hash (cost=1.06..1.06 rows=6 width=27) (actual time=0.008..0.010 rows=6 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on status (cost=0.00..1.06 rows=6 width=27) (actual time=0.006..0.007 rows=6 loops=1) |
| -> Hash (cost=23.00..23.00 rows=600 width=55) (actual time=0.139..0.140 rows=600 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 60kB |
| -> Seq Scan on cpe context_cpe (cost=0.00..23.00 rows=600 width=55) (actual time=0.004..0.075 rows=600 loops=1) |
| -> Index Scan using by_pid_v on versioned_purl (cost=0.42..0.81 rows=15 width=52) (actual time=0.006..0.013 rows=9 loops=880) |
| Index Cond: (base_purl_id = base_purl.id) |
| -> Hash (cost=448.35..448.35 rows=16635 width=144) (actual time=4.589..4.590 rows=16635 loops=1) |
| Buckets: 32768 Batches: 1 Memory Usage: 3107kB |
| -> Seq Scan on version_range (cost=0.00..448.35 rows=16635 width=144) (actual time=0.008..3.157 rows=16635 loops=1) |
| -> Index Scan using by_pvid on qualified_purl (cost=0.42..0.50 rows=3 width=32) (actual time=0.005..0.006 rows=2 loops=1578) |
| Index Cond: (versioned_purl_id = versioned_purl.id) |
| -> Index Scan using sbom_package_purl_ref_qual_purl_id_idx on sbom_package_purl_ref (cost=0.43..0.81 rows=18 width=32) (actual time=0.005..0.007 rows=3 loops=3149) |
| Index Cond: (qualified_purl_id = qualified_purl.id) |
| -> Hash (cost=43881.90..43881.90 rows=7409 width=650) (actual time=1469.320..1469.336 rows=250537 loops=1) |
| Buckets: 16384 (originally 8192) Batches: 128 (originally 1) Memory Usage: 15800kB |
| -> Nested Loop (cost=144.06..43881.90 rows=7409 width=650) (actual time=0.467..1286.483 rows=250537 loops=1) |
| -> Nested Loop (cost=143.78..43588.91 rows=7409 width=611) (actual time=0.458..1207.729 rows=250537 loops=1) |
| Join Filter: (sbom.sbom_id = sbom_package.sbom_id) |
| -> Hash Join (cost=143.22..38935.28 rows=7496 width=582) (actual time=0.442..418.925 rows=250537 loops=1) |
| Hash Cond: (sbom_node.sbom_id = sbom.sbom_id) |
| -> Nested Loop (cost=0.57..38624.63 rows=63553 width=164) (actual time=0.029..379.296 rows=250537 loops=1) |
| -> Seq Scan on sbom_package_cpe_ref (cost=0.00..5856.37 rows=250537 width=77) (actual time=0.006..23.059 rows=250537 loops=1) |
| -> Memoize (cost=0.57..1.07 rows=1 width=87) (actual time=0.001..0.001 rows=1 loops=250537) |
| Cache Key: sbom_package_cpe_ref.sbom_id, sbom_package_cpe_ref.node_id |
| Cache Mode: logical |
| Hits: 192408 Misses: 58129 Evictions: 22400 Overflows: 0 Memory Usage: 8193kB |
| -> Index Scan using sbom_node_pkey on sbom_node (cost=0.56..1.06 rows=1 width=87) (actual time=0.004..0.004 rows=1 loops=58129) |
| Index Cond: ((sbom_id = sbom_package_cpe_ref.sbom_id) AND ((node_id)::text = (sbom_package_cpe_ref.node_id)::text)) |
| -> Hash (cost=135.07..135.07 rows=607 width=418) (actual time=0.409..0.410 rows=607 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 279kB |
| -> Seq Scan on sbom (cost=0.00..135.07 rows=607 width=418) (actual time=0.006..0.339 rows=607 loops=1) |
| -> Index Scan using sbom_package_pkey on sbom_package (cost=0.56..0.61 rows=1 width=74) (actual time=0.003..0.003 rows=1 loops=250537) |
| Index Cond: ((sbom_id = sbom_node.sbom_id) AND ((node_id)::text = (sbom_node.node_id)::text)) |
| -> Memoize (cost=0.29..0.30 rows=1 width=55) (actual time=0.000..0.000 rows=1 loops=250537) |
| Cache Key: sbom_package_cpe_ref.cpe_id |
| Cache Mode: logical |
| Hits: 249959 Misses: 578 Evictions: 0 Overflows: 0 Memory Usage: 95kB |
| -> Index Scan using cpe_pkey on cpe (cost=0.28..0.29 rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=578) |
| Index Cond: (id = sbom_package_cpe_ref.cpe_id) |
| Planning Time: 3.758 ms |
| Execution Time: 3043.479 ms
At scale, hitting api/v1/purl/{uuid} blows up
The cleaned up SQL query looks like:
There maybe some relation to /api/v1/vulnerability/{CVE-###-####}
Note - this poor performance was originally 'masked' by subtransactions error.