-
[OPIK-4879] [BE] Optimize slow ClickHouse queries by narrowing feedback_scores scans (#5599)
frostbyte_neo 发布于
2026-03-11 14:01:22 +00:00 - [OPIK-NA] [BE] Add time-range filters to feedback_scores CTE in span stats query
The SELECT_SPANS_STATS query scans all feedback scores for the entire
project regardless of time-range filters, causing queries to read 745 GiB
and take 1500+ seconds. Adding uuid_from_time/uuid_to_time filters to the
feedback_scores_combined_raw CTE aligns it with the existing pattern in
SELECT_BY_PROJECT_ID and allows ClickHouse to prune data significantly.- [OPIK-NA] [BE] Make feedback_scores CTE conditional in span count query
The COUNT_BY_PROJECT_ID query always includes the feedback_scores CTE
chain even when no feedback score filters are active. Wrapping it in a
conditional block prevents CTE parsing overhead and ensures the WITH
clause is only generated when needed. Also adds time-range filters to
the feedback_scores CTE for when it is used.- [OPIK-NA] [BE] Add time-range filters to span_feedback_scores CTE in trace queries
The span_feedback_scores_combined_raw CTE in both SELECT_TRACES_STATS
and SELECT_BY_PROJECT_ID (traces) scans all span feedback scores for the
project without time-range filtering. Adding uuid_from_time/uuid_to_time
filters allows ClickHouse to prune data for time-filtered queries, matching
the pattern already used for trace-level feedback scores in the same queries.- [OPIK-NA] [BE] Pre-filter span_feedback_scores by target_spans in trace find query
The span_feedback_scores_combined_raw CTE in the trace SELECT_BY_PROJECT_ID
query scans all span feedback scores for the project. Adding
entity_id IN (SELECT id FROM target_spans) pre-filters scores to only
spans that belong to traces in the time range, matching the pattern
already used in COUNT_BY_PROJECT_ID for traces.- [OPIK-NA] [BE] Pre-filter span_feedback_scores by spans_data in trace stats query
The span_feedback_scores_combined_raw CTE in SELECT_TRACES_STATS scans
all span feedback scores then JOINs with spans_data downstream. Adding
entity_id IN (SELECT id FROM spans_data) pushes the filter up, allowing
ClickHouse to only scan scores for spans that match the time/project
filters in spans_data.- [OPIK-NA] [BE] Use approximate count in workspace metadata query
The total_spans CTE used count(distinct id) which scans all rows for
large projects (9+ GiB). Since this count is only used to estimate
project data size, uniq() provides sufficient accuracy (~1-2% error)
with significantly less resource usage.- [OPIK-NA] [BE] Add time-range filters to traces_final in thread queries
Thread find and count queries scanned ALL traces with thread_id for
the entire project. When time-range filters are active, adding
uuid_from_time/uuid_to_time bounds to traces_final allows ClickHouse
to prune by the traces primary key, reducing scans significantly.- [OPIK-NA] [BE] Always filter by experiment trace IDs in experiment queries
When has_target_projects was set, traces/spans/feedback_scores were
filtered only by project_id, scanning all data in those projects.
Now we always include the entity_id IN (experiment_items_final) filter
alongside project_id, combining index pruning with exact row filtering.
This narrows scans from entire projects to just experiment-related data.- fix(review): address PR feedback on query optimizations
- TraceDAO: Use either time-range OR entity_id IN prefilter, not both
(target_spans/spans_data already apply time filters) - ExperimentDAO: Revert feedback_scores to original if/else pattern
when has_target_projects (JOIN approach performs better at scale) - ExperimentDAO: Keep traces/spans always-filter pattern for consistency
- SpanDAO: Remove FINAL from feedback_scores/authored_feedback_scores
where ROW_NUMBER() already handles deduplication (matches PR #5537)
下载附件