Skip to main content

Edit this page

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:

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):

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

All examples on this page use a sentences table whose b column is indexed with a lower-casing, non-stemming dictionary. For a full reference of every function and operator, see Full-Text Search Functions.

ts_phrase matches a run of tokens in order. It requires position to be enabled on the column (feature flags):

Query
SELECT a, b FROM sentences_idx WHERE b @@ ts_phrase('over lazy dog') ORDER BY a;
Result
 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.
Query
SELECT a, b FROM sentences_idx WHERE b @@ ('quick' ## 'brown') ORDER BY a;
Result
 a | b---+------------------------------------- 1 | quick brown fox jumps over lazy dog
Query
SELECT a, b FROM sentences_idx WHERE b @@ ('quick' ## 1 ## 'fox') ORDER BY a;
Result
 a | b---+------------------------------------- 1 | quick brown fox jumps over lazy dog 2 | quick red fox jumps over lazy dog
Query
SELECT a, b FROM sentences_idx WHERE b @@ ('quick' ## [0, 2] ## 'fox') ORDER BY a;
Result
 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.

Prefix, wildcard and regex​

ts_starts_with matches any token with the given prefix:

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

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

Query
SELECT a, b FROM sentences_idx WHERE b @@ ts_like('quic%') ORDER BY a;
Result
 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:

Query
SELECT a, b FROM sentences_idx WHERE b @@ ts_regexp('qu.*ck') ORDER BY a;
Result
 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:

Query
SELECT id, name FROM products_idx WHERE name @@ ts_levenshtein('cat', 1) ORDER BY id;
Result
 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:

Query
SELECT id, title FROM titles_idx WHERE title @@ ts_ngram('hello', 0.3) ORDER BY id;
Result
 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):

Query
SELECT a, b FROM sentences_idxWHERE b @@ (ts_phrase('quick brown') || ts_phrase('grey turtle'))ORDER BY a;
Result
 a | b---+------------------------------------- 1 | quick brown fox jumps over lazy dog 3 | slow grey turtle sleeps
Query
SELECT a, b FROM sentences_idxWHERE b @@ (ts_phrase('quick') && ts_phrase('brown fox'))ORDER BY a;
Result
 a | b---+------------------------------------- 1 | quick brown fox jumps over lazy dog
Query
SELECT a, b FROM sentences_idxWHERE b @@ (ts_phrase('quick') && !!ts_phrase('brown fox'))ORDER BY a;
Result
 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:

Query
SELECT a, b FROM sentences_idxWHERE b @@ ts_any([ts_phrase('red fox'), ts_phrase('grey turtle')])ORDER BY a;
Result
 a | b---+----------------------------------- 2 | quick red fox jumps over lazy dog 3 | slow grey turtle sleeps
Query
SELECT a, b FROM sentences_idxWHERE b @@ ts_all([ts_phrase('quick'), ts_phrase('brown fox')])ORDER BY a;
Result
 a | b---+------------------------------------- 1 | quick brown fox jumps over lazy dog
Query
SELECT a, b FROM sentences_idxWHERE b @@ ts_any(['quick', 'brown', 'turtle'], 2)ORDER BY a;
Result
 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:

Query
SELECT a, b FROM sentences_idxWHERE b @@ ts_compound(    ts_phrase('quick'),    ts_phrase('grey'),    [ts_phrase('lazy'), ts_phrase('big')])ORDER BY a;
Result
 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:

Query
SELECT a FROM sentences_idx WHERE a @@ ts_between(2, 3, true, true) ORDER BY a;
Result
 a--- 2 3
Query
SELECT a FROM sentences_idx WHERE a @@ ts_le(2) ORDER BY a;
Result
 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:

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

  • phraseto_tsquery — treat the input as a phrase:

    Query
    SELECT a, b FROM sentences_idx WHERE b @@ phraseto_tsquery('over lazy dog') ORDER BY a;
    Result
     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):

    Query
    SELECT a, b FROM sentences_idx WHERE b @@ to_tsquery('quick AND brown') ORDER BY a;
    Result
     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:

    Query
    SELECT a, b FROM sentences_idxWHERE b @@ websearch_to_tsquery('"quick brown" OR "grey turtle"')ORDER BY a;
    Result
     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 character offsets (such as those produced by ts_offsets); the default markup is <b>...</b>, and StartSel/StopSel options override it:

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

ts_offsets returns the character offsets of the matched tokens as interleaved start, end pairs — the building block for custom highlighting in the client. It requires offset to be enabled on the column:

Query
SELECT id, ts_offsets(docs_idx.body) AS offsetsFROM docs_idxWHERE body @@ ts_phrase('fox')ORDER BY id;
Result
 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. ts_is_null / ts_is_not_null match rows by the nullness of an indexed column. 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 a filter. EXPLAIN shows it:

EXPLAIN SELECT a FROM sentences_idx WHERE b @@ 'fox';
┌───────────────────────────┐
│       IRESEARCH_SCAN      │
│    ────────────────────   │
│      Index: sentences_idx │
│          Filter:          │
│        (Term) b = fox     │
│      Projections: a       │
└───────────────────────────┘

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​