Ranking BM25 e snippets

Objetivos

Nesta aula você vai:

• Ordenar resultados FTS5 por relevância com bm25() e rank
• Gerar trechos destacados com snippet() e highlight()
• Afinar pesos de coluna e interpretar scores BM25 na prática

Introdução

Encontrar documentos que contêm o termo não basta: o usuário espera os mais relevantes primeiro e um trecho que mostre por que o resultado faz sentido. FTS5 expõe o ranking BM25 e funções auxiliares de highlight/snippet sem sair do SQL.

Conteúdo

O que BM25 mede

BM25 (Best Matching 25) pontua um documento com base em:

• frequência do termo no documento (com saturação);
• raridade do termo no corpus (IDF);
• comprimento do documento em relação à média.

No FTS5, scores menores (mais negativos) são melhores quando se usa a função bm25() — por isso o padrão é ORDER BY bm25(tabela).

A coluna oculta rank (quando disponível via auxiliar) também reflete o ranking da consulta atual.

Ordenação por relevância

SELECT rowid, titulo, bm25(artigos_fts) AS score
FROM artigos_fts
WHERE artigos_fts MATCH 'sqlite fts5'
ORDER BY bm25(artigos_fts)
LIMIT 20;

Pesos por coluna (título mais importante que corpo):

-- bm25(tabela, peso_col1, peso_col2, ...)
SELECT rowid, titulo, bm25(artigos_fts, 10.0, 1.0) AS score
FROM artigos_fts
WHERE artigos_fts MATCH 'd1 workers'
ORDER BY score;

Pesos maiores aumentam a influência daquela coluna no score.

highlight() e snippet()

highlight envolve ocorrências no texto completo da coluna:

SELECT highlight(artigos_fts, 0, '<b>', '</b>') AS titulo_hl
FROM artigos_fts
WHERE artigos_fts MATCH 'cloudflare'
ORDER BY bm25(artigos_fts)
LIMIT 10;

O segundo argumento é o índice da coluna (0 = primeira coluna da virtual table).

snippet extrai uma janela curta ao redor dos matches — ideal para listagens:

SELECT
  rowid,
  snippet(artigos_fts, 1, '<mark>', '</mark>', '…', 32) AS trecho
FROM artigos_fts
WHERE artigos_fts MATCH 'full text search'
ORDER BY bm25(artigos_fts)
LIMIT 10;

Parâmetros típicos de snippet: coluna, marcador início/fim, elipse, tamanho aproximado em tokens.

Boas práticas de UI

1. Ordene por BM25 (ou híbrido: BM25 + recência).
2. Mostre snippet no corpo e highlight só no título se o título for curto.
3. Escape HTML antes de inserir marcadores, ou use marcadores e sanitize no render.
4. Não exponha o score cru ao usuário final sem contexto — use só para ordenar.

Híbrido relevância + negócio

SELECT
  a.id,
  a.titulo,
  snippet(f, 1, '', '', '…', 24) AS trecho,
  bm25(f) AS rel
FROM artigos a
JOIN artigos_fts f ON f.rowid = a.id
WHERE f MATCH 'edge sql'
  AND a.status = 'publicado'
ORDER BY (bm25(f) * 1.0) ASC, a.publicado_em DESC
LIMIT 20;

BM25 lexical não substitui busca semântica (embeddings). Para sinônimos e intenção, combine com Vectorize/OpenSearch quando o produto exigir.

Exemplos práticos

CREATE VIRTUAL TABLE posts_fts USING fts5(titulo, corpo);

INSERT INTO posts_fts(rowid, titulo, corpo) VALUES
  (1, 'Guia FTS5', 'Ranking BM25 e snippets no SQLite'),
  (2, 'D1 na prática', 'Full-text search com fts5 no Cloudflare D1'),
  (3, 'BM25 explicado', 'Como o score BM25 ordena documentos');

SELECT
  rowid,
  highlight(posts_fts, 0, '[', ']') AS titulo,
  snippet(posts_fts, 1, '[', ']', '...', 16) AS trecho,
  bm25(posts_fts, 5.0, 1.0) AS score
FROM posts_fts
WHERE posts_fts MATCH 'bm25 OR fts5'
ORDER BY score;

// Worker: montar resposta de busca
export async function searchPosts(db, rawQ) {
  const q = sanitizeFts(rawQ);
  if (!q) return [];
  const { results } = await db
    .prepare(
      `SELECT rowid AS id,
              snippet(posts_fts, 1, '<em>', '</em>', '…', 28) AS snippet,
              bm25(posts_fts, 8.0, 1.0) AS score
       FROM posts_fts
       WHERE posts_fts MATCH ?
       ORDER BY score
       LIMIT 20`
    )
    .bind(q)
    .all();
  return results;
}

Problemas e como resolver

Resumo

Use ORDER BY bm25(tabela) (com pesos de coluna quando o título importa mais) e apresente contexto com snippet/highlight. BM25 resolve relevância lexical; para sinônimos e semântica, planeje uma camada adicional. Em seguida: FTS5 no Cloudflare D1 com content tables e triggers.