Skip to main content
SereneDBSereneDB
897Contact Us

Full-Text Search

Once a column is covered by an inverted index, you search it with the @@ match operator. The left side is the indexed column; the right side is a query expression of type TSQUERY. Queries select from the index by name:

sql
SELECT ... FROM index_name WHERE column @@ query;

Every query family below produces a TSQUERY. The simplest is a bare string literal, which is analyzed by the column's text search dictionary into one or more tokens; multi-token input matches any token (OR semantics):

SQL
SELECT a, b FROM sentences_idx WHERE b @@ 'fox' ORDER BY a;
 a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog
 2 | quick red fox jumps over lazy dog

ts_phrase matches a run of tokens in order. It is fastest with position enabled on the column (feature flags); a column without it still answers phrases when the index keeps its text, see Phrases without positions:

SQL
SELECT a, b FROM sentences_idx WHERE b @@ ts_phrase('over lazy dog') ORDER BY a;
 a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog
 2 | quick red fox jumps over lazy dog

The ## operator builds a proximity phrase by chaining parts left to right:

  • a ## b — a and b must be strictly adjacent, in order;
  • a ## N ## b — exactly N tokens may sit between them;
  • a ## [min, max] ## b — the gap may be anywhere in that range;
  • the chain extends to any length — a ## b ## c — and each pair can carry its own gap, e.g. a ## 1 ## b ## c.
