PostgreSQL 执行计划：参数、节点与常见问题

PostgreSQL 执行计划：参数、节点与常见问题

EXPLAIN是 PostgreSQL 里最常用的性能排查工具。一条 SQL 在大表上跑得慢，可能是索引不对，可能是统计信息过期，也可能是优化器选了次优路径。这篇文章讲清楚执行计划的参数怎么用、核心节点怎么看，以及生产环境里最常见的三类慢查询问题。

示例

EXPLAIN(ANALYZE,BUFFERS,FORMATTEXT)SELECTt.data_key,t.station_id_c,t.datatimeFROMhourly_obs_202607 tWHEREEXISTS(SELECT1FROMstation_info sWHEREs.station_id_c=t.station_id_cANDs.admin_code_chnLIKE'4105%'ANDs.chn_station=1);

一、EXPLAIN 参数

EXPLAIN只给预估计划。加上ANALYZE和BUFFERS，才拿到实际耗时和 I/O 数据——这是排查慢查询的标准组合。

1. ANALYZE

ANALYZE让数据库真正执行这条 SQL，返回每个节点的实际耗时和实际行数。把预估成本和实际耗时放一起对比，就能看出优化器的估算偏差有多大。

注意：ANALYZE会真实执行 SQL。排查写操作（INSERT/UPDATE/DELETE）时，记得包在事务里回滚：

BEGIN;EXPLAINANALYZEDELETEFROMhourly_obs_202607WHEREdata_key=1;ROLLBACK;

2. BUFFERS

显示查询过程中的缓存和磁盘 I/O（需要和 ANALYZE 一起用）。

• shared hit：数据在共享内存里命中，没走磁盘。
• shared read：内存没命中，从磁盘读。
• temp written：内存不够用，数据写到磁盘临时文件。

3. FORMAT

指定输出格式。默认TEXT，人类可读的树状文本。也支持JSON、XML、YAML，方便导到可视化工具里。

二、核心节点

执行计划是一棵节点树。下面几个节点最常见，搞清楚它们，慢查询定位就快很多。

1. 表扫描

Seq Scan（全表顺序扫描）
从头到尾读整张表。小表没问题，大表只查少量数据的话，说明少索引或统计信息过期。

Index Scan（索引扫描）
先查索引找到 TID，再回表读完整行。查询条件区分度高、返回行数少（比如不到 1%）的时候合适。返回行数多了，大量随机 I/O 回表会让性能急剧下降。

Index Only Scan（仅索引扫描）
查询需要的字段全在索引里，不用回表。最理想的扫描方式——覆盖索引能省掉大量磁盘 I/O。

Bitmap Heap Scan（位图堆扫描）
先扫索引，把匹配行的 TID 放进内存位图，再按位图顺序读堆表。比普通 Index Scan 强的地方：把随机 I/O 变成了顺序 I/O。适合中等数据量的范围查询。

2. 关联连接

Nested Loop（嵌套循环）
外层表扫 N 行，内层表每行查一次。小表驱动大表的时候很快。外层表大、内层表没索引的话——成本指数级爆炸。

Hash Join（哈希连接）
扫小表在内存建哈希表，然后扫大表做 O(1) 匹配。大表连大表、等值连接没索引的时候最好用。但如果小表太大，超出work_mem，哈希表会溢出到磁盘，性能就崩了。

Merge Join（归并连接）
两张表都要先按关联字段排好序，然后像拉链一样同步推进匹配。大表等值连接、关联字段上都有索引的时候好用。如果Merge Join下面挂着两个Sort节点——说明数据本来无序，排序开销可能很大。

三、三个常见慢查询问题

1. Hash Join 内存溢出

大表 JOIN 耗时 30 秒，计划里长这样：

Hash Join (actual time=2500.123..28500.456 rows=500000 loops=1) -> Hash (actual time=2400.000..2400.000 rows=2000000 loops=1) Buckets: 1048576 Batches: 32 Memory Usage: 65536kB

看Hash节点下的Batches: 32。正常情况哈希表在内存里建完，Batches 是 1。超过 1 就说明表太大超了work_mem，数据库把哈希表切片写到磁盘上了。内存 O(1) 查找变成磁盘 I/O，速度差好几个数量级。

怎么修：

• 临时：当前会话调大work_mem，SET work_mem = '256MB';。
• 长期：关联字段加索引，让优化器走 Merge Join 或 Nested Loop；或者做大表分区。

2. Merge Join 带双排序

查询耗时 15 秒，Merge Join 下面挂着两个 Sort：

Merge Join (actual time=1200.456..14500.123 rows=100000 loops=1) -> Sort (actual time=500.123..600.456 rows=1000000 loops=1) Sort Method: external merge Disk: 85400kB

Merge Join 要求两边数据有序。没索引，优化器只能强加 Sort。而且Sort Method: external merge Disk说明排序数据也超了work_mem——两次排序加一次归并全在走磁盘。

怎么修：

• 关联字段加索引，数据天然有序，两个 Sort 直接消失。
• 加不了索引的话，调大work_mem让排序在内存完成。

3. Index Scan 变成随机 I/O 制造机

查询走了索引，但还是耗时 8 秒：

Index Scan using idx_orders_status on orders t (actual time=0.045..7800.123 rows=500000 loops=1) Buffers: shared hit=15000, shared read=450000

shared read=450000，非常高。匹配数据占了表的大部分——数据库在索引树里找到 50 万个 TID，然后挨个回表。堆表物理排列不按这个字段来，50 万次回表变成疯狂的随机 I/O。走索引比全表扫描还慢。

怎么修：

• 建覆盖索引（INCLUDE查询字段），避免回表，计划会变成 Index Only Scan。
• 如果必须回表且返回比例高，SET enable_indexscan = off;强制走 Bitmap 或 Seq Scan，随机 I/O 转顺序 I/O。

四、排查顺序

遇到慢 SQL，按这个来：

1. 看 Buffers：有没有大量shared read或temp written——找到 I/O 瓶颈在哪。
2. 看大表扫描：大表走了 Seq Scan？Index Scan 的 loops 或回表量是不是太高？
3. 看 Join 节点：Hash Join 的 Batches 是不是大于 1？Merge Join 是不是带了 Sort？

这套方法能让你在几秒内定位到拖后腿的节点。