Postgres执行计划因WHERE条件差异巨变的原因及优化

一、执行计划不同的原因不止行数差异 #

PostgreSQL优化器选择执行计划是基于统计信息和成本模型综合判断的，行数只是其中一个因素，核心差异点包括：

1. 统计信息预估偏差：Query1中优化器预估postal_master返回1行，但实际返回11871行，预估严重错误导致选择了嵌套循环；而Query2的预估行数（1330行）更接近实际（8230行），优化器选择了更高效的并行Bitmap扫描。
2. 索引适配性差异：Query1使用的idx_postal_code_source仅匹配name字段，WHERE里的region、accountModCode、modularity_code只能在索引扫描后过滤，导致扫描了55万行无用数据；Query2的territorypostal__source_region_mod索引覆盖了三个过滤条件，直接过滤出目标数据，效率更高。
3. 连接成本判断错误：Query1优化器误以为返回行数极少，选择嵌套循环后，对premise_master执行了11871次索引扫描，每次返回12690行，最终过滤掉1.5亿行，这是性能瓶颈；Query2则通过BitmapAnd的方式快速定位匹配的连接行，每次连接仅返回1行左右。

二、Query1的优化方案 #

1. 创建覆盖WHERE条件的复合索引 #

当前索引仅匹配name，无法高效过滤其他条件，创建包含所有WHERE过滤字段的复合索引：

CREATE INDEX idx_postal_master_where ON dev.postal_master 
("name", "region", "accountModCode", "modularity_code");

如果想进一步避免回表，创建包含查询所需字段的覆盖索引：

CREATE INDEX idx_postal_master_covering ON dev.postal_master 
("name", "region", "accountModCode", "modularity_code")
INCLUDE ("postal_code", "feature", "granularity");

2. 优化premise_master的连接索引 #

Query1连接时仅用name索引，导致每次扫描大量数据，创建匹配连接条件的复合索引：

CREATE INDEX idx_premise_master_join ON dev.premise_master 
("name", "primary_code", "final_code");

这样连接时可以直接通过三个字段快速定位匹配行，避免无效扫描。

3. 更新统计信息 #

预估偏差大概率是统计信息过时导致，执行以下命令更新表的统计信息：

ANALYZE dev.postal_master;
ANALYZE dev.premise_master;

让优化器能基于准确的统计信息选择执行计划。

4. 临时调整执行计划（应急方案） #

如果索引和统计信息优化后仍无改善，可以临时关闭嵌套循环，强制优化器使用类似Query2的Bitmap扫描方式：

SET enable_nestloop = off;
-- 执行Query1
SET enable_nestloop = on;

注意这是临时方案，优先通过索引和统计信息优化解决问题。

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