Full-Text Search¶

Query Lance tables using full-text search (FTS) with the lance_match, lance_match_phrase, and lance_multi_match SQL functions. These functions push FTS predicates down to the Lance inverted index for efficient text search.

lance_match¶

Keyword search against a single FTS-indexed column.

lance_match(column, query)
lance_match(column, query, 'key=value,...')

Arguments:

Options:

Examples:

-- Basic keyword search
SELECT * FROM lance.db.documents WHERE lance_match(body, 'machine learning');

-- Fuzzy search with AND operator
SELECT * FROM lance.db.documents
WHERE lance_match(body, 'machin lerning', 'fuzziness=1,operator=AND');

-- Boosted search with prefix length
SELECT * FROM lance.db.documents
WHERE lance_match(body, 'spark', 'boost=2.0,prefix_length=2');

lance_match_phrase¶

Phrase search against a single FTS-indexed column. Returns rows where the query terms appear consecutively (or within a slop distance).

lance_match_phrase(column, query)
lance_match_phrase(column, query, slop)

Arguments:

Examples:

-- Exact phrase match
SELECT * FROM lance.db.documents WHERE lance_match_phrase(body, 'machine learning');

-- Phrase match allowing 2 words between terms
SELECT * FROM lance.db.documents WHERE lance_match_phrase(body, 'deep networks', 2);

lance_multi_match¶

Search across multiple FTS-indexed columns with a single query. A row matches if any column matches (OR, default) or all columns match (AND).

lance_multi_match(query, col1, col2, ...)
lance_multi_match(query, 'operator=AND|OR', col1, col2, ...)

Arguments:

When the second argument is a string literal containing = with a recognized option key, it is treated as an options string. Otherwise it is treated as the first column name.

Examples:

-- Search across title and body (OR — row matches if either column matches)
SELECT * FROM lance.db.documents
WHERE lance_multi_match('machine learning', title, body);

-- AND operator — row must match in all columns
SELECT * FROM lance.db.documents
WHERE lance_multi_match('machine learning', 'operator=AND', title, body);

Relevance Score (_score)¶

The _score metadata column exposes the BM25 relevance score computed by the FTS index. It is available only when a full-text search predicate is active.

-- Project _score alongside data columns
SELECT id, body, _score FROM lance.db.documents
WHERE lance_match(body, 'machine learning');

-- Sort by relevance
SELECT id, _score FROM lance.db.documents
WHERE lance_match(body, 'vector database')
ORDER BY _score DESC;

-- Combine with scalar filters
SELECT id, _score FROM lance.db.documents
WHERE lance_match(body, 'deep learning') AND year >= 2024
ORDER BY _score DESC;

_score is a hidden metadata column — it does not appear in SELECT * output. Selecting or filtering on this metadata column without an FTS predicate raises an error at planning time.

If the table has a stored column named _score, Spark resolves _score to that data column instead. It remains visible in SELECT * and supports ordinary reads, filters, and ordering without FTS. Full-text search, including SEARCH and HYBRID_SEARCH, rejects tables with a stored _score column of any type, even when the column is not projected. Rename the stored column to use FTS and access relevance scores.

Known Limitations¶

• No local pushed ranked retrieval. Local per-fragment scans score all matching rows before Spark applies ORDER BY _score DESC LIMIT k.
• Column names must match the schema exactly. Column references are resolved at planning time; aliases or expressions are not supported.
• lance_match_phrase requires positional index. The FTS index must be built with with_position = true. Without it, phrase queries will fail.
• Unbounded WHERE-filter queries return every match. Without a supported namespace top-k push, every matching row is evaluated. wand_factor is not exposed.
• One FTS predicate per query. Only a single FTS function call is allowed per WHERE clause. For multi-column search, use lance_multi_match instead of combining multiple lance_match calls with OR.
• FTS predicates cannot appear inside OR. WHERE lance_match(a, 'x') OR other_condition is not supported — OR semantics cannot be preserved when pushing a single FTS query to the scanner.
• Query-serving namespaces must implement structured FTS queries. The directory namespace supports this request format. Custom REST implementations must evaluate the structured full-text query rather than ignore it. Catalog-only namespaces (Glue, Hive, Iceberg) fall back to local per-fragment scans.

Cost Model¶

On the local per-fragment scan path, each Spark task independently opens all committed FTS index segments to build a global BM25 scorer. Total segment-open cost per query is proportional to N_fragments × M_index_segments. For example, a 500-fragment dataset with 20 index segments causes 10,000 segment opens per query. Running OPTIMIZE periodically compacts data fragments, reducing the fragment count and therefore the total number of segment opens — this is the primary user-facing mitigation for latency-sensitive workloads.