-
[OPIK-7035] [BE] Thread query optimizations: listing page-pushdown + close/feedback thread-id resolution (#7178)
frostbyte_neo 发布于
2026-06-23 10:48:02 +00:00 - [OPIK-7035] [BE] Two-phase page resolution for threads listing
The threads-listing query (ThreadDAO.SELECT_TRACES_THREADS_BY_PROJECT_IDS,
GET /v1/private/traces/threads) aggregated every thread in the project from the
wide traces + spans tables and applied LIMIT/OFFSET only at the end. On a
~1M-trace project this read 2.74 GiB / 946 MiB / 4.3s to return one page
(measured in prod, OPIK-7035) - same wide-scan-before-limit shape #7158 fixed.Apply the page_ids pattern already used by TraceDAO/SpanDAO, adapted for the
thread aggregation:- page_thread_ids - one narrow traces scan computes per-thread sort keys
(min start_time, max end_time, max last_updated_at) WITH the filter applied,
LEFT JOINs the small trace_threads for the last_updated_at coalesce, and
pushes LIMIT/OFFSET early. No wide columns. This single scan replaces the
traces_final_ids prefilter (the two are now mutually exclusive in find(),
so the project traces are scanned once, not twice -> no CTE-re-evaluation
compounding). - traces_final / spans_deduped / trace_threads_final and the feedback/comments
aggregates are all bound to that page -> wide input/output and spans read
for ~page-size threads only.
Gated to the common default-sort listing case; any filter/sort that needs the
spans/feedback/annotation joins, the uuid time-range path, the single-thread
pushdown, or streaming falls back to the unchanged query. Deterministic page
order via a thread_id tiebreaker.Output-equivalent: verified on prod that the page (set + order + full-row
fingerprint over all columns) is identical to the current query across page 1,
a deep page, and the source filter. Cost on a ~1M-trace project:
memory 946 MiB -> 167 MiB (5.7x), bytes read 2.74 GiB -> 1.02 GiB (2.7x),
~1.5x faster.FindTraceThreadsResourceTest: 466/466 pass (pushdown + fallback paths).
Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- [OPIK-7035] [BE] Batch thread-id resolution on close + feedback paths
Two thread-model-id resolution fixes that avoid full-project trace_threads scans:
-
createMissingThreads (close path): findThreadsByProject was filtered by id
alone, leaving thread_id (the 3rd PK column) unbound, so the
(workspace_id, project_id, thread_id, id) primary key could not seek - it
scanned the whole project's trace_threads (~750K rows in prod to find a
handful, p99 161ms / max 593ms). Also pass thread_id; thread_model_id maps 1:1
to thread_id so output is unchanged. Prod: 278K -> 82K rows, 39ms -> 13ms for a
single thread (~14x fewer rows on the worst cases). -
scoreBatchOfThreads (PUT /threads/feedback-scores): resolved thread-model-ids
one-per-thread in a flatMap; now resolves all in a single bulk
getOrCreateThreadIds (one findByProjectIdAndThreadIds) via the same batch path
the close flow already uses. Output unchanged.
FindTraceThreads* + ThreadFeedbackScoresCreation + TraceThreadManualOpenClose tests pass.
Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- [OPIK-7035] [BE] Test threads-listing pagination (offset > 0) for the page-pushdown
The page-pushdown moves OFFSET into the page_thread_ids CTE and drops it from the
outer query (so it is not applied twice). Existing listing tests only covered
page 1 (offset 0), where that relocation is a no-op. Add a default-sort test that
pages through (size 3 over 7 threads -> offsets 0/3/6) and asserts the pages
reconstruct the full ordered list exactly, catching double/missing offset,
overlaps, and gaps.Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
- [OPIK-7035] [BE] Address review: graceful empty handling + assert full page payload
- getOrCreateThreadIds: drop @NonNull on threadIds and use CollectionUtils.isEmpty
to handle null/empty gracefully (per review). - Pagination test: assert the full page payload via TraceAssertions.assertThreads
instead of only thread ids, for resilience (per review).
Co-Authored-By: Claude Opus 4.8 (1M context) noreply@anthropic.com
Co-authored-by: Claude Opus 4.8 (1M context) noreply@anthropic.com
下载附件