PostgreSQL 16超千万地址表相似查询性能优化问询

针对你用pg_trgm处理大表地址相似查询慢的问题，结合执行计划分析，核心瓶颈是先通过token过滤出大量候选行，再逐行计算相似度的开销过高，以下是具体优化方案：

1. 替换自定义token列，直接用pg_trgm原生索引 #

你当前手动维护pentagram列存储trigram，不如直接在address列创建原生GIN索引，让PostgreSQL自动处理trigram匹配和相似度过滤，避免重复计算和冗余存储：

-- 创建支持相似度查询的GIN索引
CREATE INDEX idx_address_trgm ON address USING gin(address gin_trgm_ops);

之后直接用相似度条件查询，数据库会通过索引筛选出符合阈值的记录，无需先取大量候选行：

-- 方式1：使用%操作符（依赖pg_trgm.similarity_threshold参数）
SET pg_trgm.similarity_threshold = 0.6; -- 设置你的相似度阈值
SELECT * FROM address WHERE address % '输入地址字符串';

-- 方式2：直接用similarity函数，无需修改参数
SELECT * FROM address 
WHERE similarity(address, '输入地址字符串') > 0.6;

2. 减少堆扫描开销：使用覆盖索引 #

如果查询只需要部分列（比如不需要返回全表字段），可以创建包含所需列的覆盖索引，避免回表访问堆数据：

CREATE INDEX idx_address_trgm_include ON address 
USING gin(address gin_trgm_ops)
INCLUDE (id, address, 其他需要的列);

这样查询时直接从索引获取数据，大幅降低IO开销。

3. 调整PostgreSQL内存参数，优化Bitmap处理 #

执行计划中Bitmap Heap Scan耗时占比极高，大概率是work_mem不足导致bitmap溢出到磁盘，多次扫描堆数据。临时调整该参数：

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

调整后重新执行查询，可显著减少堆扫描的实际耗时。

4. 标准化输入与存储地址，减少无效候选 #

地址数据存在大量冗余（比如缩写、空格、大小写差异），预处理后能大幅提升匹配精度，减少不必要的候选行：

• 存储时标准化：统一缩写（如st→street、tn→tamil nadu）、去除多余空格、统一大小写
• 查询时标准化：对输入地址做同样处理，再执行相似查询

5. 改用GiST索引（可选） #

如果GIN索引在高并发写入场景下性能不佳，可尝试GiST索引，它在相似度阈值较高时，筛选候选行的效率可能更优：

CREATE INDEX idx_address_trgm_gist ON address USING gist(address gist_trgm_ops);

6. 合并多token查询，避免重复扫描 #

你当前为每个token单独查询再合并，会重复扫描相同的堆数据。改用一次查询匹配所有输入地址的trigram，或者直接用原生相似度查询，避免重复计算。

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