用postgresql实现es搜索功能

如果搜索型号字段 用户输入 3458 怎么把这些搜索出来 MAX3485ESA (UMW) ， yyX34677785ESA (UMW), ddX3d4677785ESA (UMW) ，像 es 就可以实现

 

PostgreSQL 实现 ES 式 Fuzzy 搜索：用户输 3458 怎么匹配到 MAX3485ESA

一、先搞清楚 ES 是怎么做到的

ES 对这种搜索用了两层机制：

ES 机制	原理	对你的例子
ngram 分词器	把 MAX3485ESA 拆成 ma,ax,x3,34,48,85,5e... 建倒排索引	用户输 3458 拆成 34,45,58，倒排命中包含 34 的文档
fuzzy query	基于 Levenshtein 编辑距离，允许字符替换 / 插入 / 删除	3458 → 3485 编辑距离 = 2（5 和 8 换位），在容错范围内

两者结合：ngram 负责快速召回候选集，fuzzy 负责容错排序。

---

二、PostgreSQL 的 4 种实现方案

方案 1：pg_trgm + word_similarity（最简单，先试这个）

pg_trgm 原生有一个被低估的函数 word_similarity，它不是比较整个字符串，而是在目标字符串中找与查询词最相似的子串—— 这正是型号搜索需要的。

SET pg_trgm.word_similarity_threshold = 0.1;  -- 调低阈值，短词才够灵敏SELECT model, word_similarity('3458', model) AS sml
FROM components
WHERE model %> '3458'        -- word_similarity 操作符，走 GIN 索引
ORDER BY sml DESC
LIMIT 20;

效果分析：

型号	最相似子串	word_similarity
MAX3485ESA(UMW)	3485	1 - edit_dist(3458,3485)/4 = 0.5
yyX34677785ESA	3467 或 7785	~0.25
ddX3d4677785ESA	3d46 或 7785	~0.25

 

MAX3485ESA 能排到前面，但 3458 只有 4 个字符，trigram 只有 34,45,58 三个，短查询词是 pg_trgm 的软肋。

方案 2：自定义 ngram 数组 + GIN（最接近 ES ngram，推荐）

自己实现 ES 的 ngram 分词逻辑，完全可控：

-- 1. ngram 拆分函数（不可变函数，才能用于生成列）
CREATE OR REPLACE FUNCTION ngram(text, int) RETURNS text[] AS $$SELECT ARRAY(SELECT substring(lower($1) FROM i FOR $2)FROM generate_series(1, greatest(0, length($1) - $2 + 1)) AS i);
$$ LANGUAGE sql IMMUTABLE;-- 2. 生成列：同时存 2-gram 和 3-gram
ALTER TABLE components ADD COLUMN model_ngram text[]GENERATED ALWAYS AS (COALESCE(ngram(model, 2), '{}') || COALESCE(ngram(model, 3), '{}')) STORED;-- 3. GIN 索引
CREATE INDEX idx_model_ngram ON components USING GIN (model_ngram);

查询：用户输入也拆 ngram，按交集数量排序（交集越多越相关）：

SELECT model,array_length(model_ngram && ngram('3458', 2), 1) AS hit2,array_length(model_ngram && ngram('3458', 3), 1) AS hit3
FROM components
WHERE model_ngram && ngram('3458', 2)    -- 至少有一个 2-gram 相交（走GIN）
ORDER BY hit3 DESC, hit2 DESC
LIMIT 20;

效果：

型号	2-gram 交集	3-gram 交集	排名
MAX3485ESA	34（1 个）	无	靠前
yyX34677785ESA	34,78,85（3 个）	无	更靠前
ddX3d4677785ESA	34,78,85（3 个）	无	并列

这个方案对 34677785 这种长数字串匹配更好，但对 3458→3485 这种换位错误召回率一般（因为 ngram 不重叠）。

 

 

 

方案 3：ngram 粗筛 + Levenshtein 精排（兼顾召回和精度）

组合方案，最接近 ES 的 ngram + fuzzy 双层架构：

