-
[OPIK-7331] [BE][FE] perf: speed up projects-list stats (invert span-score lookup + 30-day window) (#7684)
frostbyte_neo 发布于
2026-08-04 17:29:01 +00:00 - [OPIK-7331] [BE] perf: avoid full spans scan in trace-stats feedback-scores query
get_trace_stats_feedback_scores (SELECT_FEEDBACK_SCORES_STATS) computed span-level
feedback-score aggregates by scanning all spans for the queried projects and
intersecting them with the small set of span feedback scores. On large projects this
read tens to hundreds of millions of span rows to serve a handful (often zero) of
span-level scores, making it the heaviest single query on the analytics database.Invert the join: read the span feedback scores first (authored_feedback_scores,
index-cheap on workspace_id/project_id/entity_type), then look those specific spans up
via the idx_spans_id minmax index, scoping to the same source/time-matched traces.
Output is unchanged and the spans read is a strict subset of the previous query, so it
can never read more.Measured on production replicas: -60% to -77% rows read, -59% to -88% peak memory, and
roughly 3x faster, with byte-for-byte identical results.Implements OPIK-7331.
- Revision: rename scored_span_ids and add opt-in 30-day window to projects-list stats
- Rename scored_span_traces -> scored_span_ids (it holds span ids, not traces; addresses review feedback).
- Add an optional window_days query param to GET /v1/private/projects/stats. When set, scope every metric
(trace/thread counts, duration, tokens, cost, errors, guardrails, feedback and span-feedback scores) to
[now - N days, now] via uuid_from_time/uuid_to_time on the time-ordered UUIDv7 id; when omitted, the
aggregates stay all-time so the public getProjectStats API keeps its existing semantics. The upper bound
is required because future-dated ids (clock skew and buggy clients) occur in prod and would otherwise
never age out of the window. Prunes by the sort key today and by time partitions once the tables are
partitioned by time. - Frontend: the Projects table requests window_days=30 and shows a "Metrics reflect the last 30 days" caption.
- Revision 2: use from_time/to_time for projects-stats window
Replace the window_days query param on GET /v1/private/projects/stats with from_time/to_time (ISO-8601 Instant), matching getTracesByProject and getSpansByProject. Each bound is applied independently and the DAO no longer reads the wall clock. The narrow-window integration test now exercises both bounds, and the FE marks scoped columns with a (30d) suffix.
- Revision 3: address review — opt-in FE window, uniform span-score bound, pre-1970 guard
- FE: make the rolling window opt-in in the shared stats hook so only the v2 Projects table requests it (v1 ProjectsPage + HomePage stay all-time); move PROJECT_STATS_WINDOW_DAYS into the Projects page.
- BE: scope span feedback scores by their trace's time (drop the redundant span-id bound on span_scores) so they match the trace-based window used by every other metric.
- BE: clamp pre-1970 timestamps to the epoch floor in InstantToUUIDMapper so a negative from_time can't wrap into a UUID that returns all-zero stats.
- Clarify the partition-pruning comment (traces prune; spans stay trace_id-bound).
- Test: assert windowed stats match the all-time result across every column (incl. trace + span feedback scores), with span ids placed outside the window.
- Revision 4: scope deduped span scores by trace time; harden pre-epoch test
- Deduped span-score chains in both stats templates now scope via spans_data (FINAL + trace_id-windowed) instead of an entity_id (span-id) window, matching span_scores/span_fs — fixes the filtered-path undercount (verified on prod: 53k->107k in-window span scores on a workspace with non-time-ordered span ids).
- Assert the pre-1970 clamp against independently-constructed epoch-floor UUIDs so the test can't pass by reusing the implementation.
- Drop the now-redundant SQL comments on the span-score CTEs (the rationale lives in the getStatsByProjectIds comment).
下载附件