Full-Text Search
PostgreSQL-compatible TiDB Cloud Starter supports PostgreSQL-compatible full-text search with tsvector, tsquery, language-specific tokenizers, ranking functions, and GIN indexes.
Full-text search is built in and does not require installing a separate extension. Chinese tokenization is also available through zhparser-compatible configurations.
Supported text search configurations
The following text search configurations are supported:
The default text search configuration is simple.
You can check the current default configuration using:
SHOW default_text_search_config;
Create a GIN index for full-text search
GIN indexes can accelerate full-text search queries.
Create a GIN index on a tsvector column:
CREATE TABLE documents (
id BIGSERIAL PRIMARY KEY,
content TEXT NOT NULL,
tsv TSVECTOR
);
CREATE INDEX idx_documents_tsv
ON documents
USING GIN (tsv);
You can also create a GIN expression index:
CREATE INDEX idx_documents_content_fts
ON documents
USING GIN (to_tsvector('simple', content));
When the expression used by the @@ operator matches the indexed expression, the optimizer can use the GIN index automatically.
Search English text
Use simple for exact token matching:
The following query can use the GIN expression index created in the previous section because it uses the same to_tsvector('simple', content) expression.
SELECT *
FROM documents
WHERE to_tsvector('simple', content)
@@ to_tsquery('simple', 'database');
Use english_stem when you need stemming and stopword removal:
CREATE INDEX idx_documents_english
ON documents
USING GIN (to_tsvector('english_stem', content));
SELECT *
FROM documents
WHERE to_tsvector('english_stem', content)
@@ plainto_tsquery('english_stem', 'running');
Use the same text search configuration in the index expression and the search query.
Search Chinese text
Use the jieba configuration for Chinese tokenization.
For example:
CREATE INDEX idx_documents_chinese
ON documents
USING GIN (to_tsvector('jieba', content));
SELECT *
FROM documents
WHERE to_tsvector('jieba', content)
@@ plainto_tsquery('jieba', '数据库');
The aliases chinese and zhparser use the same Chinese tokenization capability.
Query functions
PostgreSQL-compatible TiDB Cloud Starter supports common PostgreSQL text-search query functions:
Boolean search
Use to_tsquery() for Boolean search expressions:
-- AND
SELECT *
FROM documents
WHERE tsv @@ to_tsquery('simple', 'database & performance');
-- OR
SELECT *
FROM documents
WHERE tsv @@ to_tsquery('simple', 'postgres | mysql');
-- NOT
SELECT *
FROM documents
WHERE tsv @@ to_tsquery('simple', 'database & !oracle');
Rank search results
Use ts_rank() to rank matching documents.
For example:
SELECT
content,
ts_rank(
to_tsvector('jieba', content),
plainto_tsquery('jieba', '数据库')
) AS rank
FROM documents
WHERE to_tsvector('jieba', content)
@@ plainto_tsquery('jieba', '数据库')
ORDER BY rank DESC
LIMIT 10;
Search with weights
Use setweight() to assign different weights to different fields.
For example:
CREATE TABLE articles (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
body TEXT NOT NULL,
tsv TSVECTOR
);
UPDATE articles
SET tsv =
setweight(to_tsvector('jieba', title), 'A') ||
setweight(to_tsvector('jieba', body), 'B');
WITH q AS (
SELECT plainto_tsquery('jieba', '数据库') AS query
)
SELECT
a.title,
ts_rank(a.tsv, q.query) AS rank
FROM articles AS a, q
WHERE a.tsv @@ q.query
ORDER BY rank DESC;
Limitations
The following limitations apply during the public preview:
- Only the text search configurations listed in Supported text search configurations are supported.
- The
englishconfiguration does not perform PostgreSQL-style stemming. Useenglish_stemfor stemmed English search. - Prefix matching using the
:*flag is not supported. The prefix flag is ignored, so do not rely on expressions such asto_tsquery('simple', 'data:*')for prefix search. ts_headline()does not highlight Chinese Han-script search terms. If you need highlighting for Chinese search results, implement highlighting in the application layer.- A scalar text-search function such as
plainto_tsquery()cannot be used as a table expression in theFROMclause. Use a CTE or inline the function in the query instead. - The maximum
tsvectorvalue size is 1 MiB.