Beating ClickBench SOTA with a team of agentic systems engineers
With no human in the loop, an agent team cut Quickwit's hot-query time by 31% and beat the fastest published ClickBench result on object storage.
We pointed a 20-agent systems engineering team at Quickwit, the open-source search engine we use at TabbyML to store agent trajectories. Over four days and roughly 350 experiments, the team cut total hot-query time from 40.5 s to 27.9 s and dropped the hot geomean from 0.403 s to 0.304 s, putting Quickwit past every published Data Lake entry on ClickBench’s own score.
The team reached that result without human intervention: agents profiled the engine, proposed optimizations, reviewed plans, implemented patches, and tested correctness and performance around the clock. Six changes survived review into the final engine, while findings from rejected runs shaped subsequent plans.
How the team works
You can’t just point an agent at a codebase and tell it to improve performance. Without structure, agents chase benchmark noise, game the test suite, or overwrite each other’s changes in parallel. So we split the work into three roles: read-only investigation, one review gate for plans and evidence, and isolated lanes for implementation and benchmarking.
Investigate, review, verify
Follow proposals downward. The solid return path advances the codebase; the dashed path carries findings back into the next proposals.
Profile the accepted engine
Investigator agents Read-only, in parallel
Findings and proposals
Verification agents Isolated validation lanes, in parallel
Lane 1
Implement · check · measure
Lane 2
Implement · check · measure
Lane N
Implement · check · measure
Each candidate vs its accepted baseline, on its own host. Findings remain shared.
Two different feedback paths
Re-profile the new base
Inform the next proposals
Investigator agents examine performance across the workload, connecting profiles with the engine code to find shared bottlenecks. They work read-only and in parallel across Quickwit’s decoding, grouping, merging, and storage reads, returning proposed engine improvements or evidence that a path has reached a format limit.
The lead agent reviews proposals before anyone writes a patch, starting with generality. The fix must improve an engine mechanism, not teach the engine to recite ClickBench from memory. Faster handling of millions of small groups is fair game. Special-casing ClickBench field names, or anything resembling known answers, is out. Without that rule, an automated loop optimizing against a benchmark eventually learns to cheat.
Verification agents take an approved plan into an isolated lane, read the targeted code first, and push back if the plan contradicts what’s actually in the codebase. Only when unit tests and a targeted microbenchmark clear the bar do they spend a full benchmark run checking end-to-end latency and regressions.
Each experiment has an ID linking its proposal, measurements, and review decision. Here is E237, an experiment to reduce the cost of growing aggregation state, through every stage.
E237: Grow aggregation state in pages
Candidates advance through these gates until one fails. In E237, the verification agent found that the approved plan targeted the wrong buffers; the lead agent revised it before implementation.
- Profile the accepted baseShared starting point
With the tiny hash tables gone after E119, Q33 spends its time growing and copying large arrays.
- Trace the cost into codeInvestigator agent
Traces the allocations to the temporary buffers used during grouping.
- Proposal reviewLead agent
General mechanism, not a Q33 trick. Contract: Q33 below 5.60 s from 5.891 s; same answers, API and wire format; peak memory below this experiment’s 18.5 GB ceiling.
- Read the code firstVerification agent
The buffers are cleared every 2,048 documents, so they never grow large. The real cost is the arrays holding each parent group. It reports the mismatch; the lead agent checks the evidence and retargets the experiment.
- Cheapest disproving checkVerification agent
A page-growth microbenchmark before committing to full timing.
- Full timing and verificationVerification agent
Three runs: Q33 5.891 → 3.131 s (−47%), ranges not overlapping, copy frames gone, answers unchanged, peak memory 17.8 GB.
- Evidence reviewLead agent
Kept: the largest single-query win of the campaign, and the base for the next round.
Most of the ~350 experiments died much earlier than E237. If you’ve spent a week profiling a database, the dead ends will look familiar: ideas killed in review, hypotheses that fall apart as soon as a verification agent reads the calling code, or microbenchmarks that barely move. When those cheap checks held up, verification agents ran the full benchmark suite and brought the numbers back to the lead agent. Each experiment’s plan defined its validation rules, including performance and memory limits.
The results
We compared against ClickBench’s Data Lake category: self-managed engines querying files in object storage. Engines serving the benchmark from local disk use a different storage model, so we left them out. On ClickBench’s own score, the hot geomean, Quickwit finished ahead of every entry: 1.16x faster than Duckflight, the fastest published result.
Because published entries ran on different hardware, we also ran ClickHouse on the same machine and object store as Quickwit, where Quickwit was 1.29x faster on hot geomean.
Leaderboard
ClickBench's score: geomean seconds over the 40 queries, with its +10 ms constant. Hot is the best of two repeat runs, cold the first run after a restart. Lower is better; ratios are against Quickwit after this loop.
- Hot#1
- 0.304 sreference
- Cold#1
- 0.946 sreference
- Hot#2
- 0.354 s1.16×
- Cold#2
- 0.968 s1.02×
- Hot#3
- 0.384 s1.26×
- Cold#5
- 2.140 s2.26×
- Hot#4
- 0.392 s1.29×
- Cold#4
- 1.737 s1.84×
- Hot#5
- 0.403 s1.33×
- Cold#3
- 1.128 s1.19×
Our runs used a 16-vCPU Google Cloud machine with 31 GB of memory (c3d-highcpu-16), reading 16 index splits from GCS; we kept that index fixed and excluded build time. Starting from unmodified Quickwit (961c871) with default settings, each accepted change became the baseline for the next round.
We measured 40 ClickBench queries over 100 million rows, excluding Q19, Q28 and Q29 because they use SQL expressions that Quickwit’s Elasticsearch-style query language can’t express. Every external score on the leaderboard is recomputed from ClickBench’s per-query timings over those same 40 queries, and we supplied the missing Quickwit translations for Q26, Q27, Q30, Q36 and Q40.
Published ClickBench entries run each query three times in a single pass (one cold run, then two repeats). To reduce run-to-run noise, we ran three full passes per step: cold is the median first try after restarting and dropping the OS page cache, and hot is the smaller of the median second and median third tries. Leaderboard scores use ClickBench’s geomean with its 10 ms offset, while experiment records report per-pass hot medians and ranges. Cold improvements also reflect storage latency and run-to-run variation, so we don’t attribute them solely to the patches.
Where the lead came from
The six accepted changes covered column-cache sizing, top-ten selection, small nested groups, decompressor reuse, bulk column reads, and growing aggregation state. Most were low-level patches in Tantivy, the Rust search library underneath Quickwit. Together they cut total hot-query time from 40.5 s to 27.9 s, with the biggest cuts landing on the slowest queries.
Step through them below to see what each change shaved off across the 40 queries.
The climb
Select a bar or press play to compare query times. Click an experiment ID to open its record.
- Q010.002 s0%
- Q020.119 s-16%
- Q030.095 s-29%
- Q040.053 s-31%
- Q050.423 s-7%
- Q060.302 s-7%
- Q070.077 s-43%
- Q080.130 s+1%
- Q090.674 s-9%
- Q100.856 s-26%
- Q110.172 s-9%
- Q120.219 s-6%
- Q130.185 s+3%
- Q140.659 s-11%
- Q150.321 s+4%
- Q160.404 s-5%
- Q170.812 s-33%
- Q180.793 s-40%
- Q200.102 s-11%
- Q211.73 s-16%
- Q221.83 s-9%
- Q232.96 s-16%
- Q241.82 s-12%
- Q250.194 s-20%
- Q260.167 s-9%
- Q270.186 s-28%
- Q300.050 s-29%
- Q310.452 s-10%
- Q320.523 s-52%
- Q333.13 s-62%
- Q340.168 s-87%
- Q350.168 s-87%
- Q365.98 s-18%
- Q370.097 s-10%
- Q380.096 s-7%
- Q390.118 s-17%
- Q401.53 s-4%
- Q410.113 s-14%
- Q420.095 s-5%
- Q430.115 s-9%
Sum of hot times. The outline is vanilla Quickwit; each segment is one query, largest first.
- 40%+ faster
- 20-40% faster
- 7-20% faster
- within 7%
- slower
- selected measurement or guard query
There is still room left, and you can see where if you look at total time instead of geomean. Duckflight’s total hot time is 22.0 s against our 27.9 s: its edge sits on the few slowest queries, which the geomean weighs no more than a 50 ms lookup. The biggest lever we haven’t pulled is index layout. The slowest split decides when a query finishes, and our largest holds 11% of the rows against an even 6.25%; rebalancing costs a 44-minute rebuild per experiment, so we kept the index fixed this run.
A self-correcting agent team
Every result, kept or not, goes back to the lead agent before the next round starts. The lead agent can hold back a measured win or turn a failed experiment into the next plan. In E251, that audit caught an ordering bug in an IP-address query even though every ClickBench answer check passed.
E251 looked ready to keep. Q36 asks for the ten IP addresses with the most visits. Quickwit merged roughly 100 million entries, converted them into result objects, then selected ten. The verification agent moved selection before conversion, so only the ten survivors needed result objects. A microbenchmark ran 3.02x faster, and Q36 fell from 5.981 s to 5.300 s, 11% faster. The profile lost exactly the conversion work the plan predicted, and every ClickBench answer matched.
The lead agent put it on hold anyway, checking something the benchmark hadn’t: whether selecting raw keys preserved the existing ordering. The shortcut compared IP addresses as numbers; the existing path compares them as text. As text, 10.0.0.10 sorts before 10.0.0.2; as numbers, it sorts after. With tied counts at the cutoff, that difference can change which rows make the top ten.
E251: Faster IP selection could change the query results
Six example IP addresses, two of them tied at the cutoff. Switch how keys are compared and how the query orders them; the line marks where LIMIT 3 cuts.
As text, "10.0.0.10" sorts before "10.0.0.2": the strings first differ at 1 vs 2.As numbers, 10.0.0.2 sorts before 10.0.0.10: the last part is 2 vs 10.ORDER BY visits DESC LIMIT 3ORDER BY address LIMIT 3
Returns 192.168.1.4, 10.0.0.7, 10.0.0.10. This is the shipped behavior.
Returns 192.168.1.4, 10.0.0.7, 10.0.0.2. 10.0.0.2 replaces 10.0.0.10: a different answer.
Returns 10.0.0.10, 10.0.0.2, 10.0.0.30. This is the shipped behavior.
Returns 10.0.0.2, 10.0.0.7, 10.0.0.10. 10.0.0.7 replaces 10.0.0.30: a different answer.
ClickBench has no tie at Q36’s cutoff, so all 40 answer checks stayed green. If you only gate on benchmark output, you merge that 11% win and quietly change query results the first time production data ties at the tenth row. The lead agent held the patch out of the next baseline and logged the trap in the experiment history so later rounds wouldn’t rediscover the same shortcut.
E094 made nested grouping 28% faster but slowed a single-level grouping query by 9%. The lead agent queued E119 to apply the change only to nested groups. That version retained the speedup and removed the regression.
Define the next climb
ClickBench was the performance goal, so the multi-agent team optimized for ClickBench specifically. Its timings measured the gains; agent review and correctness checks decided which patches shipped.
On most teams, an engine like Quickwit runs as shipped unless something catches fire, because pulling engineers off product work to spend days inside a dependency’s internals is hard to justify. With an agent team, you can aim that kind of effort at throughput in a data pipeline, tail latency in a service, or CPU cost in a telemetry collector. You define the workload, the metric, and the constraints: what must not change and which resources it can spend. The agents handle the profiling, dead ends, and verification, and hand back reviewed patches with the experiment record.
We’re looking for the next system to point this team at. If you have a performance bottleneck worth climbing on, reach out at [email protected].
Appendix 1: Selected plans, measurements and patches
Selected milestone records from the campaign, covering the shipped optimizations and representative rejections: what each plan predicted, how it was checked, what we measured, and the patch where one modified engine code. The deferred split-layout rebalance is covered in Where the lead came from above. The complete aggregation-memory investigation (E119, E237 and E284) follows these records; E251’s held-back speedup is covered in the main story.
Edited from the original experiment plans and run records for this post, not verbatim transcripts. Local paths, storage history and operational details are omitted. Predictions are kept separate from measurements; PR links point to patches in our fork, not claims of upstream acceptance.
Showing all 12 records
E024Fit the active column working set in the fast-field cacheKept
The plan
- Hypothesis
- Column scans across 100M rows evict decoded fast fields under the 1 GB default cache. Sizing `fast_field_cache.capacity` above the active working set should stop repeat fetches on hot runs without exhausting memory on the 31 GB node.
- Proposed change
- Raise `fast_field_cache.capacity` from 1 GB to 4 GB against the same GCS index and binary.
- Check
- Three full-suite benchmark passes, checking hot geometric mean, cold stability and actual peak RSS.
The record
- Observed
- Total hot time 40.5 → 36.2 s (-10.6%); hot geomean 0.403 → 0.349 s (-13.1%). Peak RSS rose by only 0.8 GB (21.3 → 22.1 GB) despite 3 GB of extra cache headroom, showing the active column working set spilled past 1 GB by roughly 0.8 GB.
- Disposition
- Kept as the first accepted step. 4 GB sits right above the working-set knee for 100M rows while leaving memory headroom for heavy grouping queries.
E053Reserve space for the grouping tableDropped
The plan
- Hypothesis
- Repeated growth of Q33's grouping table may explain its allocation cost. Reserving capacity in advance should reduce rehashing. Target: Q33 below 7.65 s, more than 10% faster than its measured baseline.
- Proposed change
- Pre-size the top-level grouping table from a capacity hint, capped at 65,536 entries. Keep nested tables lazy so a reservation is not multiplied by millions of parent groups.
- Check
- Three benchmark runs, with guards for other queries, memory use and unchanged answers. Earlier variants that reserved space in nested tables exhausted memory and were revised before the final run.
The record
- Observed
- Q33 hot 8.498 → 8.367 s (-1.5%), within noise. The final reservation covered only about 0.65% of a roughly 10.1-million-entry table.
- Disposition
- Dropped. This capped reservation did not meet the target. It did not settle whether a correctly sized reservation could help; E284 revisited that question.
E078Select ten results instead of sorting everythingKept
The plan
- Hypothesis
- Q36 only returns ten buckets. Partitioning to the top ten and sorting that slice should remove a serial full sort. Target: Q36 below 6.16 s, more than 10% faster, with the same buckets, ordering and discarded-count total.
- Proposed change
- Use top-k selection for count and key ordering, keeping the existing comparator. Leave sub-aggregation ordering on its full-sort path because its tie behaviour is different.
- Check
- Three benchmark runs, a 5% regression guard for other queries, answer comparisons, and unit tests for selection, ties and the discarded-count total. All 342 aggregation tests passed.
The record
- Observed
- Q36 hot 6.848 → 6.124 s (-10.6%). The baseline and candidate run ranges did not overlap. The full sort disappeared from the profile; Q36 answers matched.
- Disposition
- Kept. Removing work on the coordinator shortened the critical path without changing the result. Merging and deserialization remained.
E094Store small groups inlineDropped
The plan
- Hypothesis
- Q33 creates millions of one-entry nested tables. Keeping the first two entries inline should remove those allocations. Target: at least 15% faster; the allocation model suggested a possible 30-40% gain.
- Proposed change
- Use a small inline map that promotes to a hash table after two entries. The first implementation also reached some large top-level maps.
- Check
- Three benchmark runs. First-run gate: Q33 below 7.0 s. Check memory, other query shapes and deterministic answers.
The record
- Observed
- Q33 hot 8.152 → 5.865 s (-28%), with 5.5 GB less peak memory. But Q16 became 9% slower: its single large table paid the inline-map checks without saving allocations.
- Disposition
- Dropped as written, retaining the finding. The next version limits the representation to nested groups. That is E119, not an additional gain on top of E094.
E107Stream intermediate results into the mergeDropped
The plan
- Hypothesis
- Avoiding a temporary map and an extra hash lookup during merging might improve Q36 by 8-12%. This prediction assumed the engine still materialized that temporary map.
- Proposed change
- Proposed: merge incoming entries directly into the destination instead of constructing an intermediate map.
- Check
- Read the existing merge implementation before building or benchmarking the proposed change.
The record
- Observed
- The pinned dependency already contained the streaming merge. The profile frames were costs of the existing implementation, not proof that the feature was missing. No patch or benchmark run.
- Disposition
- Dropped because the premise was wrong. The remaining work was deserialization and allocation, which required a different experiment.
E119Use inline groups only where they helpKept
The plan
- Hypothesis
- The Q16 regression comes from applying E094 to a top-level map. Keeping that map unchanged should restore Q16 to about 0.433 s while keeping Q33 near 5.8 s.
- Proposed change
- Use the existing top-level flag to choose the old hash map for top-level groups and the inline representation for nested groups.
- Check
- Three benchmark runs. First-run gates: Q16 below 0.45 s and Q33 below 6.1 s. Compare against both E094 and the pre-inline baseline; check answers, memory and aggregation tests.
The record
- Observed
- Q16 returned to 0.426 s, inside its range before the inline change and outside the slower E094 range. Q33 stayed near 5.87 s. Peak memory remained below this experiment’s 17 GB gate.
- Disposition
- Kept as the scoped version of E094. The large nested-group improvement survived without the top-level regression.
E134Avoid decoding empty sub-aggregationsDropped
The plan
- Hypothesis
- Q36 has no nested aggregations, yet every bucket decodes an empty map. A compact absent-value representation might improve Q36 by 5-12%, provided the message format remains compatible.
- Proposed change
- Try a custom encoding that represents an empty aggregation without constructing a map during decoding.
- Check
- A release microbenchmark and the legacy-message compatibility test before any full benchmark.
The record
- Observed
- Microbenchmark: decode 7.34 → 4.05 ns per entry; encode 8.43 → 3.92 ns. But the legacy-message test failed: non-empty maps gained a format byte, breaking old/new version interoperability. No full benchmark.
- Disposition
- Dropped at the pre-check. Mixed-version nodes must exchange these messages during rolling upgrades; faster empty-map handling did not justify breaking that contract.
E152Reuse the term-dictionary decompressorKept
The plan
- Hypothesis
- Constructing a decompressor for each compressed dictionary block costs about 7% of Q23 CPU. Reusing one per reader should remove that setup. Target: Q23 below 3.10 s on the first run and at least 5% faster overall.
- Proposed change
- Allocate a decompressor lazily for each block reader and reuse it across blocks. Keep the block format and decoded bytes unchanged.
- Check
- Compile checks and a compression-enabled microbenchmark, then three benchmark runs. Require non-overlapping Q23 timing ranges, the setup frame gone from the profile, and no material regression in related text queries.
The record
- Observed
- Q23 run range 3.224-3.288 → 2.935-3.001 s. The median improved 7.2%; the headline comparison 3.288 → 3.001 s is 8.7%. Decompressor creation disappeared from the leading profile frames.
- Disposition
- Kept. Related text queries were collectively equal or faster, deterministic answers matched, and memory stayed within the gate.
E186Decode column ranges in bulkKept
The plan
- Hypothesis
- One column encoding falls back to per-value reads even for contiguous ranges. Bulk unpacking should remove repeated lookup and reconstruction work. Target: Q07 below 0.125 s on the first run and at least 7% faster.
- Proposed change
- Add a bulk range-read implementation for blockwise-linear columns, respecting codec-block boundaries. No storage-format change. A companion path for scattered reads was considered but not included.
- Check
- Require a microbenchmark speedup of at least 1.3x before the full run. Then three benchmark runs with guards for other column queries, unchanged answers and memory use.
The record
- Observed
- Bulk-read microbenchmarks improved 2.0-3.2x. Q07 hot 0.128 → 0.082 s (-35.5%); Q04 -24.4%; Q30 -27.2%; Q10 -6.0%. Hot geomean 0.329 → 0.315 s.
- Disposition
- Kept. The measured gain came from the contiguous-range path only; the proposed scattered-read companion was not landed.
E237Grow aggregation state in pagesKept
The plan
- Hypothesis
- Growing contiguous arrays for group state copies large allocations, accounting for roughly half of Q33 CPU. Fixed-size pages should remove those copies. Target: Q33 below 5.60 s, more than 5% faster.
- Proposed change
- Use grow-only paged storage for per-group statistics and parent-group maps. Preserve indexed access, the aggregation API and the intermediate message format.
- Check
- Microbenchmark page growth before the full run, then three benchmark runs. Require Q33 ranges entirely below the baseline, the copying frames reduced, deterministic answers unchanged and memory below this experiment’s 18.5 GB ceiling.
The record
- Observed
- Q33 hot 5.891 → 3.131 s (-46.9%), with non-overlapping run ranges. The two large reallocation stacks vanished from the leading frames. Peak memory was 17.84 GB. Q36 showed a marginal 3% slowdown, recorded as run noise rather than a demonstrated code regression.
- Disposition
- Kept and used as the headline endpoint. Across the full loop from vanilla Quickwit: hot geomean 0.403 → 0.304 s (-24.4%); total hot time 40.5 → 27.9 s (-31%). E251 is not included.
E251Select raw entries before building resultsDropped
The plan
- Hypothesis
- Q36 builds a final result object for every merged entry before selecting ten. Selecting raw entries first should remove that materialization. Target: Q36 below 5.55 s, at least 6% faster, with exactly the same selection and ordering.
- Proposed change
- Apply top-k selection to raw count/key entries and convert only the survivors. Keep the full conversion path for sub-aggregation ordering.
- Check
- A release microbenchmark, aggregation tests and three benchmark runs, plus code review of ordering semantics. Benchmark answer comparisons alone cannot establish compatibility outside the tested queries.
The record
- Observed
- Microbenchmark 3.02x faster. Q36 hot medians 5.981 → 5.300 s (-11.4%); targeted conversion frames disappeared. Benchmark answers matched, but review found that IP keys now compared numerically instead of as text.
- Disposition
- Rejected. Sorting IP addresses, or breaking a count tie at the cutoff, could return a different answer. The timing gain is real but is excluded from the kept chain.
E284Pre-size the table using the document countDropped
The plan
- Hypothesis
- After E237, table growth is 27.33% of Q33 CPU. Unlike E053's small reservation, a document-count-derived capacity might remove most rehashing. Predicted Q33 target: roughly 2.29-2.50 s, 20-27% faster.
- Proposed change
- Pre-check an 8-million-entry reservation on top-level maps. The full proposal would add memory-budget accounting and a density safeguard; nested maps would stay lazy.
- Check
- Run Q33 against a same-machine control before preparing the full patch. Require the rehash frame to shrink below 5% and check whether wall time actually improves. No full-suite run unless the cheap check supports the win.
The record
- Observed
- Rehashing disappeared, but Q33 wall time was unchanged: 3.176 → 3.195 s. CPU per repetition increased 44%; time moved into filling the large, sparse table.
- Disposition
- Dropped after the cheap check. Removing a profile frame did not make the query faster. No full benchmark or PR.
Appendix 2: Three ways to spend less memory
Q33 groups 100M rows by two IDs, counts each pair and returns the most frequent. It was one of the slowest queries in the suite, and the team came back to it three times. Each round started from the engine the previous round left behind.
The profile at the start of each round shows how every fix moved the bottleneck somewhere else.
Each fix moved the bottleneck somewhere else
Left: Q33's hot CPU profile at the start of each round, split by what the time was spent on. Right: Q33's wall clock. Pick a cost to follow it across rounds.
- 9%30%9%17%20%15%
allocating small tables. E119 stores small nested groups inline.
8.15 s - 49%12%21%10%
copying growing arrays. E237 grows the parent-group arrays in pages.
5.87 s - 27%23%27%21%
resizing hash tables. E284 reserves 8 million entries up front.
3.13 s - Resize frame gone. No full profile was recorded.
Resize left the profile, but Q33 went 3.176 s (same-day control) to 3.195 s and CPU per repetition rose 44%.
3.19 s
Round one: millions of hash tables holding one or two entries
Quickwit handled the two-level grouping with a separate hash table for every first-level group, about 6.25 million small tables per split. Most held one or two entries, so the allocation and bookkeeping cost more than the contents.
The first patch stored up to two entries inline in the group, found them with a linear scan, and only allocated a real hash table when a third entry showed up. Q33 went from 8.15 s to 5.87 s, 28% faster, and used 5.5 GB less memory. Two other nested-grouping queries improved by about 10%, supporting the diagnosis that many small inner groups caused the overhead.
It also made Q16 9% slower. Q16 groups by a single field into one huge table, which passes two entries almost immediately and then pays for the inline check on every insert after that.
The team dropped that version and rebuilt it so only nested groups use the inline form, while top-level groups keep the ordinary hash map. Q16 returned to 0.426 s, inside its original range, and Q33 kept its gain.
Round two: the verification agent who told the lead agent the plan was wrong
With the tiny tables gone, Q33’s profile showed time going into growing and copying large arrays. The proposal traced those allocations to temporary buffers used during grouping. It passed review, and the plan told a verification agent to rework those buffers.
The verification agent read the calling code first, as the protocol required. This is the step we’d most expect a hurried engineer to skip, and it’s where the biggest win of the run came from.
The temporary buffers were cleared every 2,048 documents, so they never grew large enough to explain the profile. The real cost was in a different set of arrays: the ones holding each parent group’s map and statistics.
The verification agent reported that the temporary buffers were too small to explain the profile. The lead agent checked the evidence and redirected the experiment to the arrays holding parent-group maps and statistics.
Those arrays were ordinary contiguous vectors. When one filled up, the engine allocated a new one twice the size and copied everything across. Doubling keeps that cheap on average, but at millions of entries, moving data that hadn’t changed was costing about half of Q33’s CPU.
The patch replaced those vectors with fixed-size pages allocated on demand: 4,096 entries per page for statistics and 256 for the larger parent-map entries. An index maps to a page and an offset, so growing the array adds a page without moving anything already stored.
The illustration below sets the buffers the plan blamed against the arrays that actually grew, and counts what each growth strategy copies.
The buffers never grew. The parent-group arrays did
Drag the size of one parent-group array, or press play. The top compares what the plan blamed with what actually grew; the bottom counts the entries each growth strategy moves.
What the plan blamed, and what actually grew
Log scale. The buffer tops out at 2,048 entries however large the query gets.
Growing to that size: doubling versus pages
Doubling vector
1,048,572
entries copied
- Reallocations
- 18
- Capacity
- 1,048,576
- Unused slots
- 48,576
Pages of 4,096
0
entries copied
- Pages
- 245
- Capacity
- 1,003,520
- Unused slots
- 3,520
Q33 went from 5.891 s to 3.131 s, a 47% cut, and the run ranges didn’t overlap (5.872 to 5.901 s before, 3.058 to 3.214 s after). Hot CPU time fell from about 56 to 28 CPU-seconds: the query did half as much work. Peak memory across the suite rose from about 16.9 GB to 17.8 GB, because partly filled pages still take space. That stayed inside this experiment’s 18.5 GB ceiling.
Q36 also ran 3% slower in that session, which we recorded as run noise rather than a demonstrated regression.
Round three: the profile frame vanished and the latency didn’t
After paging, hash-table resizing became the largest remaining item in Q33’s profile, at 27% of CPU. Removing other work made resizing a larger share of the remaining CPU time.
An earlier experiment had already tried pre-sizing the table, and it did nothing. It had capped the reservation at 65,536 entries for a table that ends up around 10 million, about 0.65% of what was needed. This time the verification agent reserved 8 million entries up front, and the plan predicted Q33 would land between 2.29 s and 2.50 s.
Resizing disappeared from the profile, but Q33 went from 3.176 s on a same-day control to 3.195 s. CPU per repetition went up 44%, with the time reappearing in the code that fills the large, sparse table. The verification agent stopped at that targeted check instead of spending a full suite run on a candidate that had already failed.
Our best guess is that one huge allocation traded rehashing for worse memory locality. We didn’t measure cache misses, so that’s a hypothesis, not a finding. We rejected the patch because Q33 hadn’t improved.