SET pg_trgm.word_similarity_threshold = 0.05;SELECT c.model,levenshtein(substring(c.model from '\d+'), '3458') AS num_dist,word_similarity('3458', c.model) AS wsml
FROM components c
WHERE c.model %> '3458'                          -- 第一层：trigram 粗筛（走索引）AND levenshtein(substring(c.model from '\d+'), '3458') <= 3  -- 第二层：数字部分编辑距离精排
ORDER BY num_dist ASC, wsml DESC
LIMIT 20;

关键技巧：substring(model from '\d+') 提取型号中的纯数字部分（3485、34677785），只对数字部分算编辑距离 —— 因为用户输的 3458 是数字，型号的核心区分也是数字段。

 

型号	提取数字	与3458编辑距离
MAX3485ESA	3485	2（5↔8 换位）
yyX34677785ESA	34677785	5
ddX3d4677785ESA	34677785	5

 

MAX3485ESA 编辑距离最小，排第一。这正是你要的效果。

方案 4：pgroonga 扩展（性能最接近 ES，终极方案）

pgroonga 是基于 Groonga 的 PG 全文检索扩展，原生支持 ngram 分词和模糊搜索，性能接近 ES，且不用维护独立集群：

 

CREATE EXTENSION pgroonga;CREATE INDEX idx_model_pgroonga ON components USING pgroonga (model pgroonga.text_full_text_search_ops);-- 模糊搜索（类似 ES fuzzy）
SELECT * FROM components WHERE model &@~ '3458';-- 编辑距离容错
SELECT * FROM components WHERE model &@* '3458';

&@* 操作符就是模糊匹配，内部用 Groonga 的倒排索引 + 编辑距离，100 万数据毫秒级。

 

三、方案对比与推荐

方案	召回率	精度	性能 (100 万)	复杂度	适合场景
pg_trgm %	中	中	50~150ms	极低	通用模糊，短词弱
pg_trgm %>	中高	中	50~150ms	极低	型号子串匹配首选
自定义 ngram 数组	高	中	30~100ms	中	需要可控分词
ngram+levenshtein	高	高	80~200ms	中高	型号搜索最佳实践
pgroonga	极高	高	10~50ms	中	追求 ES 级体验
fuzzystrmatch 直接算	极高	高	2~5 秒	低	❌ 不能用，无索引

 

 

四、针对你的型号搜索，我的建议

元器件型号有个特点：字母前缀 + 数字主体 + 字母后缀，用户搜索时主要输数字部分。所以最优架构是：

-- 1. 提取数字部分作为生成列
ALTER TABLE components ADD COLUMN model_num textGENERATED ALWAYS AS (COALESCE(substring(model from '\d+'), '')) STORED;-- 2. 数字部分建 trigram 索引
CREATE INDEX idx_model_num_trgm ON components USING GIN (model_num gin_trgm_ops);-- 3. 完整型号也建 trigram 索引（兜底）
CREATE INDEX idx_model_trgm ON components USING GIN (model gin_trgm_ops);-- 4. 查询：数字部分 word_similarity 粗筛 + 编辑距离精排
SET pg_trgm.word_similarity_threshold = 0.1;SELECT model, brand,word_similarity('3458', model_num) AS num_sml,levenshtein(model_num, '3458') AS dist
FROM components
WHERE model_num %> '3458'            -- 数字子串相似度粗筛（走索引）OR model %> '3458'                -- 完整型号兜底
ORDER BY CASE WHEN levenshtein(model_num, '3458') <= 2 THEN 0 ELSE 1 END,  -- 精确匹配优先num_sml DESC
LIMIT 30;

这个方案的效果：

• 用户输 3458 → MAX3485ESA 排第一（数字部分3485编辑距离 = 2）
• 用户输 3485 → 精确匹配所有含3485的型号
• 用户输 MAX → 匹配所有 MAX 前缀
• 用户输 ESA → 匹配所有 ESA 后缀

如果这个方案还不够（比如需要支持中文同义词、拼写自动纠错、分面聚合），再上 pgroonga 或直接迁 ES。100 万数据量，pg_trgm + 生成列的组合完全够用。