SQL: mixed exact/sketch plans for a two-percentile query fail to compile; two queries over one table share nothing

Workload

Two repeating SQL queries on 115cd3c2, both every 10 s with ε=0.01, δ=0.001:

Q1 SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY value), percentile_cont(0.9) WITHIN GROUP (ORDER BY value) FROM metrics WHERE ts > now() - INTERVAL '5' MINUTE
Q2 SELECT city, percentile_cont(0.9) WITHIN GROUP (ORDER BY value) FROM metrics WHERE ts > now() - INTERVAL '20' MINUTE GROUP BY city

Save as wl.json:

{"query_workload": {"language": {"sql": "datafusion_sql"}, "query_batch": null, "repeating_queries": [
  {"query": "SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY value), percentile_cont(0.9) WITHIN GROUP (ORDER BY value) FROM metrics WHERE ts > now() - INTERVAL '5' MINUTE",
   "demand": {"fixed_interval": 10000}, "requirements": {"accuracy": {"max_error": 0.01, "failure_probability": 0.001}},
   "predictability": {"predictable": {"known_at": null}}},
  {"query": "SELECT city, percentile_cont(0.9) WITHIN GROUP (ORDER BY value) FROM metrics WHERE ts > now() - INTERVAL '20' MINUTE GROUP BY city",
   "demand": {"fixed_interval": 10000}, "requirements": {"accuracy": {"max_error": 0.01, "failure_probability": 0.001}},
   "predictability": {"predictable": {"known_at": null}}}]},
 "data_workload": {"arrival": {"ingesting": {"rows_per_second": 666.67}}, "sources": {"metrics": {"series_count": 100}}}}

cargo run -p asap-devtools --bin stage_pipeline -- --workload wl.json --exhaustive --max-displayed 2000 --out out.json \
  --table '{"name": "metrics", "columns": [{"name": "ts", "type": "timestamp", "nullable": false}, {"name": "city", "type": "utf8"}, {"name": "value", "type": "float64"}], "time_index": 0}'

1. Plans that mix exact and a sketch for Q1 fail to compile

Any plan that computes one of Q1's percentiles exactly and the other with a sketch is rejected with executor_cannot_compile: … aggregate output names differ from measures. There are 84 of them (labels Q1 Kll · … and Q1 DDSketch · …). Plans that compute both percentiles the same way are not affected.

In these plans, the exact Aggregate computing the p50 has one measure (quantile, q=0.5) and the output schema ["quantile_0_5"], but output_names: []. As a result, mixed exact/sketch plans for a multi-measure query are never priced.

2. Q1 and Q2 share nothing

No candidate shares anything between Q1 and Q2, not even shared Scan (Q1, Q2), even though both read metrics. Q2's p90 per city over 20 min (120 × 10 s panes) could serve Q1's p90 over 5 min by merging the newest 30 panes and rolling up across cities, yet no candidate does this.

fix(pass1): an exact measure split from a sketched one keeps its outp…

Both problems are fixed and merged.

Problem 1 (#881, 61b5809). When Pass 1 split a multi-measure aggregate, the exact Aggregate it built for a measure that stays exact had output_names: []. It now names its one measure as the target names it. Result on this workload:

the 84 executor_cannot_compile rejections are gone: {"costlier": 328, "executor_cannot_compile": 84} becomes {"costlier": 412};
the cheapest mixed plan (Q1 Kll · Q2 Kll) costs 0.6401 cost/s, so it is priced but not selected.

Problem 2 (#887, 67f5e22; #883 was closed after a force-push broke its sync). What caused it, checked against the three suspected causes:

(a) Scans were not widened for SQL: refuted. SQL scans already read every table column, so Q1's and Q2's scans were equal. Structural CSE (cse.rs, InternTable::intern) shares a non-ASAP node only when its rows have a unique key, and a table's rows have none. Equal scans are now shared.
(b) Newest-n and rollup don't combine: partly confirmed. One SummaryMerge with both newest_panes and group_by is still rejected. But a rollup stacked over a newest-panes merge was already valid IR. What was missing:
- No rule built it. The window-composition rule's shared chains now also admit a coarser sketch build over SQL rows: it merges its newest panes, then rolls the groups up into its own.
- A rollup to no keys over a SQL relation was rejected (GlobalRelationRegroup). It now stands for the global SQL aggregate: the executor outputs one empty state when there is no input state, so the answer is a NULL row.
(c) Sizing for the strictest reader: not a problem. The shared chain is sized for every reader (sized_for_all). It is a KLL with k = 345, the size each query has alone. KLL and DDSketch error does not depend on q, so one state serves both p50 and p90.

Results on this workload (stage_pipeline --exhaustive):

	logical	physical	selected	cost/s
before	155	413	Q1 in its own 30 panes	0.5475
scan sharing only	310	981	`shared Scan (Q1, Q2)`	0.5377
after	348	1151	`one Kll for Q1, Q2 in 120 × 10s panes (Q1 newest 30)`	0.4989 (−8.9%)

Tests (sql_two_percentiles):

the mixed plans compile and match the exact answers;
the scan is shared;
the newest-n plus rollup candidate exists, and its executor answers for Q1 and Q2, NULL city included, are within ε of exact percentile_cont;
Q1 answers one NULL row over an empty window.

Open question: should equal scans always be shared, rather than offered as a choice? Offering it as a choice doubles the candidates for each shared table.

feat(pass2): share SQL table scans; a coarser grouping reads a finer …

Navigation Menu

Repository navigation

SQL: mixed exact/sketch plans for a two-percentile query fail to compile; two queries over one table share nothing #878

Workload

1. Plans that mix exact and a sketch for Q1 fail to compile

2. Q1 and Q2 share nothing

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

SQL: mixed exact/sketch plans for a two-percentile query fail to compile; two queries over one table share nothing #878

Description

Workload

1. Plans that mix exact and a sketch for Q1 fail to compile

2. Q1 and Q2 share nothing

Activity

zzylol commented on Oct 22, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions