# Three bugs that made our time-series queries 680 slower (and how we found them)

> Source: <https://dev.to/motedb/three-bugs-that-made-our-time-series-queries-680x-slower-and-how-we-found-them-4d8h>
> Published: 2026-10-07 08:09:19+00:00

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<float32>`; `insert_arrays` bulk-loads numpy arrays at ~200K rows/s with durability on.

**Filtered vector search that doesn't drop results** — `WHERE zone = 'bay_3' ORDER BY emb <-> ? LIMIT 10` used to filter *after* top-k, so a highly-selective predicate could return fewer rows than asked, or none. Candidate depth now deepens iteratively until it has k survivors.

**Scan UPDATE/DELETE predicate pushdown** — 112.6K rows/s, past SQLite's 110K at the same durability level.

**ANN tail latency** — p99 30ms → 1.5ms; the "tail" was cold page faults during the first ~30 queries after open, fixed with `madvise(WILLNEED)` warmup.

One breaking change to flag: multi-word `MATCH` now defaults to AND (SQLite FTS5-compatible). Use `MATCH('a OR b')` for the old behavior.

```
cargo add motedb          # Rust
pip install motedb-python # Python (macOS + Linux wheels)
```

Everything above is reproducible — the benchmark suite, the differential harness, and the full changelog live in the repo:

👉 **[github.com/motedb/motedb](https://github.com/motedb/motedb)** — MIT, pre-1.0, very active. Star it if embedded multimodal storage is your kind of problem, and tell us in the issues what workload you'd throw at it.

*What's the sneakiest silent-fallback bug you've shipped? Wrong answers (and right ones) in the comments — curious whether differential testing is common practice or still niche outside databases.*