Text search

Ranked words, typos, highlights and facets, then words and meaning in one ranking.

match ranks a collection's documents by how well their text matches your words, and the rest of a search box hangs off it: fuzzy and prefix for typos and words begun, highlight() and snippet() for where the words are, and facet for counts beside the page.

Run in the shell, answers and all; the timings are from one run.

fenec> create collection articles (title text @text, body text @text, year int @sorted, topic text @hash);
collection `articles` created
fenec> put articles [{title: "Ownership in Rust", body: "Ownership and borrowing keep memory safe without a garbage collector", year: 2023, topic: "rust"}, {title: "A tour of borrowing", body: "Borrowing lets a function read a value without taking ownership of it", year: 2024, topic: "rust"}, {title: "Garbage collection pauses", body: "A collector that pauses the program trades latency for convenience", year: 2021, topic: "runtime"}, {title: "Indexes for search", body: "An inverted index maps each word to the documents that hold it", year: 2024, topic: "search"}];
4 records (143.88µs)
fenec> get articles select title match body "borrowing"
title                _score
-------------------  ------
Ownership in Rust    0.7053
A tour of borrowing  0.6814
(2 rows, 377.67µs)
fenec> get articles select title where year >= 2024 match body "ownership"
title                _score
-------------------  ------
A tour of borrowing  0.6814
(1 rows, 503.92µs)
fenec> get articles select title match title "ownrship" fuzzy
title              _score
-----------------  ------
Ownership in Rust  1.2218
(1 rows, 345.96µs)
fenec> get articles select title, highlight(body) match body "borrowing"
title                highlight(body)  _score
-------------------  ---------------  ------
Ownership in Rust    [[14, 23]]       0.7053
A tour of borrowing  [[0, 9]]         0.6814
(2 rows, 450.00µs)
fenec> get articles select title match body "collector" facet topic
title                      _score
-------------------------  ------
Ownership in Rust          0.7053
Garbage collection pauses  0.7053
(2 rows, 432.21µs)
facet topic: runtime 1, rust 1

match needs a @text index and adds a _score column. A where narrows the rows while they are ranked; fuzzy finds ownrship's word; highlight answers the offsets of the words in a row's text; and facet counts the values of a field over every matched row, beside the rows (the shell prints them on the last line). Words and meaning in one ranking, fuse, are under Hybrid search; the vectors are on Vector search.

The text clause

match ranks documents by how well their text matches your words. rerank then reorders the best of them by vector distance.

get articles match body $1 limit 10
get articles where year >= 2024 match body $1 limit 10
get articles match body $1 rerank embed $2 candidates 500 limit 10

import { connect } from '@fenecdb/web/client'

await db.from('articles').match('body', 'ownership').limit(10).rows();

await db.from('articles')
  .where('year', '>=', 2024)
  .match('body', 'ownership')
  .limit(10)
  .rows();

await db.from('articles')
  .match('body', 'ownership')
  .rerank('embed', [0.9, 0.1, 0], { candidates: 500 })
  .limit(10)
  .rows();

from fenecdb import Client

db.collection("articles").match("body", "ownership").limit(10).rows()

(db.collection("articles")
    .where("year", ">=", 2024)
    .match("body", "ownership")
    .limit(10)
    .rows())

(db.collection("articles")
    .match("body", "ownership")
    .rerank("embed", [0.9, 0.1, 0], candidates=500)
    .limit(10)
    .rows())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("articles").Match("body", "ownership").Limit(10).Rows(ctx)

db.From("articles").
	Where("year", ">=", 2024).
	Match("body", "ownership").
	Limit(10).
	Rows(ctx)

db.From("articles").
	Match("body", "ownership").
	Rerank("embed", []float64{0.9, 0.1, 0}, fenecdb.Candidates(500)).
	Limit(10).
	Rows(ctx)

using FenecDb;

await db.From("articles")
    .Match("body", "ownership")
    .Limit(10)
    .RowsAsync();

await db.From("articles")
    .Where("year", ">=", 2024)
    .Match("body", "ownership")
    .Limit(10)
    .RowsAsync();

