-
[NA] [BE] Fix high cost metrics calculation (#5965)
frostbyte_neo 发布于
2026-04-01 14:20:23 +00:00 - [NA] [BE] fix: filter spans by trace_id in project metrics queries
Span subqueries in GET_COST, GET_COST_WITH_BREAKDOWN, GET_TOKEN_USAGE,
and GET_TOKEN_USAGE_WITH_BREAKDOWN were not scoping spans to the traces
returned by traces_filtered. Adding AND trace_id IN (SELECT id FROM
traces_filtered) ensures spans are only aggregated for traces that pass
all applied filters (time range, name, metadata, feedback scores, etc.).Benchmarked on production (1.9M spans, 7-day window):
- Granules read: 25,429 → 4,959 (5x reduction)
- GET_TOKEN_USAGE latency: ~2.0s → ~0.6s median (3.5x faster)
- GET_COST latency: ~1.6s → ~0.9s median (1.7x faster)
- [NA] [BE] fix: scope span subqueries to traces_filtered and add created_at index on authored_feedback_scores
- Add AND trace_id IN (SELECT id FROM traces_filtered) to span subqueries in
GET_COST, GET_COST_WITH_BREAKDOWN, GET_TOKEN_USAGE, GET_TOKEN_USAGE_WITH_BREAKDOWN.
Previously filtering by span.id (5th ORDER BY column) caused full-table scans;
the fix reduces granules read from 25,429 to 4,959 (~5x) and query latency by ~2x. - Replace inline dateDiff duration expressions with the MATERIALIZED duration column
in TRACE_FILTERED_PREFIX, SPAN_FILTERED_PREFIX, and GET_AVERAGE_DURATION. - Remove FINAL from feedback_scores and authored_feedback_scores reads in
TRACE_FILTERED_PREFIX, SPAN_FILTERED_PREFIX, and THREAD_FILTERED_PREFIX,
replacing deduplication with ROW_NUMBER() window function which is already applied. - Scope traces_final in THREAD_FILTERED_PREFIX to only traces whose thread_id is
in the selected time window (was previously loading all threads in the project). - Add minmax skip index on authored_feedback_scores.created_at (migration 000076).
- Update and rename 000076_add_minmax_index_authored_feedback_scores_created_at.sql to 000078_add_minmax_index_authored_feedback_scores_created_at.sql
下载附件