Skip to main content

Text Search (BM25)

BM25 ranks documents by lexical term matches, weighting term rarity, frequency, and document length. It complements vector search, which can find semantic matches without identical words. Use BM25 for support articles, product descriptions, or logs where exact terminology matters.

DDL surface​

StatementPurpose
CREATE TEXT INDEX name ON table_name(column_name) WITH (...)Build and persist corpus statistics
CREATE OR REPLACE TEXT INDEX ...Rebuild with replacement settings
REFRESH TEXT INDEX nameRebuild using current source rows
SHOW TEXT INDEXESInspect tenant index inventory
DROP TEXT INDEX IF EXISTS nameRemove the index

OR REPLACE and IF NOT EXISTS cannot be combined. A persistent index stores the corpus statistics used for scoring; it is not a guarantee that ranking avoids scanning source documents.

WITH options​

OptionDefaultMeaning
k11.2Term-frequency saturation
b0.75Document-length normalization
tokenizerascii_alphanumascii_alphanum, unicode, or cjk_bigram

Use unicode for Unicode alphanumeric words, or cjk_bigram for CJK spans. The ASCII tokenizer does not stem English words: refund and refunds are different terms. The query and document use the index's tokenizer.

Scalar functions​

BM25_SCORE — in-batch​

BM25_SCORE(query, document) computes corpus statistics from its evaluation batch. Scores can change when batching or partitioning changes. Use it for exploration; do not treat those scores as a stable corpus-wide ranking baseline.

BM25_RANK — persistent index​

BM25_RANK(index_name, query, document) uses the named index's persisted statistics. A consistent artifact gives queries a common corpus baseline. Refresh it as the corpus changes and account for metadata/cache propagation.

The function returns DOUBLE. No lexical match produces zero; a missing index or failed artifact lookup is an error, not a zero-relevance result. NULL inputs propagate as NULL.

Practical example​

For a complete corpus and runnable script, use the text-search tutorial. This standalone example creates its own schema and data:

CREATE CATALOG IF NOT EXISTS help;
CREATE SCHEMA IF NOT EXISTS help.docs;
CREATE TABLE help.docs.tickets (id BIGINT, body VARCHAR);
INSERT INTO help.docs.tickets VALUES
    (1, 'The refund policy allows returns within 30 days.'),
    (2, 'Track your shipping delivery status.'),
    (3, 'Request a refund with your purchase receipt.');

CREATE TEXT INDEX help.docs.tickets_idx ON help.docs.tickets(body);
SHOW TEXT INDEXES;

SELECT id, body,
       BM25_RANK('help.docs.tickets_idx', 'refund receipt', body) AS score
FROM help.docs.tickets
WHERE BM25_RANK('help.docs.tickets_idx', 'refund receipt', body) > 0
ORDER BY score DESC, id ASC;

Rows 1 and 3 contain query terms; row 2 does not. Row 3 matches both terms. Compare ranking and matched content rather than copying fixed decimal scores, which depend on the corpus and tokenizer. Re-running the setup requires choosing fresh names or deliberately resetting the example objects.

Refresh and persistence​

REFRESH TEXT INDEX help.docs.tickets_idx;

Refresh rebuilds statistics from the current corpus. The service can also enable commit-triggered refresh. Automatic refresh is asynchronous; use explicit refresh and inspect metadata when a workflow requires current statistics.

Catalog metadata and persisted artifacts allow a later process to reload the index. Keep catalog and storage access healthy. A failed refresh or artifact load requires investigation; do not interpret stale or missing state as a successful rebuild.

Combine lexical and semantic evidence only after constructing compatible document embeddings. The simple table above has no embedding column; the vector tutorial supplies that setup.

Raw BM25 and cosine scores have different scales. A weighted sum such as 0.6 * bm25 + 0.4 * cosine_similarity is not automatically normalized. Calibrate scores or use a rank-based fusion method, then evaluate relevance on representative queries. A mandatory BM25_RANK(...) > 0 filter excludes semantic-only matches, which may be undesirable.

RAG passage selection​

After the example corpus exists, and with authorized AI access:

WITH passages AS (
    SELECT body
    FROM help.docs.tickets
    WHERE BM25_RANK('help.docs.tickets_idx', 'refund receipt', body) > 0
    ORDER BY BM25_RANK('help.docs.tickets_idx', 'refund receipt', body) DESC
    LIMIT 4
)
SELECT AI_COMPLETE(
    'Answer only from this context. How do I request a refund? Context: ' ||
    STRING_AGG(body, ' | ')
) AS answer
FROM passages;

Check that an answer is supported by retrieved passages. Lexical relevance and generation faithfulness are separate quality checks. See AI scalar functions for policy, cost, and failure behavior.

Cleanup​

DROP TEXT INDEX IF EXISTS help.docs.tickets_idx;

Text-search tutorial · Vector operations · AISQL cookbook

Last updated on