PostgreSQL 15大表1000条随机主键查询性能优化咨询

从执行计划和描述来看，查询慢的核心原因是随机磁盘I/O的串行处理：

• 执行计划中Buffers: shared hit=2924 read=2076，说明有2076个数据块需要从磁盘读取；
• PostgreSQL默认effective_io_concurrency=1，即单个查询只能串行发起磁盘I/O请求，总耗时为单块读取延迟 × 读取块数——即便高速SAN支持并行I/O，单个查询也无法利用这一能力，这就是为何同时执行多个查询时总耗时相近，存储的并行能力被分散到多个查询，而非集中服务单个查询；
• 此外，当前查询需要通过主键索引回表读取tenant_id，额外增加了磁盘I/O量。

1. 开启并行磁盘I/O，利用存储性能 #

调整effective_io_concurrency参数，允许PostgreSQL为单个查询发起多个并行I/O请求：

-- 临时生效，重启后需重新设置
SET effective_io_concurrency = 32;
-- 永久生效（执行后需重载配置或重启数据库）
ALTER SYSTEM SET effective_io_concurrency = 32;
SELECT pg_reload_conf();

该参数值需根据存储的并发能力调整，高速SAN常见取值为16-64，它会让PostgreSQL在处理随机I/O时同时发起多个读取请求，大幅降低总等待时间。

2. 创建覆盖索引，避免回表I/O #

当前查询仅需id和tenant_id，但主键索引仅包含id，需回表读取数据块获取tenant_id。创建包含tenant_id的覆盖索引，让查询直接从索引获取所有数据，无需访问数据页：

CREATE INDEX idx_mytable_id_tenant ON public.mytable (id) INCLUDE (tenant_id);

覆盖索引体积远小于原表，不仅能减少磁盘读取的块数，还能提升缓存命中率，后续同类查询在缓存命中后仅需毫秒级完成。

3. 对查询ID排序，减少随机扫描开销 #

将随机ID列表排序后再查询，PostgreSQL会按主键索引的顺序扫描，减少索引页的随机寻道开销，同时如果数据是按主键顺序插入的，排序后的ID对应的数据页也会更连续：

-- 在SQL中对ID数组排序
SELECT id, tenant_id 
FROM public.mytable 
WHERE id = ANY (ARRAY(SELECT unnest('{list,of,1000,keys}'::bigint[]) ORDER BY 1));

也可以在应用层提前对ID排序后传入SQL，效果一致。

4. 使用临时表JOIN替代IN/ANY，优化执行计划 #

将随机ID存入临时表并创建索引，通过JOIN方式查询，优化器会更倾向于选择高效的Merge Join或Hash Join，进一步提升I/O效率：

-- 创建临时表并插入ID
CREATE TEMP TABLE tmp_query_ids (id bigint PRIMARY KEY);
INSERT INTO tmp_query_ids VALUES (1), (2), ..., (1000); -- 替换为你的随机ID列表

-- 执行JOIN查询
SELECT t.id, t.tenant_id 
FROM public.mytable t
JOIN tmp_query_ids q ON t.id = q.id;

临时表的主键索引会让优化器选择Merge Join，按顺序扫描两个索引，最大程度减少随机I/O。

5. 调整随机页成本参数，优化计划选择 #

高速SAN的随机读取延迟远低于PostgreSQL默认值，调整random_page_cost让优化器更准确估算索引扫描成本：

SET random_page_cost = 1.2;
ALTER SYSTEM SET random_page_cost = 1.2;
SELECT pg_reload_conf();

该参数默认值为4，高速存储可设为1.1-1.5，确保优化器不会因误判成本而选择低效的执行计划。

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