Three bugs that made our time-series queries 680 slower (and how we found them) MoteDB, an embedded database for embodied AI, fixed three bugs that made a top-k time-series query on INT timestamp columns 680x slower, cutting latency from 1,196ms to 1.76ms on a 1M-row table. The bugs included a top-k decoder that silently skipped DeltaVarint chunks, zone-map metadata that stayed at (0,0) for integer-backed buffers and pruned recent data, and a hardcoded in-range check that hid uncommitted writes; the Python binding also never routed time-series top-k to the fast kernel despite EXPLAIN showing it. The fixes ship in v0.12.1. A SELECT ... ORDER BY ts DESC LIMIT 10 on a 1M-row table took 1.2 seconds . It should have taken single-digit milliseconds. The worst part? Our EXPLAIN said we were using the fast path. We were wrong. This is the story of the three bugs behind that number, how differential testing caught them, and how v0.12.1 of MoteDB https://github.com/motedb/motedb fixed them — from 1,196ms down to 1.76ms , faster than a DuckDB full scan on the same data. If you ship software with "fast paths", this post is for you, because every single one of these bugs survives a normal test suite. MoteDB is an embedded database for embodied AI — robots, AR glasses, industrial arms. The single most frequent query on a robot is some variant of: SELECT FROM sensor WHERE device = 'arm 7' ORDER BY ts DESC LIMIT 10 "Show me the last 10 readings." It runs constantly: dashboards, anomaly drills, health checks, LLM tool calls. On our time-series layout columnar segments, gorilla-style timestamp encoding, zone maps per segment , this should hit a top-k fast path and never decode the full table. Except on tables where ts is declared as INT — which a lot of embedded schemas do, because timestamps-as-epoch-ints are the path of least resistance — the query took 1.2 seconds on 1M rows. And here is the detail that made it painful to find: the EXPLAIN output showed the top-k fast path as the chosen plan. Our top-k by timestamp reads column segments at three decode points. All three had the same assumption baked in: timestamps are stored with the GorillaTimestamp codec. But for INT columns we use DeltaVarint — a gorilla-style integer codec. When the top-k decoder met a DeltaVarint chunk, the code did the polite thing: js = continue, // not a timestamp chunk, skip Result: zero rows out of the fast path. No error. No log. An empty candidate list, which the executor treats as "fall back to the generic scan". That's bug number one, and it's the most dangerous kind: the fast path didn't crash, it quietly produced nothing . The fallback did the work of 1M decodes. Nobody noticed, because correctness was fine — the slow path is correct. It was only slow. Every columnar segment carries min/max metadata for the timestamp column, and scans use it to skip segments that can't contain rows in range. So what happens to that metadata when rows are still sitting in the write buffer? The buffer's time-tracking code updated stats when it saw Value::Timestamp . Our INT-table rows are Value::Integer . So the tracked range for those segments stayed at its initialization value: 0, 0 . Then the zone-map gate asked, for every segment: "could this segment contain recent timestamps?" A segment with min=0, max=0 containing the most recent data — pruned. All of them. The gate was doing exactly what it was designed to do, on metadata that silently never got populated. The fix is a semantic one, and it generalizes: 0, 0 is not "this segment has no recent data", it's "unknown" — and "unknown" must not prune. The gate now lets 0,0 segments through instead of discarding them. Robot writes land in a write buffer first and get folded into segments at checkpoint. snapshot rows has an in-range check to decide whether buffered uncommitted rows fall inside the query's time window. For Integer-backed buffers, that check was hardcoded: false // Integer buffers: never in range Meaning: the last few seconds of writes — precisely the rows that "ORDER BY ts DESC LIMIT 10" exists to return — were invisible to the top-k until a checkpoint ran. On a robot that checkpoints every 30 seconds, "the latest reading" could be half a minute stale, and nothing would tell you. While instrumenting all this, we found something worse. The Python binding's query entry point never routed time-series top-k to the fast kernel at all. EXPLAIN cheerfully printed the fast path as the chosen plan — but that was a paper plan. The streaming entry point was missing the routing branch, so every query from Python took the generic executor from the start. So we had: …while our EXPLAIN output told us everything was fine. The v0.12 fixes, briefly: 0,0 metadata no longer prunes; it's treated as unknown. in range handles Integer buffers; uncommitted rows are visible to top-k immediately. ORDER BY ts LIMIT k to the top-k kernel, and pass-2b backfills the ts values when the projection omitted the column. Results on 1M rows Apple Silicon, release build, reproducible via the repo's benchmark suite : | Query | Before | After | |---|---|---| | ORDER BY ts DESC LIMIT 10 | 1,196 ms | 1.76 ms | | DuckDB full scan reference | — | 2.1 ms | A 680× improvement, and the fast path now actually beats scanning everything — which is the entire point of a fast path. 1. A fast path without a differential test is a rumor. All four bugs kept every test green, because the slow path is correct. What caught the correctness half was our SQLite-as-oracle differential harness; what finally surfaced the performance half was an adversarial benchmark that compares the fast path's output and its timing against the generic path. If you maintain two code paths for the same query, they need to be diffed against each other on every commit — same inputs, same outputs, and the fast one had better actually be fast. 2. EXPLAIN is a claim, not a measurement. Bug 4 means our EXPLAIN output described a plan that never executed. Since then, any latency investigation starts with wall-clock timing per stage, and EXPLAIN gets checked against reality, not trusted. 3. Type-generality is a correctness surface. The root cause chain here is one abstraction leak: "timestamps" were treated as Timestamp in a dozen places, and every one of them needed a separate decision about what to do with Integer . Each place we made that decision independently, we made a different mistake. If your engine has a fast path per query shape × column type , budget test coverage proportional to that product — it grows fast. 4. "Unknown" must not prune. Any optimizer gate built on metadata needs a distinct answer for "this segment provably can't match" versus "we don't know". Collapsing both into a zero value turns missing stats into wrong results — in our case, into a silently empty newest segment. The top-k fix was one item in a large release. The highlights: Hybrid search — BM25 full-text and vector KNN fused with Reciprocal Rank Fusion in one call, no score calibration needed: python import motedb db = motedb.Database "robot.mote" db.execute "CREATE VECTOR INDEX ev emb ON events emb " db.execute "CREATE TEXT INDEX ev note ON events note " rows = db.hybrid search "ev note", "bearing noise", text list "ev emb", query vec.tolist , vector list k=10, each row carries rrf , bm25 , distance Arrow/pandas interop — query arrow returns a pyarrow.Table with VECTOR n columns mapped to Arrow's canonical fixed size list