await db.From("articles")
    .Match("body", "ownership")
    .Rerank("embed", new[] { 0.9, 0.1, 0 }, candidates: 500)
    .Limit(10)
    .RowsAsync();

import FenecDB

try await db.from("articles")
    .match("body", "ownership")
    .limit(10)
    .rows()

try await db.from("articles")
    .where("year", ">=", 2024)
    .match("body", "ownership")
    .limit(10)
    .rows()

try await db.from("articles")
    .match("body", "ownership")
    .rerank("embed", [0.9, 0.1, 0], candidates: 500)
    .limit(10)
    .rows()

import com.fenecdb.*

db.from("articles").match("body", "ownership").limit(10).rows()

db.from("articles")
    .where("year", ">=", 2024)
    .match("body", "ownership")
    .limit(10)
    .rows()

db.from("articles")
    .match("body", "ownership")
    .rerank("embed", listOf(0.9, 0.1, 0.0), candidates = 500)
    .limit(10)
    .rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('articles').match('body', 'ownership').limit(10).rows();

await db.from('articles')
    .where('year', '>=', 2024)
    .match('body', 'ownership')
    .limit(10)
    .rows();

await db.from('articles')
    .match('body', 'ownership')
    .rerank('embed', [0.9, 0.1, 0], candidates: 500)
    .limit(10)
    .rows();

sends

get articles match body $1 limit $2
-- ["ownership", 10]
get articles where year >= $1 match body $2 limit $3
-- [2024, "ownership", 10]
get articles match body $1 rerank embed $2 candidates 500 limit $3
-- ["ownership", [0.9, 0.1, 0], 10]

match needs a @text index and adds a _score column.
rerank needs no vector index. It reads the vectors of the matched rows directly.
Reranking cannot find what the words missed. For that, use hybrid search.

How match filters, and what rerank is for

match ranks by BM25 over a @text index and adds a _score column. Like near it decides the ordering, so it combines with neither order nor near — unless fuse says how the two rankings make one (below). A where filter is applied while the postings are merged, so it costs no second pass.

rerank reorders what match found by exact vector distance. It needs match — there is nothing to reorder without it — but it does not need an @hnsw index: the vectors are read straight out of the store, so this path builds, holds and validates no graph at all. candidates sets how many of the matched rows are rescored (default 200); it is never allowed below limit + offset.

The two stages answer different questions. match is recall and knows nothing about meaning; rerank is precision but only over what the first stage handed it. Measured on BEIR: on SciFact 50 candidates score nDCG@10 0.656 against 0.645 for a full dense scan of the same vectors, and on FiQA 1 000 candidates come within 0.0001 of the full scan — each while scoring under 2% of the corpus. If the lexical stage cannot find the answer at all, no amount of reranking will.

Typos and words begun

fuzzy lets a word of a match find the words a typo or two away from it, and prefix lets the last word find the words it begins — what a search box needs as someone types into it.

get products match title $1 fuzzy limit 10             -- "databse" finds "database"
get products match title $1 prefix limit 10            -- "datab" finds "database", "databases"
get products match title $1 fuzzy prefix limit 10      -- both, the last word a misspelt beginning
get products match title $1 fuzzy 1 limit 10           -- one edit at most

A word's length decides its edits: none under 5 characters, one from 5 to 8, two from 9. fuzzy 1 allows one at most, fuzzy 0 none.
A word as typed ranks first. A document holding the word itself scores more from it than one holding only a word near it.
A word reaches 10 000 words of the index at most, and the query is refused past that rather than cut — a prefix of one letter over a large vocabulary is.
highlight() and snippet() mark what was found: databse marks database.

How an edit is counted, and the BEIR results

An edit is a letter added, dropped, changed, or two letters next to each other swapped — Damerau's distance in its restricted form, as Lucene, Elasticsearch and Meilisearch count it: the four are what Damerau found four misspellings in five to be, and a swap counted as two edits would put a five-letter word with one out of reach. An edit that touches a word's first letter counts two, as Meilisearch counts it: a first letter is rarely the mistyped one, and letting it be multiplies what is found by words no one meant. The bounds by length are Meilisearch's too: a short word has too many neighbours — cart is one letter from card, care, cars, cat and art. Words are folded as the index folds them before they are compared, so İSTANBL finds istanbul. Only the last word is a prefix; the words before it are whole.

