POSTGRES中DISTINCT+SIMILAR TO查询性能优化求助

问题分析 #

你当前的查询用了多个OR拼接的SIMILAR TO正则，且对LOWER(Doctor)做匹配——即便建了普通索引也没法有效利用，因为函数转换和分散的正则条件会让PostgreSQL放弃索引走全表扫描，大数据集下自然慢。

具体优化步骤 #

1. 简化正则表达式，替换为更高效的POSIX匹配

把一堆重复的SIMILAR TO条件合并成一个简洁的POSIX正则（用~*实现不区分大小写匹配，省去手动LOWER()）：

SELECT DISTINCT Doctor 
FROM "Table" 
WHERE Doctor ~* '^(dr|mr|ms)[.,]?[ !^]?\s?ab|^ab'

解释：

• ^(dr|mr|ms)：匹配以Dr/Mr/Ms开头的行
• [.,]?：可选的点或逗号后缀
• [ !^]?\s?：可选的空格/感叹号/脱字符，再加可选空格（对应原查询的分隔符逻辑）
• ab：匹配后续以ab开头的内容
• |^ab：直接匹配以ab开头的行

2. 创建针对性索引，让查询用上索引

普通B-tree索引没法支持这种带中间匹配的正则，换成trigram（三元组）索引，专门优化模糊匹配场景：

CREATE INDEX idx_doctor_trgm ON "Table" USING gin (Doctor gin_trgm_ops);

如果你的PostgreSQL版本低于10，也可以用GIST索引（兼容性更好，性能稍逊）：

CREATE INDEX idx_doctor_trgm ON "Table" USING gist (Doctor gist_trgm_ops);

3. 替换DISTINCT为UNION，减少排序开销

DISTINCT需要对全量结果排序去重，换成UNION（自动去重），让两个子查询分别利用索引后再合并，通常更快：

SELECT Doctor
FROM "Table"
WHERE Doctor ~* '^ab'
UNION
SELECT Doctor
FROM "Table"
WHERE Doctor ~* '^(dr|mr|ms)[.,]?[ !^]?\s?ab'

4. 预计算结果（适合更新不频繁的场景）

如果自动补全请求量极大，且医生名称不会频繁变更，直接把符合条件的结果预存在物化视图里：

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_doctor_autocomplete AS
SELECT DISTINCT Doctor
FROM "Table"
WHERE Doctor ~* '^(dr|mr|ms)[.,]?[ !^]?\s?ab|^ab';

-- 定期刷新数据（比如每天凌晨）
REFRESH MATERIALIZED VIEW mv_doctor_autocomplete;

查询时直接从物化视图取，速度能快一个数量级：

SELECT * FROM mv_doctor_autocomplete;

5. 调整数据库配置，优化排序性能

如果查询还是因为排序慢，临时调大work_mem让PostgreSQL用内存排序（避免磁盘排序）：

SET work_mem = '64MB'; -- 根据服务器内存调整，比如16-128MB

也可以在postgresql.conf里永久调整，适合长期优化。

验证优化效果 #

每次改完用EXPLAIN ANALYZE跑查询，看执行计划里有没有用到idx_doctor_trgm索引，全表扫描（Seq Scan）换成索引扫描（Index Scan）就算生效了。

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