-
[OPIK-6311] [BE] perf: extend push-top-limit to filtered queries via slim DI ID CTE (#6567)
frostbyte_neo 发布于
2026-05-04 09:41:17 +00:00 - [OPIK-6311] [BE] perf: push Top-N filter into dataset_items_aggr_resolved CTE
Extends push_top_limit so it also activates for the default-sort case
(no sortingFields, no filters, no search) used by the experiment compare
page. In that case the Top-N CTE is defined before
dataset_items_aggr_resolved, and dataset_item_id IN (top_dataset_items)
is pushed into the dedup CTE so the existing bloom_filter / minmax skip
indexes on dataset_item_versions and experiment_item_aggregates can prune
granules.When sorting requires the dataset-items join (push_top_needs_div), keep
the previous CTE order to avoid forward references.Implements OPIK-6311.
- [OPIK-6311] [BE] test: cover default-sort, filtered, searched, and orphaned paths on aggregated branch
Adds four integration tests in ExperimentAggregatesIntegrationTest that
exercise the has_aggregated branch of the dataset items + experiment
items endpoint, locking in regression coverage that was previously
missing for the comparison endpoint.-
defaultSortPaginationConsistentBeforeAndAfterAggregates: queries page
1 and page 2 without a sorting parameter, before and after calling
populateAggregations on both experiments. Asserts page IDs match
exactly and pages do not overlap. The existing parameterized
pagination test always supplies an explicit sort, so this is the
first coverage of the default-sort pushdown path. -
filteredResultsConsistentBeforeAndAfterAggregates: parameterized over
three duration filter cases (matches all, matches none, matches a
subset). Asserts filtered IDs and totals match exactly before and
after aggregation. -
searchedResultsConsistentBeforeAndAfterAggregates: applies a search
term that matches some items and one that matches none, asserting
IDs and totals match before and after aggregation. -
experimentItemsForDeletedDatasetItemConsistentBeforeAndAfterAggregates:
deletes half of the dataset items so experiment_items become
orphaned, then asserts the comparison endpoint returns identical
results before and after aggregation.
- [OPIK-6311] [BE] perf: simplify GROUP BY in aggregated comparison query when push_top_limit is active
When push_top_limit activates the row-volume in the aggregated branch
of /v1/private/datasets/{id}/items/experiments/items collapses to ~N
items per page. In that regime, hashing the wide GROUP BY key (14
columns including Map and Array types) for every joined row dominates
runtime relative to the same work performed via any() aggregates.This change introduces a push_top_limit-gated alternative for the
projection and GROUP BY of the aggregated branch:- Projection: di.* references in the outer SELECT are wrapped with
any() (they are uniform within a group keyed by dataset_item_id and
resolved_dataset_version_id, so any() is semantically equivalent to
including them in the group key for current data shapes). - GROUP BY: drops the redundant di.* columns and keeps only
ei.dataset_item_id, dataset_id, and COALESCE(di.data, map()).
Keeping data in the group key preserves the single column most
likely to differ between rows under any future change to the OR-join
semantics, while still removing 11 hashes per row.
Behavior is unchanged when push_top_limit is false: the original
projection and full 14-column GROUP BY are used, avoiding the +15%
slow-path regression observed when this rewrite is applied to large
groups.Measured on a representative workload (single experiment, ~2.4M items,
default-sort 25-row page, post-OPT3 baseline 0.45s): query time drops
to ~0.43s (-4%). When combined with OPT3, this brings the same query
from the original ~10.5s down to ~0.43s overall.- [OPIK-6311] [BE] fix: gate push-top-limit off when filters or search are present
The Top-N CTE selects items by sort BEFORE the outer query applies
filters and search. When filters or search are active the outer scan
strips items from the top-N, which silently returns short pages even
though additional matches exist below the cutoff.Tightens the pushTopLimit gate to require !hasFilters && !hasSearch
across both the explicit-sort and default-sort branches. The
default-sort branch already had this guard; this lifts it to also
cover the explicit-sort path that has been latent since the original
push-top-limit work.Adds a regression test in ExperimentAggregatesIntegrationTest that
reproduces the bug: items with deterministic durations 10s, 20s,
... per index; sorted by duration ASC with filter duration > 30000ms;
page size 2. Without the fix, the unfiltered top-2 picks the lowest
durations which then fail the filter, returning an empty page even
though items at index 3+ match. With the fix, the raw-branch result is
preserved post-aggregation. Verified via assertDatasetItemsWithExperimentItems
for full-object equality.- [OPIK-6311] [BE] fix: pick di metadata via argMax tied to resolved_dataset_version_id
In the push_top_limit branch the previous any(di.*) aggregates picked
metadata non-deterministically across resolved_dataset_version_id
boundaries: when the same dataset_item_id is referenced by experiments
that resolved to different dataset versions, the JOIN produces one
ei row per (experiment, version) and any() could return description /
tags / etc. from the wrong version.Replaces the di.* aggregates with argMax(di.X, eas.resolved_dataset_version_id)
so the result is deterministic — always the metadata from the highest
(latest) resolved version for that dataset_item_id. Also drops
COALESCE(di.data, map()) from the GROUP BY since it now goes through
argMax too, leaving the group key as just (ei.dataset_item_id, dataset_id).Behavior is unchanged when push_top_limit is false. Common case (all
experiments share the same resolved version) is unaffected.Measured DB benchmark median across 5 rounds: ~0.425s (was ~0.432s
with data kept in GROUP BY, was ~0.436s for OPT3 alone). Cumulative
speedup vs original (10.5s -> 0.425s) ~ 24.7x.- [OPIK-6311] [BE] perf: extend push-top-limit to filtered queries via slim DI ID CTE
Folds all filter blocks into the pre-DI top_dataset_items CTE so the IN
filter on dataset_item_id can prune the dataset_items_aggr_resolved
dedup CTE and the outer EIA scan via the existing bloom_filter / minmax
skip indexes — the same mechanism the no-filter OPT3 path uses.For dataset_item_filters (filters on DI columns like id, source,
trace_id, tags, created_at, etc.), introduces a slim
dataset_items_filtered_ids CTE that pre-resolves filter-matching IDs
from dataset_item_versions. top_dataset_items joins those IDs via
arrayJoin([id, row_id]) IN, so the rest of the pipeline gets the
narrowed ID set automatically.Gate logic skips pushdown for:
- search (search references di.data, would force post-DI placement,
which we measured as a net regression). - sort-on-DI fields combined with any filter (post-DI doesn't fold
filters and adding them duplicates the outer JOIN+GROUP BY). - sort-on-DI without filter: keeps the existing post-DI placement
unchanged.
DB benchmarks on a representative experiment (~2.4M items, page 25
sorted by id DESC, 10 rounds each):EIA-only filter (ilike(output, '%true%') matching ~82k EIA rows):
no pushdown: 4.37s median → with pushdown: 0.33s median (13.0x).Dataset-item filter (ilike(data['input'], '%comedy%') matching ~128k DI
rows):
no pushdown: 5.81s median → with pushdown: 0.80s median (7.3x).Both paths produce byte-identical 25-row outputs (MD5 verified).
EXPLAIN confirms dataset_item_versions scans drop from 405 granules to
1 via the existing skip indexes.Targeted tests pass: 21 in ExperimentAggregatesIntegrationTest (incl.
the existing 20-variant parameterized sort + the explicit-sort+filter
reproducer that now exercises the new pre-DI-with-filter path) + 117 in
DatasetsResourceTest comparison-endpoint suites. mvn spotless:check and
mvn -DskipTests compile both pass.- [OPIK-6311] [BE] chore: use imports and builders in explicitSortAndFilter test
Address review nits on the explicitSortAndFilterConsistentBeforeAndAfterAggregates
test: import SortingField/Direction instead of using fully-qualified class names,
and switch the SortingField and ExperimentsComparisonFilter constructions to the
builder pattern for readability.- [OPIK-6311] [BE] revert: drop push_top_limit-gated GROUP BY simplification
Reverts
382e61cc48(perf: simplify GROUP BY in aggregated comparison query
when push_top_limit is active) and453f6cacce(fix: pick di metadata via
argMax tied to resolved_dataset_version_id), the follow-up that depended on
it. Restores the original 14-column GROUP BY projection for all paths.The benchmark gain measured for the simplification was inside the noise
band of the OPT3 + dataset_items_filtered_ids pushdowns it stacked on top
of, and the path it added (push_top_limit-gated argMax aggregates) is not
worth the extra branch in the StringTemplate, so dropping it.- [OPIK-6311] [BE] test: cover dataset_item filter across two distinct dataset versions
Adds multiVersionExperimentsFilterConsistentBeforeAndAfterAggregates plus
createExperimentPinnedToFreshDatasetVersion helper. Each helper invocation
inserts a fresh dataset_items batch (emitting a new dataset_version) and
then creates the experiment so it auto-pins to the just-published version,
so calling it twice on the same dataset gives two experiments at two
distinct resolved_dataset_version_id values.The query then forces the dataset_items_filtered_ids CTE to render via a
DI-strategy filter on id (a tautology, id != random uuid), exercising the
FROM dataset_item_versions FINAL dedup across multiple version rows in
scope, and asserts before-aggregation and after-aggregation results stay
identical.下载附件