The words a word reaches count as one term of the query, a document's counts of them summed, so BM25's saturation holds over them as over a word written three times, and the term's rarity is theirs together — a rare misspelling in the corpus ranks no higher than the word it misspells. A word one edit away, or one a prefix begins, counts as the word would; two edits away, a quarter as much. Where the index holds the word as typed, its variants together are kept below the least it adds to a document, so a one-word query ranks every document holding it above every document holding only what is near it. Measured on BEIR with one typo put into each query, a word one edit away was the word meant and two edits away as often another word, which the weights follow.

The words are found in a sorted copy of the index's words, made the first time a match asks for fuzzy or prefix and kept up by every write after it, as the index is: derived, never in the file. A prefix is a range of it; the words within a few edits are found by walking it as the tree of prefixes it is, giving a branch up once no word below it can be near enough. Over 100 000 documents of 60 000 words, sorting them took 6 ms and 1.3 MB; finding a word's neighbours over FiQA's 135 500 words, 14 µs at one edit and 120 at two, its completions 4 µs.

Measured on BEIR, nDCG@10 (make beir), one realistic typo put into a word of each query — a letter dropped, a key beside it typed as well or instead, or two letters swapped, the first letter 7% of the time: SciFact's queries as written score 0.662 and 0.665 with fuzzy; with the typo 0.636, and 0.663 with fuzzy. FiQA's 0.232 and 0.233; with the typo 0.213, and 0.229. With each query's last word half typed, prefix takes SciFact from 0.616 to 0.655 and FiQA from 0.188 to 0.208, and leaves the queries written whole where they were. See the whole table.

Hybrid search

fuse combines a text search and a vector search into one ranking. Use it when some answers share your words and others only your meaning.

A filter beside near is another thing, though it is also called a hybrid query: A hybrid query on Vector search.

get articles match body $1 near embed $2 fuse limit 10
get articles where year >= 2024 match body $1 near embed $2 fuse limit 10
get articles match body $1 near embed $2 fuse k 60 candidates 50 limit 10

It adds ranks, not scores, so there is no weight to tune.
It needs an @hnsw index for the vector side.

How fuse adds ranks, and the BEIR results

fuse lets match and near rank the same query together. Each side takes its own first candidates — 20 unless given, never fewer than limit + offset — with the where filter applied to both, and a document scores 1 / (k + rank) from each list it is on, k being 60 unless given. The sum is the _score; ties go to the lower id.

Ranks rather than scores, because the two have no scale in common: a BM25 score grows with the length of the query and the rarity of its words, a cosine distance does neither, and a weighted sum would need its weight tuned again for every corpus. Where rerank can only reorder what the words found, fuse also takes what they missed — at the price of the @hnsw graph near walks.

Measured on BEIR, nDCG@10, all-MiniLM-L6-v2 vectors (the whole table). On SciFact, where a claim shares its words with the evidence, fuse scores 0.700 against 0.662 for match and 0.645 for near alone. On FiQA, where a question shares few words with its answer, it scores 0.366 against 0.232 and 0.366: the weak side does not drag the strong one down. Up to 61 candidates a side at k = 60, a document both searches found outranks every document only one found; deeper, agreement in the middle of both lists starts to outvote the top of one, and at 100 a side the two scores fall to 0.688 and 0.359.

Highlights and snippets

highlight() and snippet() in the select list show where the words of a match are in a row's text, so a results page can mark them.

get articles select title, highlight(body) match body $1 limit 10
get articles select title, highlight(body, "<mark>", "</mark>") match body $1 limit 10
get articles select title, snippet(body, 20, "…") match body $1 limit 10
get articles select title, snippet(body, 20, $2, $3, $4) match body $1 limit 10

import { connect } from '@fenecdb/web/client'

