PostgreSQL中单字符串存储与搜索速度优化实现方案咨询

你有一张包含100万+记录的simplified表，结构如下：

create table simplified(
    url text primary key,   -- google.com/search?=something
    url_host text not null, -- google.com
    url_path text not null  -- /search?=something 
);

当前使用的模糊查询语句为：

select * from simplified where url like '%google%';

针对你的三个问题，直接给出针对性方案：

1. LIKE运算符是否为最优选择？有更快的替代方案吗？ #

LIKE '%google%'这种前后都带通配符的查询，无法利用常规的B-tree索引，会触发全表扫描，在百万级数据下速度极慢，绝对不是最优选择。

最快的替代方案是使用PostgreSQL的pg_trgm扩展，它通过生成字符串的三元组（trigram）来实现高效的子串匹配，配合GIN或GIST索引可以将这类模糊查询的速度提升几个数量级，具体操作如下：

-- 安装pg_trgm扩展
CREATE EXTENSION pg_trgm;
-- 给url字段建GIN索引（查询速度更快，占用空间略大）
CREATE INDEX idx_simplified_url_trgm ON simplified USING GIN (url gin_trgm_ops);
-- 若表更新频繁，可改用GIST索引（更新速度更快，查询略慢）
CREATE INDEX idx_simplified_url_trgm_gist ON simplified USING GIST (url gist_trgm_ops);

建好索引后，原LIKE '%google%'的查询会自动走索引，无需修改语句。

2. tsvector/tsquery结合GIN索引能否有效用于单字符串搜索？ #

不适合。tsvector和tsquery是为自然语言全文检索设计的，它会将文本拆分成独立的词汇（比如把google.com拆成googl和com），仅支持整词或词根匹配，无法处理任意子串的模糊搜索。

比如你要搜索gle这种google的中间子串，tsvector不会生成对应的词汇项，根本搜不到结果。因此这类方案完全不适用于URL的任意子串匹配场景。

3. 还有哪些可行的替代方案？ #

除了pg_trgm，根据你的搜索场景还可以选择以下方案：

方案一：针对拆分后的字段优化 #

你的表已经拆分了url_host和url_path字段，如果搜索目标明确（比如只搜域名中的关键词），可以单独给url_host建pg_trgm索引，查询时直接过滤url_host like '%google%'，比全表搜url更高效：

CREATE INDEX idx_simplified_host_trgm ON simplified USING GIN (url_host gin_trgm_ops);
SELECT * FROM simplified WHERE url_host LIKE '%google%';

方案二：前缀/后缀匹配优化 #

如果你的搜索是固定前缀（比如google%）或固定后缀（比如%google），可以用B-tree索引优化：

• 前缀匹配：直接给url建B-tree索引，LIKE 'google%'会自动走索引；
• 后缀匹配：给reverse(url)建B-tree索引，查询时转成前缀匹配：

  CREATE INDEX idx_simplified_url_reverse ON simplified USING btree (reverse(url));
  SELECT * FROM simplified WHERE reverse(url) LIKE reverse('%google');
  

方案三：pg_bigm扩展 #

类似pg_trgm，但基于二元组（bigram）实现，对于短字符串的匹配效率可能更高，安装和使用方式和pg_trgm类似：

CREATE EXTENSION pg_bigm;
CREATE INDEX idx_simplified_url_bigm ON simplified USING GIN (url gin_bigm_ops);

内容的提问来源于stack exchange，提问作者Aria Lopez