github-featured / comet-ml--opik

发布

0 发布 8,340 Git 标签

RSS 订阅源
  • 1.10.6-4108 04c1e38ee2
    比较

    [OPIK-3846] [BE] Extract target_projects CTE to separate query (#5054)

    frostbyte_neo 发布于 2026-02-05 08:49:51 +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

    • [OPIK-3846] [BE] Optimize SELECT_DATASET_ITEM_VERSIONS_WITH_EXPERIMENT_ITEMS_COUNT query

    Add project_id filtering to feedback_scores tables to leverage ClickHouse index structure.

    • [OPIK-3846] [BE] Optimize SELECT_DATASET_ITEM_VERSIONS_WITH_EXPERIMENT_ITEMS query with CTEs

    Add CTEs for trace data pre-computation to reduce I/O on large datasets:

    • experiment_items_trace_scope: Pre-compute distinct trace_ids
    • target_projects: Pre-compute project_ids for index usage
    • trace_data: Pre-compute trace data with deduplication
    • [OPIK-3846] [BE] Extract target_projects CTE to separate query

    Applies the optimization to all three queries:

    • SELECT_DATASET_ITEM_VERSIONS_WITH_EXPERIMENT_ITEMS
    • SELECT_DATASET_ITEM_VERSIONS_WITH_EXPERIMENT_ITEMS_COUNT
    • SELECT_DATASET_ITEM_VERSIONS_WITH_EXPERIMENT_ITEMS_STATS

    By pre-fetching project_ids in a separate query (getTargetProjectIds),
    we reduce the number of traces table scans in the main queries.

    All three methods now call getTargetProjectIds first and bind the
    result as target_project_ids parameter.

    • Update 000055_add_duration_column_to_traces_and_spans.sql

    • Update 000055_add_duration_column_to_traces_and_spans.sql

    • [OPIK-3846] [BE] Address PR review comments for query optimization

    • Add SETTINGS log_comment to SELECT_TARGET_PROJECTS query for observability
    • Make target_project_ids binding conditional using <if(has_target_projects)> template
    • Update all 13 SQL occurrences to use conditional template pattern
    • Update 3 Java binding locations with conditional logic

    • Address PR review feedback

    • Fix filters

    下载附件