await db.from('articles')
  .select('title')
  .highlight('body')
  .match('body', 'ownership')
  .limit(10)
  .rows();

await db.from('articles')
  .select('title')
  .highlight('body', { pre: '<mark>', post: '</mark>' })
  .match('body', 'ownership')
  .limit(10)
  .rows();

await db.from('articles')
  .select('title')
  .snippet('body', 20, { ellipsis: '…' })
  .match('body', 'ownership')
  .limit(10)
  .rows();

await db.from('articles')
  .select('title')
  .snippet('body', 20, { ellipsis: '…', pre: '<b>', post: '</b>' })
  .match('body', 'ownership')
  .limit(10)
  .rows();

from fenecdb import Client

(db.collection("articles")
    .select("title")
    .highlight("body")
    .match("body", "ownership")
    .limit(10)
    .rows())

(db.collection("articles")
    .select("title")
    .highlight("body", pre="<mark>", post="</mark>")
    .match("body", "ownership")
    .limit(10)
    .rows())

(db.collection("articles")
    .select("title")
    .snippet("body", 20, ellipsis="…")
    .match("body", "ownership")
    .limit(10)
    .rows())

(db.collection("articles")
    .select("title")
    .snippet("body", 20, ellipsis="…", pre="<b>", post="</b>")
    .match("body", "ownership")
    .limit(10)
    .rows())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("articles").
	Select("title").
	Highlight("body").
	Match("body", "ownership").
	Limit(10).
	Rows(ctx)

db.From("articles").
	Select("title").
	Highlight("body", fenecdb.Tags("<mark>", "</mark>")).
	Match("body", "ownership").
	Limit(10).
	Rows(ctx)

db.From("articles").
	Select("title").
	Snippet("body", 20, fenecdb.Ellipsis("…")).
	Match("body", "ownership").
	Limit(10).
	Rows(ctx)

db.From("articles").
	Select("title").
	Snippet("body", 20, fenecdb.Ellipsis("…"), fenecdb.Tags("<b>", "</b>")).
	Match("body", "ownership").
	Limit(10).
	Rows(ctx)

using FenecDb;

await db.From("articles")
    .Select("title")
    .Highlight("body")
    .Match("body", "ownership")
    .Limit(10)
    .RowsAsync();

await db.From("articles")
    .Select("title")
    .Highlight("body", pre: "<mark>", post: "</mark>")
    .Match("body", "ownership")
    .Limit(10)
    .RowsAsync();

await db.From("articles")
    .Select("title")
    .Snippet("body", 20, ellipsis: "…")
    .Match("body", "ownership")
    .Limit(10)
    .RowsAsync();

await db.From("articles")
    .Select("title")
    .Snippet("body", 20, ellipsis: "…", pre: "<b>", post: "</b>")
    .Match("body", "ownership")
    .Limit(10)
    .RowsAsync();

import FenecDB

try await db.from("articles")
    .select("title")
    .highlight("body")
    .match("body", "ownership")
    .limit(10)
    .rows()

try await db.from("articles")
    .select("title")
    .highlight("body", pre: "<mark>", post: "</mark>")
    .match("body", "ownership")
    .limit(10)
    .rows()

try await db.from("articles")
    .select("title")
    .snippet("body", 20, ellipsis: "…")
    .match("body", "ownership")
    .limit(10)
    .rows()

try await db.from("articles")
    .select("title")
    .snippet("body", 20, ellipsis: "…", pre: "<b>", post: "</b>")
    .match("body", "ownership")
    .limit(10)
    .rows()

import com.fenecdb.*

db.from("articles")
    .select("title")
    .highlight("body")
    .match("body", "ownership")
    .limit(10)
    .rows()

db.from("articles")
    .select("title")
    .highlight("body", pre = "<mark>", post = "</mark>")
    .match("body", "ownership")
    .limit(10)
    .rows()

db.from("articles")
    .select("title")
    .snippet("body", 20, ellipsis = "…")
    .match("body", "ownership")
    .limit(10)
    .rows()

