如何优化PostgreSQL中IMDB DVD标题的相似性查询性能？

核心问题分析 #

你的查询当前执行并行全表扫描，没有利用到创建的GIN索引——因为ORDER BY similarity(...) DESC的逻辑无法直接通过GIN索引实现，数据库不得不扫描所有1000万条记录计算相似度后再排序取Top10，这是耗时的主要原因。

优化方案 #

1. 用%操作符先过滤高相似度候选集（优先推荐） #

pg_trgm的%操作符可以利用GIN/GIST索引快速筛选出与目标字符串相似度高于阈值的记录，大幅缩小需要计算相似度和排序的数据集。

修改后的查询：

SELECT tconst, primarytitle, similarity(primarytitle, 'The Shape of Water 2017') AS similarity_score
FROM title_basics
WHERE primarytitle % 'The Shape of Water 2017'  -- 利用GIN索引过滤候选
ORDER BY similarity_score DESC
LIMIT 10;

• 若默认阈值不符合需求，可手动指定相似度阈值（同时保留索引使用）：

  SELECT tconst, primarytitle, similarity(primarytitle, 'The Shape of Water 2017') AS similarity_score
  FROM title_basics
  WHERE similarity(primarytitle, 'The Shape of Water 2017') > 0.3  -- 调整阈值，比如0.3
  ORDER BY similarity_score DESC
  LIMIT 10;
  

  注：PostgreSQL 14+ 版本直接使用similarity(...) > 阈值时会自动利用GIN索引，低版本可能需要临时设置SET enable_seqscan = off强制走索引（不建议长期配置）。

2. 切换为GIST索引（大表场景替代方案） #

GIST索引比GIN索引占用空间更小，在筛选小范围候选集时性能可能更优，适合你的数据规模：

-- 可选：删除原GIN索引
DROP INDEX IF EXISTS t_gin;
-- 创建GIST索引
CREATE INDEX t_gist ON title_basics USING gist(primarytitle gist_trgm_ops);

之后执行方案1中的查询即可。

3. 结合业务场景过滤数据 #

如果你的DVD收藏以电影为主，可先通过titleType字段过滤掉非电影数据（如电视剧、短片等），进一步减少扫描范围：

SELECT tconst, primarytitle, similarity(primarytitle, 'The Shape of Water 2017') AS similarity_score
FROM title_basics
WHERE titleType = 'movie'  -- 过滤电影类型
  AND primarytitle % 'The Shape of Water 2017'
ORDER BY similarity_score DESC
LIMIT 10;

可针对titleType单独创建B树索引，或创建复合GIN索引提升过滤效率：

CREATE INDEX t_type_trgm_gin ON title_basics USING gin(titleType, primarytitle gin_trgm_ops);

4. 调整PostgreSQL配置参数 #

• 确保work_mem设置充足：当前执行计划显示用内存完成top-N排序（Memory:26kB），暂时无需调整；若后续出现磁盘排序，可适当提高work_mem值；
• 若服务器CPU核心充足，可小幅提高max_parallel_workers_per_gather，不过当前已启用2个worker，收益有限。

验证优化效果 #

执行优化后的查询后，用EXPLAIN ANALYZE查看执行计划，确认是否走了索引扫描（如Index Scan using t_gin on title_basics），此时扫描行数会从千万级骤降到几百/几千级，查询耗时会大幅降低。

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