52 全文检索

使用 tsvector、tsquery、语言配置和 GIN 索引搜索文本并进行排名。

PostgreSQL 内置了一套功能完善的全文检索引擎——对大多数应用来说，不需要 Elasticsearch，也不需要 Solr。核心抽象是 tsvector（文档经过解析和规范化后的表示）和 tsquery（经过解析的搜索表达式）。在 tsvector 上建立 GIN 索引后，就能在纯 SQL 中获得快速文本搜索、词干提取、排名和多语言支持。

本章要点

to_tsvector 和 to_tsquery
用于测试向量与查询是否匹配的 @@ 操作符
配置语言（英语、simple 等）
使用 GIN 建立索引以加快搜索
使用 ts_rank 对结果排名

示例

示例 52.1 全文检索

-- Setup
DROP TABLE IF EXISTS docs_fts;
CREATE TABLE docs_fts (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  body  text NOT NULL
);

INSERT INTO docs_fts (title, body) VALUES
  ('Postgres tips',    'PostgreSQL is a powerful relational database with full-text search built in.'),
  ('SQL joins',        'Inner joins keep matching rows; left joins keep all left rows.'),
  ('Indexing',         'B-tree indexes speed up equality and range queries. GIN indexes are great for arrays and text search.'),
  ('Cats',             'Cats are not databases. This sentence exists to add noise.');

-- to_tsvector parses, normalizes, and removes stop words
SELECT to_tsvector('english', 'PostgreSQL has powerful full-text search') AS v;

-- to_tsquery turns a query string into a tsquery
SELECT to_tsquery('english', 'powerful & search') AS q;

-- @@ tests "does this document match this query?"
SELECT title
FROM docs_fts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'powerful & search');

-- websearch_to_tsquery: parses Google-style input (quotes, OR, -minus)
SELECT title
FROM docs_fts
WHERE to_tsvector('english', title || ' ' || body) @@ websearch_to_tsquery('english', 'index OR join -cats');

-- Add a generated tsvector column + GIN index for fast search at scale
ALTER TABLE docs_fts ADD COLUMN search_tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

CREATE INDEX docs_fts_search_idx ON docs_fts USING gin (search_tsv);

-- Rank results
SELECT title, ts_rank(search_tsv, websearch_to_tsquery('english', 'database search')) AS rank
FROM docs_fts
WHERE search_tsv @@ websearch_to_tsquery('english', 'database search')
ORDER BY rank DESC;

DROP TABLE IF EXISTS docs_fts;

to_tsvector('english', text) 会将输入切分为词元、转换为小写、应用英语词干提取器（因此 running 和 runs 会映射到同一个词根），移除停用词（the、a、is），并返回一个 tsvector——一个带有位置信息的规范化词元排序列表。不同的配置（english、simple、german、russian、……）实现不同的词干提取和停用词规则。

to_tsquery 会解析表达式：&（与）、|（或）、!（非）、<->（后跟）。websearch_to_tsquery 接受更友好的语法——双引号短语、OR 关键字以及用于排除的 -。后者正是用户搜索框后端所需要的函数。

@@ 操作符用于测试 tsvector 是否匹配 tsquery。在 tsvector 上建立 GIN 索引后，这是 PostgreSQL 用于文本搜索的快速路径。上面的做法使用一个生成列，它始终反映 title || ' ' || body——无需触发器，也不用担心忘记更新。

运行示例

运行方式：

$ psql -f source/full-text-search.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 4
                                  v
---------------------------------------------------------------------
 'full':5 'full-text':4 'postgresql':1 'power':3 'search':7 'text':6
(1 row)
 ...

常见陷阱

在 WHERE 子句中调用 to_tsvector('english', ...)，却没有表达式索引，会使索引优化失效。可以为该表达式建立索引，或者使用生成列。
选择错误的语言：to_tsvector('english', russian_text) 会生成毫无意义的词干。让配置与数据匹配。
LIKE '%foo%' 不是全文检索，而是子字符串匹配；没有三字符组（trigram）索引时速度很慢，也不支持词干提取或停用词。自然语言查询应使用全文检索。
对于多语言数据，可能需要将语言与文本一起存储，并将配置参数化。

提示

对于不需要词干提取的子字符串或模糊匹配，pg_trgm 扩展提供基于三字符组的相似度（similarity(a, b)）以及一个能高效支持 LIKE '%foo%' 的索引。它通常与全文检索一起使用。

动手练习

在最后的 DROP TABLE docs_fts 之前运行这段练习，或者先重新创建演示表。添加更多文档并搜索它们。使用 <-> 进行短语查询：to_tsquery('english', 'full <-> text') 会精确匹配“full text”。添加 ts_headline('english', body, query)，高亮每条结果中匹配的片段。

52 全文检索

本章要点

示例

运行示例

常见陷阱

下载