db.from("articles")
    .select("title")
    .snippet("body", 20, ellipsis = "…", pre = "<b>", post = "</b>")
    .match("body", "ownership")
    .limit(10)
    .rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('articles')
    .select(['title'])
    .highlight('body')
    .match('body', 'ownership')
    .limit(10)
    .rows();

await db.from('articles')
    .select(['title'])
    .highlight('body', pre: '<mark>', post: '</mark>')
    .match('body', 'ownership')
    .limit(10)
    .rows();

await db.from('articles')
    .select(['title'])
    .snippet('body', 20, ellipsis: '…')
    .match('body', 'ownership')
    .limit(10)
    .rows();

await db.from('articles')
    .select(['title'])
    .snippet('body', 20, ellipsis: '…', pre: '<b>', post: '</b>')
    .match('body', 'ownership')
    .limit(10)
    .rows();

sends

get articles select title, highlight(body) match body $1 limit $2
-- ["ownership", 10]
get articles select title, highlight(body, $1, $2) match body $3 limit $4
-- ["<mark>", "</mark>", "ownership", 10]
get articles select title, snippet(body, 20, $1) match body $2 limit $3
-- ["…", "ownership", 10]
get articles select title, snippet(body, 20, $1, $2, $3) match body $4 limit $5
-- ["…", "<b>", "</b>", "ownership", 10]

Without tags you get positions: highlight(body) is a list of [start, end] pairs; snippet(body, 20) is {"text": …, "marks": [[start, end], …]}.
With tags you get the marked text, each mark between the two values you pass — literals or parameters. The engine adds no markup of its own and escapes nothing.
Offsets count UTF-16 code units, what a JavaScript, Java, Kotlin, C#, Dart or Swift NSString index counts: text.slice(start, end) is the marked word.
They need match, and go with fuse and rerank; a query without match has no words to mark, and is refused.

How marks are found, and what they cost

A row's text is read again at query time by the tokenizer of the @text index the match searched — the same splitting, the same folding (İstanbul, ISTANBUL and istanbul are one word), the same prefixes and runs of characters — and every span a term of the query was read from is marked. So a mark is exactly what the index matched: a word; the whole word a prefix of @text(prefix=N) was cut from (kitapların for kitap); the characters of a pair or a triple of Han, kana, Hangul or Thai, overlapping pairs made one mark (東京都 for 東京都). Nothing is kept in the index for it, and the file does not change. Marks come in order and never overlap; a mark never ends inside a character, and takes the marks that belong to the characters at its ends — a combining accent, a Thai tone mark, the variation selector and keycap after a digit — so tagged text never splits a letter from its accent.

Any text field can be marked, read by the tokenizer of the field match searched: select highlight(title) beside match body $1 marks the title's words the query holds. Under fuse a row only near found holds no word of the query and has no marks; a null field is null. Each item answers under its label, highlight(body) or snippet(body), after the fields select names, where the list puts it — and after every field with select *, highlight(body). The same item twice is refused: the two columns would answer to one name.

A snippet is the window of N words holding the most marks, centred on the first and the last of them — the first N words when none is marked — each character of a run of Han, kana or Hangul counted a word. The ellipsis, when given, stands for what it leaves out at either end; a window holding the start or the end of the text has none there. Its marks count from the start of its text, the ellipsis included.

The text is not escaped. A page that puts a marked text into HTML lets the text's own < through. Build the page from the offsets instead — the text as text nodes, each mark a <mark> element — or escape the text first and the tags after.

Over 100 000 documents of 30 words, a mark costs a row about 1 to 3 µs (2 to 4 for a snippet) on top of the match; a query that asks none runs as it did, match 0.06 ms either way (make search-bench).

facet counts the values of a field over every row the query matches — not only the page — for a filter sidebar: how many results each brand, color or tag has.

get products where price < 500 limit 20 facet brand, color
get products where price < 500 match title $1 limit 20 facet brand top 10, tags
get products where category = "phones" count facet brand
get products limit 0 facet meta.origin top 5

import { connect } from '@fenecdb/web/client'

