Qirava DMS docs

DDL: CREATE INDEX (filter/sort/search/vector/graph/nested/composite)

Indexes are created with CREATE INDEX <name> ON <table> (<col>, ...) [<KIND>]. The kind selects how the index is built and what it accelerates: filter (default) for equality/range/array-contains, composite for multi-column leftmost-prefix lookups, nested for dotted-path fields, search for full-text, vector for ANN, and sort/graph as declared kinds. Most kinds are maintained as sorted BTreeMap secondary indexes; a unique index additionally enforces uniqueness on write. Indexes build eagerly over existing rows when created and stay maintained on every mutation. Created indexes are recorded in _sys_indexes. This page covers every kind and when to use each.

CREATE INDEX syntax#

CREATE INDEX <name> ON <table> (<col1>, <col2>, ...) [<KIND>]. The column list is parenthesized and comma-separated. The kind keyword is optional and defaults to filter. A composite index needs at least two columns. For schema tables, indexed fields must be declared columns; schemaless tables allow any field name (including dotted paths).

Request

CREATE INDEX by_age ON users (age) FILTER
CREATE INDEX i ON t (a, b)          -- kind omitted -> filter

Response

// each -> { "error": null, "data": null, "root": { "count": 0 } }

Index kinds and when to use each#

Kind	Use for	Maintained?
filter	equality (`= v`), ranges (`< <= > >=`), array-contains over a List column	yes (BTreeMap)
composite	multi-column leftmost-prefix equality lookups; optional FIFO bucket ordering	yes (BTreeMap)
nested	a dotted-path field (`addr.city`) treated as an equality/range index	yes (BTreeMap)
search	full-text SEARCH MATCH (inverted index, tokenized)	yes (BTreeMap)
vector	NEAREST ANN via random-hyperplane LSH buckets	yes (BTreeMap of bucket→positions)
sort	declared sort index (the sort-via-index path uses equality-kind indexes)	declared; not built as a BTreeMap unless unique
graph	declared graph index; edge `from`-index speeds TRAVERSE adjacency	declared; equality-kind index on `from` serves traversal

Unique indexes#

A unique index rejects an INSERT that would duplicate an existing key with a conflict error. Uniqueness is checked twice: once under a read lock before logging, and again under the write lock before applying (insert is the only grow path). A unique index is built and maintained even for kinds that are otherwise not BTreeMap-backed.

Request

-- users has a unique filter index on `email`
INSERT INTO users { email: "a@b" }   -- ok
INSERT INTO users { email: "a@b" }   -- conflict

Response

// second -> { "error": { "code": "conflict", "message": "unique index 'u_email' violated" }, ... }

Eager build and maintenance on mutation#

Creating an index on an existing table builds it eagerly over all current rows, so it immediately serves queries. After that, indexes are maintained incrementally: INSERT appends the new row's keys; UPDATE refreshes only the indexes whose fields changed (removing old keys, adding new ones); DELETE retains survivors and rebuilds the index (positions shift). Multi-valued List columns are indexed under each element's key, and search columns are re-tokenized on update.

Request

CREATE TABLE users SCHEMALESS
INSERT INTO users { name: "ada", age: 36 }
INSERT INTO users { name: "bob", age: 20 }
CREATE INDEX by_age ON users (age) FILTER
SELECT name FROM users WHERE age >= 30

Response

// index built over existing rows -> { "error": null, "data": [ { "name": "ada" } ], "root": { "count": 1 } }

Composite FIFO ordering#

A composite index can be marked FIFO so that a lookup supplying all its keys returns matches in insertion order — useful for relation/queue indexes. Index buckets preserve insertion order within an equal key.

Request

-- rel has a Composite FIFO index on (a, b)
INSERT INTO rel { a: 1, b: 2, tag: "x" }
INSERT INTO rel { a: 1, b: 2, tag: "y" }
INSERT INTO rel { a: 1, b: 3 }
SELECT * FROM rel WHERE a = 1 AND b = 2

Response

// count 2; "x" precedes "y" (insertion / FIFO order)

The _sys_indexes catalog#

Creating an index on a non-system table records a row in _sys_indexes with name, table, fields (comma-separated), and kind. Like _sys_tables, this lets a durable engine restore indexes on restart.

Request

SELECT name FROM _sys_indexes

Response

// after CREATE INDEX by_age ... -> data includes { "name": "by_age" }