What a read reports

An answer here is never only the records. It carries how it was reached, and it carries a note when something happened that the records alone would not show.

The table this page reads, and the two records it reads:

DEFINE TABLE users (city string, name string, age any);
DEFINE INDEX by_city ON users FIELDS city;
CREATE users = { city: 'london', name: 'ada', age: 17 };
CREATE users = { name: 'grace', age: 'forty' };

age is declared any on purpose — the note further down is about what happens when one record holds a number there and another holds a string.

SELECT * FROM users WHERE city = 'london';

1: { age: 17, city: 'london', name: 'ada' }
(1 record(s), via index)

via index is the access path. There is one vocabulary for it across the whole language — record, index, ordered, scan, approximate, graph, join, materialised — and the same word appears whether you read it off an answer, ask for it in advance, or assert it with USING.

Asking in advance

EXPLAIN SELECT * FROM users WHERE city = 'london';

{ access: 'index', columns: 1, exact: true, index: 'by_city', shape: 'equality', table: 'users' }

EXPLAIN SELECT * FROM users WHERE name = 'ada';

{ access: 'scan', exact: true, table: 'users' }

EXPLAIN runs the planner and not the read, so it is free to run against production. The second one is the answer you were looking for when you asked: there is no index on name, so the read reads everything.

Asserting it

Knowing the plan today is worth less than being told when it changes. USING turns the plan into a condition of the read:

SELECT * FROM users WHERE name = 'ada' USING INDEX by_city;

Refused — IndexNotUsed.

error: this read was asked to use the index by_city and used no index

That is the point of the clause. A read that was fast because an index existed becomes a read that scans when somebody drops the index, renames a field, or changes the condition in a way that makes it unservable — and it stays correct the whole time, so nothing fails until the table is large enough for it to matter. USING fails immediately instead, at the moment the plan changed rather than at the moment the plan became painful.

Put it on the reads that must not silently degrade. It is an assertion, so it belongs where you would otherwise have written a comment saying "this must use the index".

Lifting the scan guard

The planner will not serve a read from an index that selects most of the table — past a threshold it takes the scan instead, because reading most of a table through an index is slower than reading it directly. That threshold is a policy this store chose, and a read may lift it:

SELECT * FROM events WHERE n >= 401 WITHOUT SCAN GUARD;

The guard compares a candidate against half the table, from a record count the store keeps exactly and, when the index has statistics, an estimate — counting the entries itself whenever the estimate is too close to call. The clause is spelled as lifting a guard rather than as overriding a number because what it changes is the policy, and a number it overrode could be right.

It lifts the veto and chooses nothing. The ranking still picks the candidate, a table with no applicable index still gets the scan, and EXPLAIN still reports what actually ran. This is deliberately not an override that names an index: a clause that named one would be a router, and a router owes an answer to what happens when the named index does not fit the predicate, how it composes with ranking, and what a plan means afterwards. A threshold raises none of those. The worst a misuse of this clause can do is the behaviour that shipped before the guard existed.

It is a separate clause rather than a word on USING INDEX, because that one is an assertion about what the read did and a modifier turning it into an instruction would be a pun a reader can miss.

It stays. It was once meant to be retired when the planner gained statistics. It has them, and the veto now compares an estimate from them with the table — a better estimate, but still an estimate, and a read whose estimate is wrong is exactly when lifting the veto is the fix. So from 0.26.0-beta the clause is a permanent part of the language rather than a hint with an end date. If a later planner drops the half-table veto altogether, the clause will still parse and do nothing.

Notes

A note is something the read has to tell you that the records do not show. It travels with the answer.

SELECT * FROM users WHERE age > 18;

2: { age: 'forty', name: 'grace' }
(1 record(s), via scan)
note: this read compared a number with a string, so it answered about the
records whose kinds happened to line up

Look at what came back. The table holds one person aged 17 and one whose age is the string 'forty', and age > 18 answered with the string — because a string orders after every number. Nothing failed. Without the note, this is a correct-looking answer to a question about ages that contains no ages.

Membership is judged by the members (from 0.26.0-beta): age IN [17, 18.0] compares a number with numbers and says nothing, while age IN ['17', '18'] says what it crossed. Before, every membership test named array and number, which is the note firing on a question that crossed nothing.

Another note to know by name is needs-rebuild (from 0.26.0-beta): a search index this read met may hold an earlier tokenizer's terms, so a field's index was not answered from — the read scanned — or a search's member was read and can miss records. It names the statement that rebuilds the index (why).

An answer that used approx_distinct or approx_quantile carries estimated (from 0.33.0-beta). It names the fold, the method (hll-14 or ddsketch) and the declared bound. Its collapsed is true when a quantile sketch had to fold buckets together, and the folded ranks are then outside that bound (folds that estimate).

The other note you will meet early is the materialised ceiling, covered in Reading from a read.

Notes are informative and the list of them is open — a node may send a kind your client has not seen. A client that refuses an unrecognised note kind is wrong; show it and carry on. Nothing about the answer depends on understanding the note.

Bounding the wait

The read is given a budget, and exceeding it says so. The budget here is one nanosecond, which is the only way to make a two-record table exceed one:

SELECT * FROM users TIMEOUT 1ns;

Refused — TimedOut.

error: the read passed its ceiling of 1ns after 1 records; it is refused rather
than answered in part

A refusal rather than a partial answer. That direction is deliberate: a truncated answer that looks whole is the failure this whole page is about, and a timeout that returned "what it had so far" would manufacture exactly that — a short list, no mark on it, from a query that usually returns a long one.

So TIMEOUT answers fully or not at all, and a refusal is something your code already has a branch for.

How many records the planner expects

From 0.22.0-beta the planner keeps statistics beside each value index — how many entries it holds, how many distinct values, its most common values and their counts, and buckets over its first field — and ranks candidates by how many records each is expected to reach. ANALYZE TABLE takes them now; a serving node also refreshes them by itself as indexes change:

ANALYZE TABLE users;
EXPLAIN SELECT * FROM users WHERE city = 'london';

[{ buckets: 0, common: 0, distinct: [1], entries: 1, index: 'by_city' }]
{ access: 'index', columns: 1, estimate: 1, estimated_by: 'statistics', exact: true, index: 'by_city', shape: 'equality', table: 'users' }

ANALYZE answers what it kept per index — the entries, the distinct count of each leading run of fields, how many common values and buckets — and estimate is the number of records the planner expects and estimated_by says where it came from: statistics, or probe when it counted the index's entries to decide whether the index beats reading the table. ANALYZE never prints a value it counted; the estimate is left out of a plan whose index reads a field the caller may not see.

An estimate chooses a path and never an answer. A statistic set aside once its index has changed by more than a tenth of its entries (or a thousand, whichever is more) is replaced by a count, and two nodes holding different statistics answer the same read with the same records. Statistics are a node's own: they are not in the log or in a backup.