await db.from('products')
  .where('price', '<', 500)
  .limit(20)
  .facet('brand')
  .facet('color')
  .rows();

await db.from('products')
  .where('price', '<', 500)
  .match('title', 'tent')
  .limit(20)
  .facet('brand', { top: 10 })
  .facet('tags')
  .rows();

await db.from('products')
  .where('category', 'phones')
  .facet('brand')
  .count();

await db.from('products')
  .limit(0)
  .facet('meta.origin', { top: 5 })
  .rows();

from fenecdb import Client

(db.collection("products")
    .where("price", "<", 500)
    .limit(20)
    .facet("brand")
    .facet("color")
    .rows())

(db.collection("products")
    .where("price", "<", 500)
    .match("title", "tent")
    .limit(20)
    .facet("brand", top=10)
    .facet("tags")
    .rows())

(db.collection("products")
    .where("category", "phones")
    .facet("brand")
    .count())

db.collection("products").limit(0).facet("meta.origin", top=5).rows()

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("products").
	Where("price", "<", 500).
	Limit(20).
	Facet("brand").
	Facet("color").
	Rows(ctx)

db.From("products").
	Where("price", "<", 500).
	Match("title", "tent").
	Limit(20).
	Facet("brand", fenecdb.Top(10)).
	Facet("tags").
	Rows(ctx)

db.From("products").
	Where("category", "=", "phones").
	Facet("brand").
	Count(ctx)

db.From("products").
	Limit(0).
	Facet("meta.origin", fenecdb.Top(5)).
	Rows(ctx)

using FenecDb;

await db.From("products")
    .Where("price", "<", 500)
    .Limit(20)
    .Facet("brand")
    .Facet("color")
    .RowsAsync();

await db.From("products")
    .Where("price", "<", 500)
    .Match("title", "tent")
    .Limit(20)
    .Facet("brand", top: 10)
    .Facet("tags")
    .RowsAsync();

await db.From("products")
    .Where("category", "phones")
    .Facet("brand")
    .CountAsync();

await db.From("products")
    .Limit(0)
    .Facet("meta.origin", top: 5)
    .RowsAsync();

import FenecDB

try await db.from("products")
    .where("price", "<", 500)
    .limit(20)
    .facet("brand")
    .facet("color")
    .rows()

try await db.from("products")
    .where("price", "<", 500)
    .match("title", "tent")
    .limit(20)
    .facet("brand", top: 10)
    .facet("tags")
    .rows()

try await db.from("products")
    .where("category", "phones")
    .facet("brand")
    .count()

try await db.from("products")
    .limit(0)
    .facet("meta.origin", top: 5)
    .rows()

import com.fenecdb.*

db.from("products")
    .where("price", "<", 500)
    .limit(20)
    .facet("brand")
    .facet("color")
    .rows()

db.from("products")
    .where("price", "<", 500)
    .match("title", "tent")
    .limit(20)
    .facet("brand", top = 10)
    .facet("tags")
    .rows()

db.from("products").where("category", "phones").facet("brand").count()

db.from("products").limit(0).facet("meta.origin", top = 5).rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('products')
    .where('price', '<', 500)
    .limit(20)
    .facet('brand')
    .facet('color')
    .rows();

await db.from('products')
    .where('price', '<', 500)
    .match('title', 'tent')
    .limit(20)
    .facet('brand', top: 10)
    .facet('tags')
    .rows();

await db.from('products')
    .where('category', 'phones')
    .facet('brand')
    .count();

await db.from('products').limit(0).facet('meta.origin', top: 5).rows();

sends

get products where price < $1 limit $2 facet brand, color
-- [500, 20]
get products where price < $1 match title $2 limit $3 facet brand top $4, tags
-- [500, "tent", 20, 10]
get products where category = $1 count facet brand
-- ["phones"]
get products limit $1 facet meta.origin top $2
-- [0, 5]

{"rows": [ ... ],
 "facets": {"brand": [{"value": "acme", "count": 12}, {"value": "nova", "count": 7}],
            "color": [{"value": "red", "count": 9}, {"value": null, "count": 2}]}}

