PostgreSQL通过主键查询大表时出现慢查询问题

针对你的大表关联查询性能问题，结合执行计划和表结构信息，给出以下具体优化方案：

1. 强制使用Hash Join替代Nested Loop #

当前执行计划采用嵌套循环（Nested Loop），对临时表的每一行都执行一次主键索引扫描，1.1万次的索引查找+回表操作累积了大量IO开销（从Buffers: shared read=9261可见大量磁盘读），导致总执行时间过长。

Hash Join更适合中等大小的临时表（1-2万行）与大表的关联：它会先将临时表t_ids加载到内存构建哈希表，再一次性扫描大表test完成匹配，大幅减少IO次数。

在PostgreSQL中可以通过以下方式强制优化器选择Hash Join：

SET enable_nestloop = OFF; -- 临时关闭嵌套循环优化
SELECT t.* FROM test t JOIN t_ids ti ON t.pk = ti.pk;

或者使用查询提示（hint）：

SELECT /*+ HashJoin(t, ti) */ t.* FROM test t JOIN t_ids ti ON t.pk = ti.pk;

2. 为临时表t_ids创建主键索引，尝试Merge Join #

由于大表test已按主键聚簇（数据按主键有序存储），如果临时表t_ids的pk列也有序，优化器可以选择Merge Join，这种连接方式的性能远优于Nested Loop。

为临时表创建索引：

CREATE INDEX idx_t_ids_pk ON t_ids(pk);

创建索引后，优化器会自动考虑Merge Join（也可以通过SET enable_hashjoin = OFF;临时测试Merge Join的效果）。

3. 预加载关联数据到内存缓存 #

执行计划中shared read=9261说明大量需要的大表数据不在内存缓存中，每次查询都要从磁盘读取，这是主要的性能瓶颈之一。可以在执行关联查询前，先将t_ids对应的大表数据预加载到缓存：

-- 预加载数据到缓存
SELECT 1 FROM test WHERE pk IN (SELECT pk FROM t_ids);

如果使用PostgreSQL，也可以用pg_prewarm函数精准预加载所需数据块：

SELECT pg_prewarm('test', 'blocks', 
  (SELECT array_agg(pg_block_number(pg_get_raw_page('test', ctid))) 
   FROM test WHERE pk IN (SELECT pk FROM t_ids)));

4. 调整work_mem参数优化Hash Join性能 #

Hash Join的效率依赖于足够的内存来存储哈希表，如果work_mem设置过小，哈希表会被写入磁盘临时文件，导致性能下降。根据临时表的大小（1-2万行，每行4字节），可以临时调大work_mem：

SET work_mem = '64MB'; -- 根据实际内存情况调整，建议能容纳临时表的哈希表

5. 避免不必要的列查询（如果适用） #

如果你的查询不需要返回大表的全部50列，只选择业务需要的列，并创建覆盖索引，可以避免回表操作，进一步减少IO：

-- 假设只需要col1、col2、col3列
CREATE INDEX idx_test_pk_include ON test(pk) INCLUDE (col1, col2, col3);

这样索引扫描就能直接获取所需数据，无需访问主表数据块。

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