SQL
SELECT a, b FROM sentences_idx WHERE b @@ ('quick' ## 'brown') ORDER BY a;
 a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog
SQL
SELECT a, b FROM sentences_idx WHERE b @@ ('quick' ## 1 ## 'fox') ORDER BY a;
 a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog
 2 | quick red fox jumps over lazy dog
SQL
SELECT a, b FROM sentences_idx WHERE b @@ ('quick' ## [0, 2] ## 'fox') ORDER BY a;
 a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog
 2 | quick red fox jumps over lazy dog

How phrase matching works. A phrase matches only when its tokens occur adjacent and in order; proximity (##) relaxes "adjacent" to "within a gap". Tested against the document quick brown fox jumps over lazy dog:

QueryMatches?Why
ts_phrase('quick brown')✅quick, brown are adjacent, in order
ts_phrase('brown quick')❌both tokens present, but not in that order
ts_phrase('quick fox')❌brown sits between them — not contiguous
'quick' ## 1 ## 'fox'✅proximity allows exactly one token (brown) between

What can be chained with ##​

Each side of a ## is a single phrase part. A part may be:

These part types mix freely: any of them can occupy any position in a single chain, in any combination, with an independent gap between any pair — for example ts_starts_with('qu') ## 1 ## ts_any(['fox', 'dog']) chains a prefix part, a gap and an alternatives part.

The boolean operators &&, || and !! are not phrase parts — they combine whole queries, not positions within a phrase. So they cannot appear directly inside a ## chain:

Goal✅ Do this❌ Not this
Alternatives at one position'quick' ## ts_any(['brown', 'grey'])'quick' ## ('brown' || 'grey')
Prefix / fuzzy at a position'quick' ## ts_starts_with('bro')—
AND / OR / NOT around a phrase('quick' ## 'brown') && 'dog''quick' ## ('brown' && 'dog')

In short: build the phrase with ## and the allowed parts, then combine the finished phrase with other queries using && / || / !! on the outside.

Phrases without positions​

Leaving position out of a column's dictionary keeps the index smaller, and phrase queries on the column still work when the index also keeps the column's text. A table created WITH (storage = 'search') keeps every column. In an inverted index, list the column in INCLUDE as well as in USING inverted:

SQL
SELECT id, BM25(notes_idx.tableoid) > 0 AS scoredFROM notes_idxWHERE body @@ ts_phrase('quick brown fox')ORDER BY id;
 id | scored
----+--------
  1 | t
  3 | t

An indexed expression such as (lower(body)) is kept by listing it a second time with included(), as in USING inverted (id, (lower(body)) words, (lower(body)) included()), also when it reads several columns. Keeping only the columns the expression reads in INCLUDE is not enough. A JSON expression such as (content->'v') works the same way: its strings match as the index analyzes them, and a JSON number, boolean or null never matches a phrase.

Every phrase form returns the same rows as with position: gaps, [min, max] intervals and slop in ts_phrase, ## chains with pattern parts, phraseto_tsquery and the phrases of to_tsquery and websearch_to_tsquery, on plain, TEXT[] and shingle columns. Scores are the same too, except that a ranked phrase with a ts_levenshtein part scores each fuzzy word like an exact match, where position scores a closer word higher. On a column without frequency, a ranked phrase gives every row the same score.

A phrase without position is slower than with it, and the more rows contain all of its words and the longer their text, the slower it gets. A phrase of rare words costs little; a phrase of common words over long texts is where position pays off. A ranked query with a LIMIT on an index built with optimize_top_k checks only the rows that can still make the top k, for the phrases that prune. Compressing the kept text, for example with INCLUDE (body included (compression = 'zstd')), shrinks the index further but makes phrases several times slower.

EXPLAIN shows where a phrase's text is checked:

SQL
EXPLAIN SELECT id FROM notes_idx WHERE body @@ ts_phrase('quick brown fox');
 QUERY PLAN
--------------------------------------------------------------
 ╭─ IRESEARCH_SCAN ─────────────────────────────────────────╮
 │ Index: notes_idx                                         │
 │ Lookup: table                                            │
 │ Index Filter:                                            │
 │ ╭─ And ───────────────────────────╮                      │
 │ ╰────────────────┬────────────────╯                      │
 │ ╭─ Terms ────────┴────────────────╮                      │
 │ │ Field: body(string)             │                      │
 │ │ Values: 'brown', 'fox', 'quick' │                      │
 │ ╰─────────────────────────────────╯                      │
 │ Table Filter:                                            │
 │ ╭─ Phrase ─────────────────────────────────────────────╮ │
 │ │ Field: body(string)                                  │ │
 │ │ Parts:                                               │ │
 │ │ Term:quick(0, 0); Term:brown(1, 1); Term:fox(1, 1);  │ │
 │ │ Verify: table filter                                 │ │
 │ ╰──────────────────────────────────────────────────────╯ │
 │ Projections: id                                          │
 │ ~1 row                                                   │
 ╰──────────────────────────────────────────────────────────╯
  • Verify: table filter: the phrase is listed under Table Filter and checked together with the query's other filters, and the Index Filter looks up its words. This is the cheaper form when other conditions reject many rows. A phrase gets it when every row has to match it and the query doesn't rank by its score: no ranking, a constant score, or the phrase scored with ::score('constant(1)') or ::score(NULL).
  • Verify: inline: the phrase stays in the Index Filter and is checked as the index finds the rows. This applies to a phrase ranked by its score, under OR or NOT, in a query ranked by vector distance or calling ts_offsets, in ts_dict_agg, and with a ts_levenshtein part while sdb_levenshtein_max_terms isn't 0.

Without position and without the column's text, a phrase of two or more words fails with ts_phrase on a column without positions needs the column's text in the index. On a column indexed without a dictionary, which holds each value as one term, a phrase of two or more parts fails with ts_phrase on a column without a text search dictionary matches only whole values. Both messages start with the call that holds the phrase: ts_phrase, ## or to_tsquery.

Prefix, wildcard and regex​

ts_starts_with matches any token with the given prefix:

SQL
SELECT a, b FROM sentences_idx WHERE b @@ ts_starts_with('turt') ORDER BY a;
 a | b
---+-------------------------
 3 | slow grey turtle sleeps

ts_like matches tokens against a SQL LIKE pattern (% = any run of characters, _ = a single character):

SQL
SELECT a, b FROM sentences_idx WHERE b @@ ts_like('quic%') ORDER BY a;
 a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog
 2 | quick red fox jumps over lazy dog

ts_regexp matches tokens against a regular expression. The default syntax is Perl-compatible (RE2); pass 'posix' for POSIX ERE:

SQL
SELECT a, b FROM sentences_idx WHERE b @@ ts_regexp('qu.*ck') ORDER BY a;
 a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog
 2 | quick red fox jumps over lazy dog

How pattern matching works. Each pattern is tested against the indexed tokens, not the raw field. Against the tokens of quick brown fox jumps over lazy dog:

QueryMatching tokenNote
ts_starts_with('qu')quickany token with that prefix
ts_like('%zy')lazy% = any run of chars, _ = one char
ts_regexp('f.x')foxRE2 by default; 'posix' for POSIX ERE

ts_levenshtein matches tokens within a given edit distance — typo-tolerant search:

SQL
SELECT id, name FROM products_idx WHERE name @@ ts_levenshtein('cat', 1) ORDER BY id;
 id | name
----+------
  1 | cat
  2 | bat
  3 | car
  5 | cats
  6 | act

ts_ngram matches by n-gram similarity against an n-gram-tokenized column; the optional threshold (0–1) trades precision for recall:

SQL
SELECT id, title FROM titles_idx WHERE title @@ ts_ngram('hello', 0.3) ORDER BY id;
 id | title
----+-------
  1 | hello
  2 | help
  4 | held

How fuzzy matching works. Each query term matches any indexed token within the given edit distance (insertions, deletions, substitutions):

QueryClosest tokenEdit distancets_levenshtein(…, 1)
jumxsjumps1✅
catsnone within 1≥ 2❌

See the Fuzzy Search recipe for a deeper walkthrough.

Boolean composition​

Build compound queries from TSQUERY expressions with || (OR), && (AND) and the unary !! (NOT):

SQL
SELECT a, b FROM sentences_idxWHERE b @@ (ts_phrase('quick brown') || ts_phrase('grey turtle'))ORDER BY a;
 a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog
 3 | slow grey turtle sleeps
SQL
SELECT a, b FROM sentences_idxWHERE b @@ (ts_phrase('quick') && ts_phrase('brown fox'))ORDER BY a;
 a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog
SQL
SELECT a, b FROM sentences_idxWHERE b @@ (ts_phrase('quick') && !!ts_phrase('brown fox'))ORDER BY a;
 a | b
---+-----------------------------------
 2 | quick red fox jumps over lazy dog

When the alternatives are a list, ts_any (OR) and ts_all (AND) are more convenient. ts_any takes an optional minimum number of alternatives that must match:

SQL
SELECT a, b FROM sentences_idxWHERE b @@ ts_any([ts_phrase('red fox'), ts_phrase('grey turtle')])ORDER BY a;
 a | b
---+-----------------------------------
 2 | quick red fox jumps over lazy dog
 3 | slow grey turtle sleeps
SQL
SELECT a, b FROM sentences_idxWHERE b @@ ts_all([ts_phrase('quick'), ts_phrase('brown fox')])ORDER BY a;
 a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog
SQL
SELECT a, b FROM sentences_idxWHERE b @@ ts_any(['quick', 'brown', 'turtle'], 2)ORDER BY a;
 a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog

ts_compound builds an Elasticsearch-style boolean query from must, must_not and should buckets:

SQL
SELECT a, b FROM sentences_idxWHERE b @@ ts_compound(    ts_phrase('quick'),    ts_phrase('grey'),    [ts_phrase('lazy'), ts_phrase('big')])ORDER BY a;
 a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog
 2 | quick red fox jumps over lazy dog

Range queries​

Numeric, temporal and verbatim text columns support range matching. ts_between takes lower and upper bounds (either may be NULL for unbounded) and inclusivity flags; ts_lt, ts_le, ts_gt and ts_ge are single-bound shortcuts:

SQL
SELECT a FROM sentences_idx WHERE a @@ ts_between(2, 3, true, true) ORDER BY a;
 a
---
 2
 3
SQL
SELECT a FROM sentences_idx WHERE a @@ ts_le(2) ORDER BY a;
 a
---
 1
 2

See the Range Queries recipe for lexicographic ordering details.

PostgreSQL-compatible query parsers​

For compatibility with PostgreSQL full-text search, SereneDB accepts the familiar parser functions. They produce a TSQUERY from a single string. Note these are SereneDB inverted-index queries, not PG tsvector/tsquery.

  • plainto_tsquery — tokenize and AND the terms:

    SQL
    SELECT a, b FROM sentences_idx WHERE b @@ plainto_tsquery('Quick Brown') ORDER BY a;
     a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog

  • phraseto_tsquery — treat the input as a phrase:

    SQL
    SELECT a, b FROM sentences_idx WHERE b @@ phraseto_tsquery('over lazy dog') ORDER BY a;
     a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog
 2 | quick red fox jumps over lazy dog

  • to_tsquery — parse a Lucene-style query string (AND/OR, +required / -excluded, * prefix, ~ fuzzy, "phrase", grouping, ^ boost):

    SQL
    SELECT a, b FROM sentences_idx WHERE b @@ to_tsquery('quick AND brown') ORDER BY a;
     a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog

  • websearch_to_tsquery — web-search syntax, where quoted substrings are phrases and OR separates alternatives:

    SQL
    SELECT a, b FROM sentences_idxWHERE b @@ websearch_to_tsquery('"quick brown" OR "grey turtle"')ORDER BY a;
     a | b
---+-------------------------------------
 1 | quick brown fox jumps over lazy dog
 3 | slow grey turtle sleeps

Highlighting​

ts_highlight wraps matched terms in markup. Its standalone form takes a text and an array of start/end byte offsets (such as those produced by ts_offsets); the default markup is <b>...</b>, and StartSel/StopSel options override it:

SQL
SELECT ts_highlight('the quick brown fox jumps over the lazy dog', [4, 9]) AS snippet;
 snippet
----------------------------------------------------
 the <b>quick</b> brown fox jumps over the lazy dog

ts_offsets returns the byte offsets of the matched tokens as interleaved start, end pairs — the building block for custom highlighting in the client. Enabling offset on the column stores those offsets in the index; without it they are recomputed for each matching row:

SQL
SELECT id, ts_offsets(docs_idx.body) AS offsetsFROM docs_idxWHERE body @@ ts_phrase('fox')ORDER BY id;
 id | offsets
----+---------
  1 | {6,9}
  2 | {0,3}

Convenience predicates​

Several wrapper functions read more naturally than col @@ ts_*(...) and expand to exactly that — phrase_matches, has_all_tokens, has_any_tokens, ngram_matches and levenshtein_matches. Plain SQL IS NULL / IS NOT NULL on an indexed column is claimed by the index as well. See the function reference for the full list.

Inspecting the query plan​

A full-text search is a first-class part of the SQL query plan, not a black box bolted on the side. The @@ predicate compiles to an IRESEARCH_SCAN over the inverted index, with the matched terms pushed into the scan as its Index Filter. EXPLAIN shows it:

SQL
EXPLAIN SELECT a FROM sentences_idx WHERE b @@ 'fox';
 QUERY PLAN
--------------------------
 ╭─ IRESEARCH_SCAN ─────╮
 │ Index: sentences_idx │
 │ Lookup: table        │
 │ Index Filter:        │
 │ ╭─ Term ───────────╮ │
 │ │ Field: b(string) │ │
 │ │ Value: fox       │ │
 │ ╰──────────────────╯ │
 │ Projections: a       │
 │ ~1 row               │
 ╰──────────────────────╯

A predicate the index cannot serve shows up as a Column Filter instead, checked row by row after the scan. A condition the index can only narrow down — a phrase without position, a geo predicate or a re-checked ts_like pattern — keeps its lookup in the Index Filter and lists the exact check under Table Filter, where it runs with the query's other filters.

Because the search executes inside the scan, it composes with the rest of SQL: a JOIN, a GROUP BY, or an ORDER BY <scorer> over the same query is planned and run as one statement. See Profiling for reading plans, and Ranking for the WAND-optimized Top: k, optimized plan.

Relevance ranking​

Matching is a yes/no filter. To score and order matches by relevance — BM25 and other scorers, boosting, and WAND-accelerated top-K queries — see the dedicated Ranking page.

See also​

Edit this page ↗