PostgreSQL指定列查询比SELECT *慢的问题排查与优化求助

为什么查询B会变慢且使用Seq Scan？ #

1. 统计信息过时
   PostgreSQL的查询规划器依赖表的统计信息判断执行计划成本。如果表数据有较大变动但未更新统计信息，规划器可能错误估算查询B的返回行数或列访问成本，误以为全表扫描（Seq Scan）比索引扫描（Index Scan）更高效。

2. 索引覆盖不足
   查询A用SELECT *，可能刚好某个索引（比如client表的主键索引或包含全列的联合索引）能直接覆盖所有需要的字段，无需回表。而查询B仅取部分列，现有索引未包含这些列的组合，规划器判断走索引需回表查数据，成本高于全表扫描，因此选择Seq Scan。

3. 连接逻辑的隐性转换
   WHERE c.active = 1条件将所有左连接转为内连接（client表必须存在且active=1），但规划器对两个查询的连接顺序或成本估算逻辑不同：查询A的*让它优先利用client的索引过滤数据，而查询B的列分布让它认为先扫表再过滤更划算。

优化查询B的具体方案 #

1. 更新统计信息

先执行统计信息更新，让规划器获取准确的数据分布：

ANALYZE client;
ANALYZE rate;
ANALYZE transportmode;
ANALYZE tarif;
ANALYZE rate_clients;

这是最基础且有效的操作，多数情况下更新统计信息就能让规划器重新选择更优执行计划。

2. 创建覆盖索引

为查询B用到的列组合创建覆盖索引，让规划器无需回表即可获取所有所需数据：

• client表索引：

CREATE INDEX idx_client_active_name ON client(active, name);

• rate表索引：

CREATE INDEX idx_rate_duration_mode_tarif ON rate(durationfrom, durationto, mode_id, tarif_id);

• transportmode表索引：

CREATE INDEX idx_transportmode_id_name ON transportmode(id, "name");

• tarif表索引：

CREATE INDEX idx_tarif_id_reference ON tarif(id, reference);

• rate_clients表索引：

CREATE INDEX idx_rate_clients_rate_client ON rate_clients(rate_id, client_id);

这些索引均为覆盖索引，包含查询B所需的全部字段，规划器可直接通过索引获取数据，避免全表扫描。

3. 验证执行计划

创建索引后，重新运行EXPLAIN ANALYZE查询B，确认是否改用Index Scan。若仍有问题，可临时手动指定索引（不推荐长期使用，仅用于测试）：

select 
r.durationfrom as "RATE START DATE",
r.durationto as "RATE END DATE",
tm."name" as "MODE",
trf.reference as "Contract Number",
c.name as "Controlling Customer"
from rate r 
left join transportmode tm on r.mode_id = tm.id
left join tarif trf on r.tarif_id = trf.id
left join rate_clients rc on r.id=rc.rate_id 
left join client c USE INDEX (idx_client_active_name) on c.id = rc.client_id
where c.active = 1;

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