Skip to main content
SereneDBSereneDB
897Contact Us

Ranking

Matching answers a yes/no question — does a row match? Ranking answers a different one — how well does it match? — and orders the results accordingly. The two are separate steps: a WHERE col @@ query filter selects the matching rows, and a scorer in ORDER BY sorts them by relevance.

Scoring with BM25​

Okapi BM25 is the standard relevance scorer. Use it in ORDER BY, highest score first:

SQL
SELECT id, title, BM25(movies_idx.tableoid) AS relevanceFROM movies_idxWHERE description @@ ts_phrase('alien')ORDER BY relevance DESC, id;
 id | title                         | relevance
----+-------------------------------+-----------
  7 | Star Trek: The Motion Picture | 0.8951471

Other scorers​

SereneDB ships several scorers; TFIDF is the classic alternative:

SQL
SELECT id, title, TFIDF(movies_idx.tableoid) AS relevanceFROM movies_idxWHERE description @@ ts_phrase('alien')ORDER BY relevance DESC, id;
 id | title                         | relevance
----+-------------------------------+-----------
  7 | Star Trek: The Motion Picture | 1.2527629
ScorerParameters (defaults)Notes
BM25k1 (1.2), b (0.75)Okapi BM25; b = 0 disables length normalization (BM15)
TFIDFwith_norms (false)Classic TF-IDF
lm_jmlambda (0.1)Language model, Jelinek-Mercer smoothing
lm_dirichletmu (2000)Language model, Dirichlet smoothing
indri_dirichletmu (2000)Indri-style Dirichlet (no floor clamp)
dfimeasure ('standardized')Divergence-from-independence; also 'saturated', 'chi_squared'
raw_tf / raw_boost / raw_dl—Raw term frequency, boost and document length

Boosting​

The ^ operator multiplies a sub-query's contribution to the score, so you can weight some clauses above others. Here a title match is boosted so it outranks a description-only match:

SQL
SELECT id, titleFROM movies_idxWHERE title @@ ('alien'::tsquery ^ 5.0) OR description @@ 'galaxy'ORDER BY BM25(movies_idx.tableoid) DESC, id;
 id | title
----+-------------------------------
  8 | Alien
  7 | Star Trek: The Motion Picture

Top-K queries and WAND pruning​

The common shape ORDER BY <scorer> DESC LIMIT k returns the best k matches. Building the index with the optimize_top_k index option enables WAND pruning, which skips candidates that provably cannot reach the top k:

sql
CREATE INDEX movies_idx ON movies
    USING inverted (id, description ranking_dict)
    WITH (optimize_top_k = 'bm25(1.2, 0.75)');
SQL
SELECT id, titleFROM movies_idxWHERE description @@ 'galaxy'ORDER BY BM25(movies_idx.tableoid) DESC, idLIMIT 1;
 id | title
----+-------------------------------
  7 | Star Trek: The Motion Picture

Pruning engages only when all of the following hold; otherwise the query still runs correctly, just without the optimization:

  • the query is ORDER BY <scorer>(idx.tableoid) DESC with a LIMIT;
  • the scorer can use the score bounds the index stores (see the table below);
  • the filter is a single term, an OR of terms that any one term satisfies, an AND of terms, one or more terms with AND NOT exclusions (a AND NOT b), one required term with optional terms that only add to the score (to_tsquery('+fox quick')), or a phrase without slop. An AND that also holds such ORs, like (a OR b) AND (c OR d), is pruned when enough rows match for pruning to pay off. An OR prunes only when the scores of its terms add up: one that keeps the best score of its terms is not pruned, such as a fuzzy ts_levenshtein match that expands to several terms, a word whose dictionary gives synonyms, or a group with ::merge('max'). Nor are a phrase with slop or with several terms at one position, an OR with a phrase or an AND among its branches, and an OR that needs more than one but not all of its terms (ts_any(list, k) or ::min_match(k) with k above 1). A phrase on a column without position (phrases without positions) is pruned when at least one of its parts is a plain word, it has no [min, max] gaps, and its dictionary puts one token at each position, which synonym and n-gram dictionaries don't.

The index stores the kind of score bound that the scorer named in optimize_top_k needs, and every scorer that needs the same kind can use it. So lm_dirichlet with another mu, or BM25 with another k1, prunes too:

optimize_top_k namesORDER BY scorers that prune
raw_tf, TFIDF with with_norms false, or BM25 with b = 0raw_tf, TFIDF with with_norms false, BM25 with b = 0
lm_jm, TFIDF with with_norms true, or BM25 with b = 1lm_jm, TFIDF with with_norms true, BM25 with b = 1
lm_dirichlet or dfilm_dirichlet, dfi
BM25 with any other bBM25 with the same b

BM25 with k1 = 0, indri_dirichlet, raw_boost, raw_dl, idf and constant have no score bounds, so they never prune, neither in ORDER BY nor in optimize_top_k.

EXPLAIN shows Top: k, optimized on the scan (Top: k, offset n, optimized when the query has an OFFSET) when the first two conditions hold, plus a Bounds: row that names the index's scorer when it differs from the ORDER BY scorer; it doesn't check the filter's shape. The scan returns the answer rows already ranked, so no separate TOP_N operator sits above it. The sdb_disable_top_k_optimization session setting turns it off at query time.

Tie-breaking​

Scores can tie. Add further ORDER BY columns after the scorer for a deterministic order — typically the primary key:

sql
SELECT id, title
FROM movies_idx
WHERE description @@ 'galaxy'
ORDER BY BM25(movies_idx.tableoid) DESC, id;

Combining ranked queries​

To blend a lexical (BM25) ranking with a vector ranking — or any two ranked result sets — use Reciprocal Rank Fusion, which combines ranks rather than incomparable scores. See Hybrid Search and the Reciprocal Rank Fusion recipe.

See also​

Edit this page ↗