-
[OPIK-3846] [BE] Optimize experiment items stats query with IN subqueries (#5033)
frostbyte_neo 发布于
2026-02-04 10:43:29 +00:00 - [OPIK-3846] [BE] Optimize experiment items stats query
Replace JOINs with IN subqueries in SELECT_DATASET_ITEM_VERSIONS_WITH_EXPERIMENT_ITEMS_STATS
for better ClickHouse query performance:- Add early trace_data CTE to resolve trace IDs and project_ids upfront
- Replace INNER JOIN experiments_resolved with IN subquery for experiment_items_scope
- Filter feedback_scores and spans by project_id IN (SELECT project_id FROM trace_data)
- Use trace_id IN subquery instead of full workspace scan on traces table
Performance improvements (benchmark with cache disabled):
- 48% faster average execution (219ms vs 420ms)
- 96% more consistent (21ms vs 479ms std dev)
- 83% better worst-case (271ms vs 1567ms max)
- 50% fewer table scans on traces and spans tables
- 38% fewer ReadFromMergeTree operations
- 42% fewer JoiningTransform operations
Also fixes test by setting correct projectName on feedback scores.
- [OPIK-3846] [BE] Optimize experiment items stats query - add duration column
- Add experiments_resolved CTE for early experiment resolution
- Add target_projects CTE for project_id index filtering
- Add experiment_items_trace_scope CTE for trace_id filtering
- Filter feedback_scores and spans by project_id and entity_id/trace_id
- Add duration materialized column to traces and spans tables
- Fix feedback_scores_percentiles WHERE clause for correct filtering
Performance improvement on 1M items:
- 3.72x faster (2.91s vs 10.92s)
- 56% fewer rows read (30.84M vs 69.49M)
- 57% less data read (4.58 GiB vs 10.74 GiB)
-
Fix issue
-
Update apps/opik-backend/src/main/resources/liquibase/db-app-analytics/migrations/000055_add_duration_column_to_traces_and_spans.sql
Co-authored-by: baz-reviewer[bot] <174234987+baz-reviewer[bot]@users.noreply.github.com>
- Update apps/opik-backend/src/main/resources/liquibase/db-app-analytics/migrations/000055_add_duration_column_to_traces_and_spans.sql
Co-authored-by: baz-reviewer[bot] <174234987+baz-reviewer[bot]@users.noreply.github.com>
Co-authored-by: baz-reviewer[bot] <174234987+baz-reviewer[bot]@users.noreply.github.com>
下载附件