In one sentence: An index built by one query and left in memory makes a completely unrelated query 18% slower for as long as it is held — which quietly taxed our own published benchmark numbers, and taxes any long-lived process that runs one path query and then keeps working.
Why this matters
A routine check before publishing a new benchmark table: does the rest of the table still say what it said? It did not. Our bucketed-count query at one million events read 84 ms against a published 59.0. Same dataset, same code path, byte-identical answer — a number that had been printed to the tenth of a millisecond, and it had moved by 42%.
A benchmark number that will not reproduce is worse than a slow one, because every conclusion resting on it is now unpinned. And the reason turned out not to be a regression at all. It was that a query's latency is a property of the process it runs in, not of the query — which is a fact about how we measure, and also a fact about how the system behaves in production.
The idea in plain language
Two terms, both defined by the problem they cause here.
A traversal index is a lookup structure TGMS builds the first time a query walks relationships — it turns "who does this entity point to?" from a scan into a direct jump, and saves about 400 ms on the query that builds it. Once built it stays in memory for the life of the process, because the next traversal query would otherwise rebuild it.
A working set is the slice of data a query touches often enough to want it in fast memory. A full-window scan streams tens of megabytes per call; the parts it re-reads are what keep it fast, and anything else large and resident competes for the same space.
The mechanism is then ordinary: the index is not used by the scan but it is resident, and it evicts the part of the scan's working set that was staying hot.
Two explanations that were wrong
The first hypothesis was a knob we had turned the day before. A scan-parallelism gate — the threshold deciding when a scan splits across threads — had been recalibrated. Testable without changing any default: force the parallel path on through an environment override and measure both ways. The result was 83.1 ms forced against 84.4 ms at the default — nothing, well inside the reproducibility band. The hypothesis was measured false before a line of configuration changed, which cost one run and saved a wrong recalibration.
The second attempt was a bisect: 5 engine builds, one run each, about fifteen minutes. It landed on a commit that persists the traversal index — three Python files, none on any scan path. That a scan-irrelevant commit could move a scan by 18% was implausible enough that the next step had to be a probe rather than a patch.
What we measured
series.count) over a
1,000,000-event synthetic bi-temporal store, warm, one 40-core host. Design:
five conditions measured in a single process against one store, so
nothing differs but what is resident — nothing held; 1M rows of plain integer
columns held; the index's own source columns held; the built index held; then
the index released. Metric: median latency in milliseconds, lower is better.
Held constant: the store, the query, the process, the code. Latency cells
reproduce to about ±20% between
days, so this comparison is only meaningful because all five conditions ran
minutes apart in one process. Records:
benchmarks/results-v1/.What we found
Holding the built index costs the unrelated query 18%. Holding a comparable weight of plain columns costs 5%, and holding the index's own source columns 4%. Releasing the index returns the time — to within a tenth of a millisecond of where it started. That reversibility is what makes it a residency effect rather than damage, and a second query measured in the same process does not move at all.
series.count at 1M, median of repeated runs, all conditions in a
single process minutes apart.That also reconciles the number we could not reproduce. The July figure was 59.0. Re-running commits that predate every engine change since then still gives about 70 ms — roughly 18% of between-day drift on the measurement host, which is a separate effect and not the index. Add the resident index's 18% on top and you land near the 84 ms that started this. Two independent 18% effects, compounding, and neither of them a code regression.
What this means in practice
First, a measurement consequence. Our registry runs 13 queries in one process in a fixed order, and the traversal query runs before the aggregation queries. So every native aggregation number we have published has been quietly paying for the index built by the query ahead of it, on every run, for as long as the table has existed. TGMS is also the only system in that table that builds such an index, so the tax is one-sided — the baselines do not pay it.
Nothing in that was wrong and no conclusion moved: the gaps those tables report are two-fold and up, far outside an 18% effect. But a reader comparing columns has a right to know it is there, so the records now say so and the tables carry an explicit ±20% reproducibility bound. One conclusion that lived inside that band — a 1.3% "win" on the interval join at 1M — is relabelled a tie.
Second, a product consequence, and it is the one that matters to users. A long-lived agent process is exactly what this describes: run one path query, keep the index, pay 18% on every scan for the rest of the session. That is a defensible trade for an agent doing repeated traversals — the index saves about 400 ms on the query that builds it — and a bad one for an agent that asks a single path question and then spends an hour aggregating.
The fix is not to delete the index, but to make residency a budgeted decision, the way segment residency already is, rather than a cache born immortal and never asked to justify itself. An engine that can say "this structure costs 18% of your scans, keep it?" is making a choice; one that silently keeps everything is making the same choice badly.
What this result does not show
- It does not show 18% is a constant. One query shape, one scale, one host, one index; a scan with a smaller working set, or a machine with more cache, would show a different number — possibly none.
- It does not show the index is not worth building. It saves roughly 400 ms on the query that builds it; this prices the other side of a trade, not the trade's verdict.
- It does not explain the between-day drift, a separate ~18% that remains uncharacterised — host state, thermal behaviour and scheduling are all candidates and none is isolated.
- The mechanism is inferred from residency and reversibility, not from cache counters; we did not instrument last-level cache misses.
Takeaway
An index no query in sight reads can still be why a query is slow, because occupying memory is itself a cost. The discipline that found it: test the plausible hypothesis with an override instead of believing it, bisect when the override says no, and when the bisect lands somewhere impossible, isolate conditions in one process rather than reasoning about them. Two of those three steps returned nothing, and both are in the records — a null result is evidence about a hypothesis.
Evidence and reproduction
benchmarks/results-v1/ with the rest, because a
null result is evidence about a hypothesis.Continue reading
- Before this: why TGMS needed a native storage engine — where that in-memory index comes from, and why it is not on disk.
- After this: where TGMS is fast and where specialists still win — the tables this effect applies to.