Skip to main content

Edit this page

Phrase and Proximity Search

Search for tokens appearing in a specific order. This allows matching partial or full sentences within indexed text, and — with slop — sentences whose wording drifts from the query. Requires POSITION = true in the dictionary.

See Setup for the shared dataset used in all examples.

Use the @@ operator with ts_phrase to find documents containing tokens in sequence:

Query
SELECT id, titleFROM movies_idxWHERE description @@ ts_phrase('biggest blockbuster')ORDER BY id;
Result
 id | title----+---------------  4 | Jurassic Park  6 | Scary Movie

Both documents contain the phrase "biggest blockbuster" in their descriptions.

Multi-word phrases​

Search for longer sequences:

Query
SELECT id, titleFROM movies_idxWHERE description @@ ts_phrase('the war against its controllers');
Result
 id | title----+------------  1 | The Matrix

Combining phrase conditions with AND​

Find documents matching multiple phrases:

Query
SELECT id, titleFROM movies_idxWHERE description @@ ts_phrase('alien') AND description @@ ts_phrase('galaxy')ORDER BY id;
Result
 id | title----+-------------------------------  7 | Star Trek: The Motion Picture

Combining phrase conditions with OR​

Find documents matching any of several phrases:

Query
SELECT id, titleFROM movies_idxWHERE description @@ ts_phrase('computer hacker') OR description @@ ts_phrase('serial killer')ORDER BY id;
Result
 id | title----+-------------  1 | The Matrix  6 | Scary Movie

Phrase search across columns​

Search different columns in the same query:

Query
SELECT id, titleFROM movies_idxWHERE title @@ ts_phrase('the matrix') AND description @@ ts_phrase('machine')ORDER BY id;
Result
 id | title----+------------------------  2 | The Matrix Reloaded  3 | The Matrix Revolutions

Combine with exact matching​

Use phrase search together with term operations:

Query
SELECT id, title, genreFROM movies_idxWHERE genre @@ 'sci-fi' AND description @@ ts_phrase('galaxy')ORDER BY id;
Result
 id | title                         | genre----+-------------------------------+--------  7 | Star Trek: The Motion Picture | sci-fi  8 | Alien                         | sci-fi

Proximity search with slop​

An exact phrase is brittle: it fails on the words a writer put between the ones a searcher typed. Nobody searching for "group children" wants to miss "a group of children". Pass slop := N to buy the phrase a budget of N position moves:

Query
SELECT id, titleFROM movies_idxWHERE description @@ ts_phrase('group children')ORDER BY id;
Result
id	title

Nothing — the tokens are not adjacent. With one unit of slop, the intervening of is affordable:

Query
SELECT id, titleFROM movies_idxWHERE description @@ ts_phrase('group children', slop := 1)ORDER BY id;
Result
 id | title----+---------------  4 | Jurassic Park

slop is an edit budget for phrases, playing the role Levenshtein distance plays for a single word — except the only edit it can buy is moving a term, never substituting or dropping one. It is the total number of positions the query tokens may be shifted to line up with the document, shared across the whole phrase: each token sitting between two query tokens costs one unit, and slop := 0 is an exact phrase, identical to omitting it. Every token you type must still appear in the document, so no amount of slop rescues a misspelled word — that is ts_levenshtein's job, and the two compose.

Widening the window​

Raise the budget and more distant co-occurrences come into range. "Zion falls to the machine army" needs three units:

Query
SELECT id, titleFROM movies_idxWHERE description @@ ts_phrase('zion machine', slop := 3)ORDER BY id;
Result
 id | title----+---------------------  2 | The Matrix Reloaded

At five, "Zion defends itself against the massive machine invasion" joins it:

Query
SELECT id, titleFROM movies_idxWHERE description @@ ts_phrase('zion machine', slop := 5)ORDER BY id;
Result
 id | title----+------------------------  2 | The Matrix Reloaded  3 | The Matrix Revolutions

That is the whole tuning trade-off. A low budget keeps the phrase tight and precise; a high one drifts toward "these words appear near each other", and eventually toward a plain AND of the terms.

Matching words out of order​

A budget of 2 also pays for one swap of an adjacent pair, so a phrase can match text that reverses it. Searching "spacecraft alien" finds the film that says "alien spacecraft":

Query
SELECT id, titleFROM movies_idxWHERE description @@ ts_phrase('spacecraft alien', slop := 2)ORDER BY id;
Result
 id | title----+-------------------------------  7 | Star Trek: The Motion Picture

Reordering is strictly more expensive than insertion: one intervening word costs 1, one transposition costs 2. If word order matters to you, keep slop at 0 or 1.

Other spellings​

::slop(N) applies the same budget as a modifier on an existing phrase — useful when the phrase comes from somewhere you would rather not edit, such as phraseto_tsquery or a stored query string:

Query
SELECT id, titleFROM movies_idxWHERE description @@ ts_phrase('group children')::slop(1)ORDER BY id;
Result
 id | title----+---------------  4 | Jurassic Park

Lucene's "..."~N proximity syntax also carries through to_tsquery, which matters when you are porting queries from Elasticsearch:

Query
SELECT id, titleFROM movies_idxWHERE description @@ to_tsquery('"group children"~1')ORDER BY id;
Result
 id | title----+---------------  4 | Jurassic Park

The two spellings are mutually exclusive — combining slop := N with ::slop(N) on one phrase is an error rather than a silently chosen winner.

Slop and explicit gaps​

slop composes with the gap arguments, and this is the one case where its meaning needs care: the budget counts deviation from the gap you declared, not from adjacency. Here group, gap 1, children declares "exactly one token between", which "a group of children" satisfies outright, so a zero budget suffices:

Query
SELECT id, titleFROM movies_idxWHERE description @@ ts_phrase('group', 1, 'children', slop := 0)ORDER BY id;
Result
 id | title----+---------------  4 | Jurassic Park

Raising the budget to 1 would then admit anything one step off that declaration — adjacent tokens, or two tokens apart. Interval gaps are the exception: [min, max] already expresses a range, so pairing it with slop is rejected rather than compounded.

Combine with analytics​

The power of SereneDB: search and aggregate in a single query:

Query
SELECT genre, COUNT(*) AS matches, AVG(runtime) AS avg_runtimeFROM movies_idxWHERE description @@ ts_phrase('film')GROUP BY genreORDER BY matches DESC, genre;
Result
 genre | matches | avg_runtime-------+---------+------------- drama |       1 |          96
Query
SELECT genre,       COUNT(*) AS count,       MIN(year) AS earliest,       MAX(year) AS latestFROM movies_idxWHERE description @@ ts_phrase('biggest blockbuster')GROUP BY genreORDER BY genre;
Result
 genre     | count | earliest | latest-----------+-------+----------+-------- adventure |     1 |     1993 |   1993 comedy    |     1 |     2000 |   2000

How phrase search works​

  1. The query text goes through the same dictionary as the indexed data
  2. The resulting tokens must appear in the same order and at consecutive positions in the document -- unless a gap or slop budget loosens one or both of those requirements
  3. Because both sides use the same normalization (case, stemming, accents), matching is consistent

For example, with basic_dict (CASE = 'lower', ACCENT = false):

Query
-- The query "Biggest Blockbuster" becomes tokens: {biggest, blockbuster}SELECT ts_lexize('basic_dict', 'Biggest Blockbuster');
Result
 ts_lexize----------------------- {biggest,blockbuster}

See also​