-
[OPIK-6735] [BE] perf: drop O(n) ROW_NUMBER CTE in findLatestVersionsByDatasetIds (#6963)
frostbyte_neo 发布于
2026-06-05 13:52:56 +00:00 - [OPIK-6735] [BE] perf: drop O(n) ROW_NUMBER CTE in findLatestVersionsByDatasetIds
The datasets-retrieve path computed version_name via ROW_NUMBER() over every
version of each dataset, materializing an on-disk temp table that exhausted RDS
local storage on large datasets (EON Next outage, 94k versions).The 'latest'-tagged version is always the max-id (UUIDv7) row, so its sequence
number equals the dataset's total version count. Identify the latest row via the
dataset_version_tags PK join and compute version_name with a COUNT(*) subquery
served by the covering index idx_dataset_versions_workspace_id_dataset_id_id —
no window sort, no temp table. Verified result-identical and spill-free on live
EON Next data.Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- perf(datasets): use correlated COUNT subquery for latest version name
Address review on findLatestVersionsByDatasetIds:
- replace derived-table GROUP BY JOIN with a correlated COUNT(*) subquery
served by idx_dataset_versions_workspace_id_dataset_id_id (~2.6x faster
on comet.com prod, one fewer JOIN, no derived-table materialization) - convert the inline rationale to javadoc
- shrink the many-versions regression test to small counts (5/3) and assert
the full latestVersion summary via AssertJ recursive comparison
Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
Co-authored-by: Claude Opus 4.8 (1M context) noreply@anthropic.com
下载附件