-
[OPIK-6311] [BE] perf: slim count path + bypass traces hop in target-projects pre-query (#6756)
frostbyte_neo 发布于
2026-05-19 14:45:29 +00:00 - [OPIK-6311] [BE] perf: slim count path + bypass traces hop in target-projects pre-query
Two complementary fixes for the experiment-comparison page-load latency:
-
DatasetItemVersionDAO.SELECT_DATASET_ITEM_VERSIONS_WITH_EXPERIMENT_ITEMS_COUNT —
add a <if(slim_count)> branch that reads count(DISTINCT stable_dataset_item_id)
directly from experiment_item_aggregates, pre-pruning the dataset_item_versions
FINAL lookup by (workspace_id, dataset_id). The gate fires when push_top_limit
would also fire AND there are no filters/search — i.e. the data side already
takes the fast path, but the count was forced through the heavy CTE chain. -
ExperimentDAO.SELECT_TARGET_PROJECTS — read project_id directly from the
experiments table when populated, fall back to experiment_aggregates.project_id
for experiments without it on the row, and only traverse experiment_items ->
traces for the experiments missing from BOTH (legacy fallback). Removes the
workspace-wide experiment_items + traces scan that was the dominant cost.
Co-Authored-By: Claude Opus 4.7 (1M context) noreply@anthropic.com
- [OPIK-6311] [BE] fix: scope slim count to requested dataset via experiment_aggregates
Address review feedback (Baz HIGH on DatasetItemVersionDAO.java:461): the
<if(slim_count)> branch was only filtering experiment_item_aggregates by
workspace_id, so EIA rows from other datasets in the same workspace would be
counted. EIA has no dataset_id column, so add an IN subquery against
experiment_aggregates (whose sorting key starts with (workspace_id, dataset_id,
id) → no FINAL needed). The optional experiment_ids predicate now lives inside
that subquery and stays consistent with the legacy branch's scoping.Co-Authored-By: Claude Opus 4.7 (1M context) noreply@anthropic.com
- [OPIK-6311] [BE] chore: drop SQL line-comments from SELECT_TARGET_PROJECTS
The CTE names already convey the intent (eia_projects, legacy_scope,
legacy_trace_scope, legacy_projects) and the surrounding Javadoc on the constant
explains the fast/fallback layering. The inline comments were redundant.Co-Authored-By: Claude Opus 4.7 (1M context) noreply@anthropic.com
- [OPIK-6311] [BE] perf: extend slim_count to non-search filtered counts
Drops the no-filter precondition on the slim_count gate. Mirroring
top_dataset_items in the data path, the slim count now renders:- EXPERIMENT_ITEM filters directly on eia
- FEEDBACK_SCORES_AGGREGATED + ..._IS_EMPTY filters on eia.feedback_scores
- DATASET_ITEM filters via a dataset_items_filtered_ids CTE matched against
eia.dataset_item_id IN arrayJoin([id, row_id]) (same stable-id resolution
shape the data path uses)
Gate is now
hasAggregated && !hasRaw && !hasSearch. Search still falls back
to the legacy heavy path because it requires the trace-side join.Benchmarked on a production-shaped fixture (workspace with ~2.3M items in
EIA): no filter 3.4 s, EXPERIMENT_ITEM filter 0.4 s, FEEDBACK_SCORES_AGGREGATED
0.5 s, AGGREGATED_IS_EMPTY 1.2 s, combined EIA filters 0.6 s. DATASET_ITEM
filter still pays the DIV pre-resolve scan but skips the heavy upstream CTEs.Co-Authored-By: Claude Opus 4.7 (1M context) noreply@anthropic.com
- [OPIK-6311] [BE] fix: skip ZERO_UUID aggregate project_id in SELECT_TARGET_PROJECTS
When populateAggregations runs before any traces exist for an experiment,
experiment_aggregates.project_id is stored as ZERO_UUID
(00000000-0000-0000-0000-000000000000). The legacy SELECT_TARGET_PROJECTS
computed projects from the traces table, so it correctly returned an empty
set in that state. The earlier rewrite read experiments.project_id directly
and returned the experiment's project_id even with no traces, which then
narrowed downstream trace filters to a project_id that had no trace data —
causing FIND_GROUPS / FIND_GROUPS_AGGREGATIONS to return empty
(ExperimentAggregatesIntegrationTest.experimentsWithZeroUuidAggregateProjectIdAreVisibleViaFallback).The fix:
- Drop the experiments.project_id UNION branch entirely.
- Filter ZERO_UUID out of eia_projects via AND project_id != :zero_uuid.
- Fall back to the experiment_items -> traces traversal for any experiment
without a valid (non-ZERO_UUID) aggregate.
This matches the legacy "traces are the source of truth" semantics: the
target_project_ids set only includes projects backed by actual trace data.
On observed prod workspaces this drops the result from a buggy 10 (including
ZERO_UUID and three projects with no traces) to the correct 6, latency ~1.3 s
vs the legacy ~23 s.Co-Authored-By: Claude Opus 4.7 (1M context) noreply@anthropic.com
- [OPIK-6311] [BE] fix: dedup eia_projects via sorting key instead of FINAL
experiment_aggregates is ReplicatedReplacingMergeTree on
(workspace_id, dataset_id, id) with last_updated_at as the version column.
The earlier eia_projects CTE projected non-key columns (project_id) without
deduplication, so it could read a stale project_id from a prior version row
and incorrectly suppress the experiment_items -> traces fallback via
legacy_scope.Switch to the same LIMIT 1 BY sorting-key dedup pattern OPIK-6519 uses for
trace_threads: read all candidate rows, sort by sorting key DESC +
last_updated_at DESC, LIMIT 1 BY the sorting key, then filter ZERO_UUID on
the deduped result. Same latency as FINAL on this query (the IN clause
keeps the candidate set small) without the merge cost.Co-Authored-By: Claude Opus 4.7 (1M context) noreply@anthropic.com
- [OPIK-6311] [BE] style: bind zero_uuid as UUID, matching other call sites
Address review nit: every other zero_uuid bind in this file passes the
UUID object directly (lines 1940, 1971, 2003, 2209, 2251, 2619); the new
getTargetProjectIdsForExperiments site was the only one passing .toString().
Functionally equivalent, just consistent.Co-Authored-By: Claude Opus 4.7 (1M context) noreply@anthropic.com
- [OPIK-6311] [BE] docs: note slim_count CTE divergence on the constant javadoc
Address review nit: flag that the slim branch's dataset_items_filtered_ids
mirrors the push_top_limit one minus the dataset_version_id predicate.Co-Authored-By: Claude Opus 4.7 (1M context) noreply@anthropic.com
Co-authored-by: Claude Opus 4.7 (1M context) noreply@anthropic.com
下载附件