PostgreSQL中jsonb/json列执行LIKE查询的最快方法是什么？

以下是几种能大幅提升JSON文本片段搜索速度的方案，按贴合需求的优先级排序：

1. 基于pg_trgm扩展创建Trigram索引（最推荐） #

Trigram索引专门针对子字符串模糊匹配优化，完美适配你当前用LIKE搜索特定文本片段的场景，且无需大幅修改查询逻辑。

• 第一步：启用pg_trgm扩展（若未启用）

  CREATE EXTENSION IF NOT EXISTS pg_trgm;
  

• 第二步：为jsonb列创建GIN类型的Trigram索引（数据量大时GIN比GIST更高效）

  CREATE INDEX idx_jsonb_trgm ON your_table USING GIN (jsonb::text gin_trgm_ops);
  

  如果JSON里的空格格式不固定（比如存在"test":10无空格的情况），可以先统一清理空格再建索引：

  CREATE INDEX idx_jsonb_trgm_clean ON your_table USING GIN (regexp_replace(jsonb::text, '\s+', ' ', 'g') gin_trgm_ops);
  

• 第三步：执行查询
  原LIKE查询会自动走索引，速度显著提升：

  SELECT * FROM your_table
  -- 若用了清理空格的索引，查询时也要做相同处理
  WHERE regexp_replace(jsonb::text, '\s+', ' ', 'g') LIKE '%"test" : 10%';
  

2. 创建全文检索索引 #

如果需要支持更复杂的文本搜索逻辑，可将JSON文本转换为tsvector类型创建GIN索引：

• 创建索引：

  -- 若需中文支持，将'english'替换为'chinese'
  CREATE INDEX idx_jsonb_fts ON your_table USING GIN (to_tsvector('english', jsonb::text));
  

• 查询时使用短语匹配：

  SELECT * FROM your_table
  WHERE to_tsvector('english', jsonb::text) @@ phraseto_tsquery('english', '"test : 10"');
  

  这种方式适合自然语言类的短语搜索，但对符号和格式的匹配精度不如Trigram索引。

3. 结合日期过滤优化索引 #

你已提到可通过日期限制查询范围，建议创建复合索引进一步缩小搜索数据集：

-- 假设你的日期列为create_date，结合Trigram索引
CREATE INDEX idx_jsonb_trgm_date ON your_table USING GIN (create_date, jsonb::text gin_trgm_ops);

查询时先写日期过滤条件，数据库会先筛选出指定日期范围内的数据，再在小数据集上执行文本片段搜索，进一步提升效率。

内容的提问来源于stack exchange，提问作者Baradè