Most first, ties by value; top N keeps the commonest N.
A list counts each value it holds: a row tagged ["sale", "new"] counts once under each.
null is a value, so a field's counts add up to the rows.
They come back beside the rows, never in one: the answer is {"rows": [...], "facets": {...}} when a query asks for facets, and the bare list of rows when it does not.

Which rows a facet counts, and the cost

The rows counted are the rows the query selects before offset and limit: those the where keeps, and with match, those holding a word of the query (rerank reorders them and counts the same). A lookup ... required decides who is counted, as it decides who is on the page; with count the facets go beside the number. near ranks every row the filter passes rather than selecting some, so counts beside it would be the filter's alone whatever the vector: a facet with near or fuse is refused. Ask them without near, in the same /batch.

A field is a field or a path into a json one. The counts are group ... count's over the same filter, value for value (tests/facets.rs holds them to it), but for a list, counted a value at a time, and a path, which group does not read. A @hash field of text, int, bool or timestamp is counted from its buckets — over every row a bucket's length, under a filter how many of a bucket's rows the set holds — and anything else reads the field of every matched row. Without top a facet answers every value it found, and one holding more than 10 000 is refused rather than cut: a list cut where it happened to stop is a wrong answer believed right.

Over 100 000 rows (make search-bench): a @hash field over every row 0.01 ms, read from every row 6.1 ms (a list of tags 17); under a filter keeping half, 5.8 and 9.3 ms against the filter's own 5.9 ms for a count. Beside a match reaching about 16 667 rows, two facets took a query from 0.06 to 1.6 ms.

With a brand chosen, a sidebar's "Brand" should still list the other brands to add, each with the count it would have. disjunctive counts a facet over the rows the query selects with the filter's own conditions on that field left out; the page, and every other facet, stay the query's.

get products where category = "tents" and brand in ["acme", "nova"] and color = "red"
  order price asc limit 24
  facet brand disjunctive, color disjunctive, material

import { connect } from '@fenecdb/web/client'

await db.from('products')
  .where('category', 'tents')
  .where('brand', 'in', ['acme', 'nova'])
  .where('color', 'red')
  .order('price')
  .limit(24)
  .facet('brand', { disjunctive: true })
  .facet('color', { disjunctive: true })
  .facet('material')
  .rows();

from fenecdb import Client

(db.collection("products")
    .where("category", "tents")
    .where("brand", "in", ["acme", "nova"])
    .where("color", "red")
    .order("price")
    .limit(24)
    .facet("brand", disjunctive=True)
    .facet("color", disjunctive=True)
    .facet("material")
    .rows())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("products").
	Where("category", "=", "tents").
	Where("brand", "in", []any{"acme", "nova"}).
	Where("color", "=", "red").
	Order("price", "asc").
	Limit(24).
	Facet("brand", fenecdb.Disjunctive()).
	Facet("color", fenecdb.Disjunctive()).
	Facet("material").
	Rows(ctx)

using FenecDb;

await db.From("products")
    .Where("category", "tents")
    .Where("brand", "in", new object?[] { "acme", "nova" })
    .Where("color", "red")
    .Order("price")
    .Limit(24)
    .Facet("brand", disjunctive: true)
    .Facet("color", disjunctive: true)
    .Facet("material")
    .RowsAsync();

import FenecDB

try await db.from("products")
    .where("category", "tents")
    .where("brand", "in", ["acme", "nova"])
    .where("color", "red")
    .order("price")
    .limit(24)
    .facet("brand", disjunctive: true)
    .facet("color", disjunctive: true)
    .facet("material")
    .rows()

import com.fenecdb.*

db.from("products")
    .where("category", "tents")
    .where("brand", "in", listOf("acme", "nova"))
    .where("color", "red")
    .order("price")
    .limit(24)
    .facet("brand", disjunctive = true)
    .facet("color", disjunctive = true)
    .facet("material")
    .rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('products')
    .where('category', 'tents')
    .where('brand', 'in', ['acme', 'nova'])
    .where('color', 'red')
    .order('price')
    .limit(24)
    .facet('brand', disjunctive: true)
    .facet('color', disjunctive: true)
    .facet('material')
    .rows();

