Qirava DMS docs

Reading: sort-via-index (top-K)

When you ask for the top-K rows by an indexed column — SORT <indexed_col> [ASC|DESC] LIMIT k — the engine can answer without a full scan or a full sort. It walks the column's ordered BTreeMap index in the requested direction, applies any residual filter, dedupes rows that appear in multiple buckets, and early-stops once k rows are emitted. If the index runs out before k (for example, rows that lack the sort field are not in the index), it cleanly falls through to the full collect-and-sort. This page explains exactly when the fast path engages and the guarantees it provides.

When sort-via-index engages#

All of these must hold: there is exactly one SORT key; the query has a LIMIT; the projection contains no aggregate; the WHERE filter is NOT index-covered (so the candidate set is not already small — if it were, a plain sort is cheap); and there is a single-field equality index (Filter/Nested/Composite leading) on the sort field. When they hold, the engine iterates the index in sort order instead of scanning + sorting.

  DESC:  walk BTreeMap buckets high → low
  ASC :  walk BTreeMap buckets low → high
         ├─ for each position in bucket
         │    ├─ dedupe by _id (multi-valued List index)
         │    ├─ residual filter (if any)
         │    └─ emit shaped/projected row
         └─ stop as soon as k rows emitted

Top-K via ordered index

Direction, residual filter, early-stop#

For DESC the engine iterates the index buckets in reverse (high to low); for ASC, forward. Within each bucket it checks the residual WHERE (when present) and pushes matching rows into the output, stopping the moment the output reaches k. Because the index is already sorted, the first k matches walked ARE the top-k.

Request

-- table t, Filter index on `age`, rows: 5, 50, 20, 99, 10, 30
SELECT age FROM t SORT age DESC LIMIT 2
SELECT age FROM t SORT age ASC LIMIT 2

Response

// DESC LIMIT 2 -> [ { "age": 99 }, { "age": 50 } ]   count 2
// ASC  LIMIT 2 -> [ { "age": 5 },  { "age": 10 } ]   count 2

Dedupe for multi-valued (List) indexes#

A single-field index over a List column indexes the row under EACH element's key, so the same row can be reached through several buckets. The top-K walk dedupes by primary key (_id), emitting each row at most once. Rows that lack an _id cannot be deduped and are always emitted.

Request

-- a tags [x,y,z]; b tags [m,n]; Filter index on `tags`
SELECT name FROM p SORT tags ASC LIMIT 2
SELECT name FROM p SORT tags DESC LIMIT 2

Response

// both -> count 2; "a" appears once and "b" appears once (no per-element duplicates)

Fall-through when the index is exhausted#

Rows missing the sort field are not in the index. Under nulls-last ordering they sort AFTER indexed rows, so filling k from the index yields the exact top-k. But if the index runs out before k rows are emitted (for example, LIMIT is larger than the indexed row count), the fast path is abandoned and the query falls through to the normal collect-all-matches, sort, truncate path — which includes the missing-field rows.

Request

-- 6 rows have `age`; then insert one row with only `other: 1`
INSERT INTO t { other: 1 }
SELECT * FROM t SORT age DESC LIMIT 100

Response

// count: 7  (all rows, including the one missing `age`, which sorts last)