-
[OPIK-3846] [BE] Optimize experiment queries with target projects extraction pattern (#5114)
frostbyte_neo 发布于
2026-02-09 08:37:17 +00:00 - [OPIK-3846] [BE] Optimize experiment queries with target projects extraction pattern
Apply target projects extraction optimization to three experiment queries:
- FIND_GROUPS_AGGREGATIONS: 47.7% faster (63.94s → 33.41s)
- FIND: 39.2% faster (37.17s → 22.61s)
- FIND_GROUPS: 20.4% faster (2.41s → 1.92s)
Key changes:
- Extract target project IDs in separate query to reduce table scans
- Filter traces, spans, and feedback_scores by project_id instead of IN subqueries
- Add projectDeleted parameter to ExperimentGroupCriteria for filtering deleted projects
- Use conditional SQL templates (has_target_projects) for optimal query paths
- Add comprehensive test coverage for projectDeleted filtering
Performance impact:
- Combined time savings: 45.58 seconds per query set
- Reduced traces scan by ~50%, experiment_items scan by ~25%
- Production impact: 12.7 hours saved daily per 1,000 queries of each type
Technical details:
- Leverage ClickHouse sort key (workspace_id, project_id, id) for efficient filtering
- Use StringTemplate4 conditionals for dynamic query generation
- Maintain backward compatibility with existing query behavior
-
Revision 2: Fix logic bug - use criteria.projectDeleted() instead of hardcoded null
-
Fix order assert
-
Revision 3: Fix test assertions - replace defensive if checks with proper assertions
-
Revision 4: Address ldaugusto PR review comments
- Comment 4: Use DISTINCT instead of FINAL for traces deduplication
- Comment 5: Use pre-calculated prompt_ids column instead of recalculating in WHERE
- Comment 6: Use DISTINCT instead of FINAL for experiment_items deduplication
- Comment 7: Refactor getTargetProjectIdsForExperiments to use context-aware patterns
- Remove workspaceId parameter from method signature
- Use makeFluxContextAware(bindWorkspaceIdToFlux(statement)) pattern
- Update all 3 call sites to remove workspaceId argument
- PR review feedback
下载附件