sends

get products where category = $1 and brand in [$2, $3] and color = $4 order price asc limit $5 facet brand disjunctive, color disjunctive, material
-- ["tents", "acme", "nova", "red", 24]

Here brand counts over the red tents of every brand, color over the acme and nova tents of every colour, and material over the page's rows. A condition left out is a term of the where's and chain that reads the field alone — brand = ..., brand in [...], (brand = "a" or brand = "b"), a range — and one reading the field beside another (brand = "a" or sale) is refused rather than guessed at. A token's rules and a collection's expiry are never left out: they are ANDed into each facet's own filter too.

ranges [b1, b2, ...] counts the rows whose number falls in each range — from a bound, included, to the next, excluded — rather than by value: every range, in order, an empty one with 0, its value the [from, to] pair. A null, a NaN and a number outside every range count in none; a list counts once in each range a value of it falls in.

get products where category = "tents" limit 24
  facet price ranges [0, 2500, 5000, 10000, 25000] disjunctive

import { connect } from '@fenecdb/web/client'

await db.from('products')
  .where('category', 'tents')
  .limit(24)
  .facet('price', { ranges: [0, 2500, 5000, 10000, 25000], disjunctive: true })
  .rows();

from fenecdb import Client

(db.collection("products")
    .where("category", "tents")
    .limit(24)
    .facet("price", ranges=[0, 2500, 5000, 10000, 25000], disjunctive=True)
    .rows())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("products").
	Where("category", "=", "tents").
	Limit(24).
	Facet(
		"price",
		fenecdb.Ranges(0, 2500, 5000, 10000, 25000),
		fenecdb.Disjunctive(),
	).
	Rows(ctx)

using FenecDb;

await db.From("products")
    .Where("category", "tents")
    .Limit(24)
    .Facet("price", ranges: [0, 2500, 5000, 10000, 25000], disjunctive: true)
    .RowsAsync();

import FenecDB

try await db.from("products")
    .where("category", "tents")
    .limit(24)
    .facet("price", ranges: [0, 2500, 5000, 10000, 25000], disjunctive: true)
    .rows()

import com.fenecdb.*

db.from("products")
    .where("category", "tents")
    .limit(24)
    .facet(
        "price",
        ranges = listOf(0, 2500, 5000, 10000, 25000),
        disjunctive = true
    )
    .rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('products')
    .where('category', 'tents')
    .limit(24)
    .facet('price', ranges: [0, 2500, 5000, 10000, 25000], disjunctive: true)
    .rows();

sends

get products where category = $1 limit $2 facet price ranges [0, 2500, 5000, 10000, 25000] disjunctive
-- ["tents", 24]

"price": [{"value": [0, 2500], "count": 31}, {"value": [2500, 5000], "count": 112},
          {"value": [5000, 10000], "count": 204}, {"value": [10000, 25000], "count": 96}]

The bounds are numbers written out, rising, two to 10 001 of them, and no top goes with them. A @sorted field counts them from its index when every bound is a key it holds exactly — over every row two searches a range and the lengths of the chunks between, under a filter its entries walked against the filter's rows — and anything else reads the field of every row (12.5 against an int field is read).

Ranges and facets, measured on 100 000 rows

Over 100 000 rows (make search-bench, an M1 Air): six ranges of a @sorted price over every row 0.004 ms against 4.7 ms read from every row; under a filter keeping 3%, 0.15 against 0.19 ms; keeping half, 5.1 against 8.1. A category page with a brand and a colour chosen, its page and two disjunctive facets beside a range facet in one statement, took 0.75 ms in process against 0.75 ms for the three statements it was before; over HTTP 0.95 ms against 1.19 for the three in turn and 0.61 for the three at once on three connections. What one statement buys is one request, one connection and counts read under one lock — the same rows every facet counted — not less work.

Text search

The text clause

Typos and words begun

Hybrid search

Highlights and snippets

Facets

Counted without its own filter

Ranges