PostgreSQL索引扫描查询提示无效，仍用顺序扫描问题排查

在PostgreSQL数据库中有两张表：小型临时表Table1和超百万行的大型常规表Table2。Table2的uid字段为主键（已建立索引），Table1无索引。执行两表关联查询时速度极慢，执行计划显示对Table2采用顺序扫描（Seq Scan）而非索引扫描。已对两张表执行ANALYZE操作，添加NestLoop和IndexScan查询提示后，执行计划仍未改变，请问原因是什么？

表结构及查询语句 #

小型临时表Table1 #

CREATE TEMPORARY TABLE Table1 (
  uid VARCHAR(15)
, idx INTEGER
);

大型常规表Table2 #

CREATE TABLE Table2 (
  uid   VARCHAR(15) PRIMARY KEY
, attr1 VARCHAR(15)
, attr2 VARCHAR(15)
, attr3 INTEGER NOT NULL
);

关联查询语句 #

SELECT t1.idx, t2.attr1
FROM Table1 t1, Table2 t2
WHERE t1.uid = t2.uid;

添加提示后的查询 #

SELECT /*+  NestLoop(t1 t2) IndexScan(t2 index_table2_uid) */ t1.idx, t2.attr1
FROM Table1 t1, Table2 t2
WHERE t1.uid = t2.uid;

原执行计划显示采用Hash Join，对Table2进行全表顺序扫描，耗时较长。

核心原因 #

1. 临时表统计信息失真 #

PostgreSQL对临时表的统计信息收集逻辑和普通表不同，即便执行了ANALYZE，如果临时表数据是动态插入后未重新执行ANALYZE，或者统计采样率不足，优化器会误判Table1的数据量较大，此时会认为Hash Join（全扫Table2）的成本更低，直接忽略嵌套循环+索引扫描的提示。

2. 查询提示语法错误 #

PostgreSQL 12+支持的查询提示有严格语法要求：

• 嵌套循环的正确提示关键字是NestedLoop，而非NestLoop
• 主键索引的默认名称通常为table2_pkey（除非手动指定了索引名），你写的index_table2_uid大概率是错误的索引名，导致提示失效。

3. 索引扫描的实际成本更高 #

如果查询需要的attr1字段不在主键索引中，使用索引扫描后需要回表获取数据，优化器会计算索引扫描+回表的总IO成本，若这个成本高于全表扫描的成本，就会拒绝使用索引扫描。

解决办法 #

1. 确保临时表统计准确 #

在临时表数据插入完成后，立即执行：

ANALYZE Table1;

让优化器获取Table1的真实行数和数据分布。

2. 修正查询提示语法 #

先通过\d Table2查看主键索引的真实名称，再使用正确的提示：

SELECT /*+ NestedLoop(t1 t2) IndexScan(t2 table2_pkey) */ t1.idx, t2.attr1
FROM Table1 t1 JOIN Table2 t2 ON t1.uid = t2.uid;

3. 创建覆盖索引 #

针对查询所需字段创建覆盖索引，避免回表操作：

CREATE INDEX idx_table2_uid_attr1 ON Table2(uid) INCLUDE(attr1);

此时优化器会优先选择索引扫描，因为无需回表就能获取所有需要的字段。

4. 临时禁用Hash Join（调试用） #

如果以上方法无效，可临时降低Hash Join的优先级：

SET enable_hashjoin = off;

执行查询后再恢复：

SET enable_hashjoin = on;

注意：此为临时调试方案，不建议全局设置。

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