PostgreSQL手动实现ngram时如何查询同时含x、y两个ngram的单词

问题背景 #

假设库内存在两张业务表：

• words表：主键为id，text字段存储多语言单词/词条，覆盖中文在内的上百种语言
• ngrams表：text字段存储切分好的ngram片段，word_id字段关联words.id，记录每个词条对应的所有ngram

需求为查询同时包含指定多个ngram片段的词条，常见错误写法如下：

select * from words
inner join ngrams on ngrams.word_id = words.id
where ngrams.text = x
and ngrams.text = y

逻辑错误：单条ngram记录的text字段不可能同时等于两个不同的值，这个写法永远查不到结果。本质需求是匹配同一个word_id下的多条不同ngram记录，实现多条件的逻辑与筛选。要求不使用内置全文检索能力，从底层手动实现通用ngram检索逻辑。

可落地实现方案 #

以下三种方案均适配多语言场景，可根据业务的查询模式选择：

方案1：多表连接（固定ngram数量场景首选） #

要匹配N个ngram就对ngrams表做N次内连接，每次连接单独匹配一个ngram条件，天然保证同一个词条关联到所有要求的ngram。以同时匹配x、y两个ngram为例：

SELECT words.*
FROM words
-- 第一次连接匹配ngram x
INNER JOIN ngrams n_x 
  ON n_x.word_id = words.id 
  AND n_x.text = 'x'
-- 第二次连接匹配ngram y
INNER JOIN ngrams n_y 
  ON n_y.word_id = words.id 
  AND n_y.text = 'y'

• 优势：逻辑直白，建好ngrams(text, word_id)联合索引后性能极高，查询时直接走索引定位，几乎没有额外计算开销
• 局限：只适合匹配ngram数量固定的场景，如果需要匹配的ngram个数动态变化，就得动态拼接SQL，灵活度不足

方案2：分组聚合统计（通用场景首选） #

先筛出所有命中目标ngram集合的记录，按词条ID分组后统计实际命中的去重ngram数量，数量等于待匹配ngram总数的就是符合要求的词条。以匹配x、y两个ngram为例：

SELECT w.*
FROM words w
INNER JOIN (
    SELECT word_id
    FROM ngrams
    -- 先筛出所有命中目标ngram的记录，走索引速度很快
    WHERE text IN ('x', 'y')
    GROUP BY word_id
    -- 同一个word_id下命中2个不同的目标ngram，说明同时包含x和y
    HAVING COUNT(DISTINCT text) = 2
) match_ngrams ON w.id = match_ngrams.word_id

如果要匹配3个ngram（比如x、y、z），只需要修改IN列表为('x','y','z')，把HAVING后的计数改成3即可，不需要调整SQL的整体结构。

• 优势：灵活度最高，适配任意数量的动态ngram匹配需求，不管是2个还是20个ngram的匹配都能用同一套SQL结构，对中文等大字符集语言没有兼容问题
• 优化提示：必须建ngrams(text, word_id)联合索引，否则数据量大了之后会出现全表扫描，性能骤降。如果ngram长度固定（比如都是2字gram、3字gram），可以加gram_length字段提前过滤，进一步缩小扫描范围。

方案3：数组包含判断（预聚合场景可选） #

利用PostgreSQL的数组类型能力，把每个词条关联的ngram聚合成数组，用数组包含判断直接筛选：

SELECT w.*
FROM words w
INNER JOIN (
    SELECT word_id
    FROM ngrams
    GROUP BY word_id
    HAVING array_agg(text) @> ARRAY['x','y']::text[]
) match_ngrams ON w.id = match_ngrams.word_id

• 局限：如果没有提前预聚合每个词条的ngram数组，这个写法会扫描全量ngram数据，性能远低于方案2，只适合有预计算逻辑的场景使用。

大数据量场景优化建议 #

针对中文这类字符规模超8万的大语种，当ngram表数据量达到千万级以上时，可以做两个优化：

1. 对ngrams表按text字段做哈希分区，查询时直接定位到目标ngram所在的分区，避免扫描无关数据
2. 提前把高频查询的ngram组合结果做缓存，减少实时计算开销

内容的提问来源于stack exchange，提问作者Lance Pollard