Fix creating dependency list segment creation queries
What does this MR do and why?
PostgreSQL query planner chooses the wrong index when all the columns are selected from the sbom_occurrences
table for batch queries. When we select only the indexed columns, the correct index is used. This change will cause firing an extra query for each batch but the extra one will query records by primary key.
Related to Segmented export parts can timeout due to plan ... (#473262 - closed).
Database review
Existing query
SELECT
sbom_occurrences.*
FROM
sbom_occurrences
WHERE
traversal_ids >= '{4249178}' AND
traversal_ids < '{4249179}' AND
(
sbom_occurrences.traversal_ids,
sbom_occurrences.id
) >= (
'{4249178,5030580,87255895}',
5405902769
) AND
(
sbom_occurrences.traversal_ids,
sbom_occurrences.id
) <= (
'{4249178,5087837,9692938,9692949}',
3801323122
) AND
sbom_occurrences.archived = false
ORDER BY
sbom_occurrences.traversal_ids ASC,
sbom_occurrences.id ASC
LIMIT 1000;
Limit (cost=5.85..291.79 rows=1000 width=355) (actual time=94.368..94.574 rows=1000 loops=1)
Buffers: shared hit=38944
I/O Timings: read=0.000 write=0.000
-> Incremental Sort (cost=5.85..88551.81 rows=309663 width=355) (actual time=94.365..94.510 rows=1000 loops=1)
Sort Key: sbom_occurrences.traversal_ids, sbom_occurrences.id
Buffers: shared hit=38944
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_unarchived_sbom_occurrences_for_aggregations on public.sbom_occurrences (cost=0.69..72740.94 rows=309663 width=355) (actual time=16.999..78.357 rows=20832 loops=1)
Index Cond: ((sbom_occurrences.traversal_ids >= '{4249178}'::bigint[]) AND (sbom_occurrences.traversal_ids < '{4249179}'::bigint[]) AND (sbom_occurrences.traversal_ids >= '{4249178,5030580,87255895}'::bigint[]) AND (sbom_occurrences.traversal_ids <= '{4249178,5087837,9692938,9692949}'::bigint[]))
Filter: ((ROW(sbom_occurrences.traversal_ids, sbom_occurrences.id) >= ROW('{4249178,5030580,87255895}'::bigint[], '5405902769'::bigint)) AND (ROW(sbom_occurrences.traversal_ids, sbom_occurrences.id) <= ROW('{4249178,5087837,9692938,9692949}'::bigint[], '3801323122'::bigint)))
Rows Removed by Filter: 17701
Buffers: shared hit=38933
I/O Timings: read=0.000 write=0.000
New query
SELECT
sbom_occurrences.id,
sbom_occurrences.traversal_ids
FROM
sbom_occurrences
WHERE
traversal_ids >= '{4249178}' AND
traversal_ids < '{4249179}' AND
(
sbom_occurrences.traversal_ids,
sbom_occurrences.id
) >= (
'{4249178,5030580,87255895}',
5405902769
) AND
(
sbom_occurrences.traversal_ids,
sbom_occurrences.id
) <= (
'{4249178,5087837,9692938,9692949}',
3801323122
) AND
sbom_occurrences.archived = false
ORDER BY
sbom_occurrences.traversal_ids ASC,
sbom_occurrences.id ASC
LIMIT 1000;
Limit (cost=0.56..49.92 rows=1000 width=54) (actual time=0.070..0.751 rows=1000 loops=1)
Buffers: shared hit=144
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_sbom_occurrences_on_traversal_ids_and_id on public.sbom_occurrences (cost=0.56..15283.55 rows=309663 width=54) (actual time=0.069..0.686 rows=1000 loops=1)
Index Cond: ((sbom_occurrences.traversal_ids >= '{4249178}'::bigint[]) AND (sbom_occurrences.traversal_ids < '{4249179}'::bigint[]) AND (ROW(sbom_occurrences.traversal_ids, sbom_occurrences.id) >= ROW('{4249178,5030580,87255895}'::bigint[], '5405902769'::bigint)) AND (ROW(sbom_occurrences.traversal_ids, sbom_occurrences.id) <= ROW('{4249178,5087837,9692938,9692949}'::bigint[], '3801323122'::bigint)))
Heap Fetches: 73
Buffers: shared hit=144
I/O Timings: read=0.000 write=0.000