AnalyticDB:High-performance BM25 full-text search

pgsearch.tokenizer() function

pgsearch.tokenizer() generates a tokenizer configuration string. Use it with pgsearch.field() to configure tokenizers and apply filters such as stemming and stop word removal.

pgsearch.tokenizer(<name>, <min_gram>, <max_gram>, <prefix_only>, <search>, <hmm>, <dict>, <stopword>, <lowercase>, <remove_long>, <stemmer>)

Search a text field

Search for a word or phrase. Enclose multi-word phrases in double quotation marks.

-- Search for the word "keyboard"
SELECT * FROM mock_items
ORDER BY description @@@ pgsearch.config('description:keyboard');

-- Search for the exact phrase "hello world"
SELECT * FROM mock_items
ORDER BY description @@@ pgsearch.config('description:"hello world"');

Search a JSON field

-- Search for rows where metadata contains {"color": "white"}
SELECT * FROM mock_items
ORDER BY metadata @@@ pgsearch.config('metadata.color:white');

Search a datetime field

Datetime fields use UTC timezone and RFC 3339 format.

SELECT * FROM mock_items
ORDER BY created_at @@@ pgsearch.config('created_at:"2023-05-01T09:12:34Z"')
LIMIT 10;

Filter on numeric and Boolean fields

Use filter expressions inside pgsearch.config() for numeric and Boolean fields. Filters on indexed fields are faster than standard SQL WHERE clauses.

-- Full-text search on description, filtered to rating < 4
SELECT * FROM mock_items
ORDER BY description @@@ pgsearch.config('description:keyboard AND rating:<4');

Proximity search

Use ~N to match terms separated by up to N words.

-- Match "ergonomic keyboard" with at most 1 word between them
SELECT * FROM mock_items
ORDER BY description @@@ pgsearch.config('description:"ergonomic keyboard"~1');

Boolean operators

Combine search terms with AND, OR, and NOT. Use parentheses to group conditions.

SELECT * FROM mock_items
ORDER BY description @@@ pgsearch.config('description:keyboard OR category:toy');

Rank improvement

Use ^ followed by a multiplier to boost the BM25 score of specific terms, pushing matching rows higher in results.

SELECT * FROM mock_items
ORDER BY description @@@ pgsearch.config('description:keyboard^2 OR category:electronics^3');

Set search (IN operator)

The IN operator is equivalent to multiple OR conditions but uses less CPU. Each phrase must produce a single token.

SELECT * FROM mock_items
ORDER BY description @@@ pgsearch.config('description:IN [keyboard toy]');

Pagination

SELECT * FROM mock_items
ORDER BY description @@@ pgsearch.config('description:socks')
OFFSET 2 LIMIT 10;

Term set search

Search for rows that match any term in an array.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.term_set(
        terms => ARRAY[
            pgsearch.term(field => 'description', value => 'socks'),
            pgsearch.term(field => 'description', value => 'novel')
        ]
    )
);

Phrase search

Search for rows containing an array of keywords in order. The index record option must be set to position.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.phrase(
        field => 'description',
        phrases => ARRAY['little', 'red', 'riding', 'hood'],
        slop => 0
    )
);

Phrase prefix search

Search for rows that start with an ordered sequence of keywords.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.phrase_prefix(
        field => 'description',
        phrases => ARRAY['little', 're'],
        max_expansion => 1
    )
);

Tokenizer terms search

Segment the query with a specified tokenizer, then search the resulting tokens. Supports Boolean logic between tokens.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    pgsearch.tokenizer_terms('description', '朝阳百货', pgsearch.tokenizer('jieba'), 'OR')
);

Fuzzy term search

Match terms within a specified edit distance (Levenshtein distance), returning results even if the spelling is imperfect.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.fuzzy_term(
        field => 'description',
        value => 'wow',
        distance => 2,
        tranposition_cost_one => true,
        prefix => true
    )
);

Range search

Search for values within a numeric or datetime range.

SELECT * FROM mock_items ORDER BY rating @@@ pgsearch.config(
    query => pgsearch.range(
        field => 'rating',
        range => '[1,4)'::int4range
    )
);

Regular expression search

Match rows containing words that fit a regular expression pattern.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.regex(
        field => 'description',
        pattern => '(glass|screen|like|cloth|phone)'
    )
);

Boolean query

Combine subqueries with must, must_not, and should conditions.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.boolean(
        should => ARRAY[
            pgsearch.parse('description:socks'),
            pgsearch.phrase_prefix(field => 'description', phrases => ARRAY['book']),
            pgsearch.term(field => 'description', value => 'writer'),
            pgsearch.fuzzy_term(field => 'description', value => 'wow')
        ],
        must_not => ARRAY[
            pgsearch.term(field => 'description', value => 'writer')
        ],
        must => ARRAY[
            pgsearch.term(field => 'rating', value => 4)
        ]
    )
);

Rank boost

Multiply the BM25 score of a subquery by a constant to push its results higher in rankings.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.boost(query => pgsearch.parse('description:socks'), boost => 2)
);

Constant score search

Assign a fixed score to all rows matching a subquery, bypassing BM25 score calculations.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.const_score(query => pgsearch.all(), score => 2)
);

Maximum disjunction search

Match rows against multiple subqueries. Rows matching more subqueries receive higher scores.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.disjunction_max(
        disjuncts => ARRAY[
            pgsearch.parse('description:socks'),
            pgsearch.parse('description:Generic')
        ],
        tie_breaker => 0.75
    )
);

All search

Return all rows that have the indexed field, each with a score of 1.0.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.all()
);

Empty search

Return no rows. Use as a placeholder in test scenarios or edge cases.

SELECT * FROM mock_items ORDER BY description @@@ pgsearch.config(
    query => pgsearch.empty()
);