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_ct.station_id_cANDs.admin_code_chnLIKE4105%ANDs.chn_station1);一、EXPLAIN 参数EXPLAIN只给预估计划。加上ANALYZE和BUFFERS才拿到实际耗时和 I/O 数据——这是排查慢查询的标准组合。1. ANALYZEANALYZE让数据库真正执行这条 SQL返回每个节点的实际耗时和实际行数。把预估成本和实际耗时放一起对比就能看出优化器的估算偏差有多大。注意ANALYZE会真实执行 SQL。排查写操作INSERT/UPDATE/DELETE时记得包在事务里回滚BEGIN;EXPLAINANALYZEDELETEFROMhourly_obs_202607WHEREdata_key1;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 time2500.123..28500.456 rows500000 loops1) - Hash (actual time2400.000..2400.000 rows2000000 loops1) Buckets: 1048576 Batches: 32 Memory Usage: 65536kB看Hash节点下的Batches: 32。正常情况哈希表在内存里建完Batches 是 1。超过 1 就说明表太大超了work_mem数据库把哈希表切片写到磁盘上了。内存 O(1) 查找变成磁盘 I/O速度差好几个数量级。怎么修临时当前会话调大work_memSET work_mem 256MB;。长期关联字段加索引让优化器走 Merge Join 或 Nested Loop或者做大表分区。2. Merge Join 带双排序查询耗时 15 秒Merge Join 下面挂着两个 SortMerge Join (actual time1200.456..14500.123 rows100000 loops1) - Sort (actual time500.123..600.456 rows1000000 loops1) Sort Method: external merge Disk: 85400kBMerge 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 time0.045..7800.123 rows500000 loops1) Buffers: shared hit15000, shared read450000shared read450000非常高。匹配数据占了表的大部分——数据库在索引树里找到 50 万个 TID然后挨个回表。堆表物理排列不按这个字段来50 万次回表变成疯狂的随机 I/O。走索引比全表扫描还慢。怎么修建覆盖索引INCLUDE查询字段避免回表计划会变成 Index Only Scan。如果必须回表且返回比例高SET enable_indexscan off;强制走 Bitmap 或 Seq Scan随机 I/O 转顺序 I/O。四、排查顺序遇到慢 SQL按这个来看 Buffers有没有大量shared read或temp written——找到 I/O 瓶颈在哪。看大表扫描大表走了 Seq ScanIndex Scan 的 loops 或回表量是不是太高看 Join 节点Hash Join 的 Batches 是不是大于 1Merge Join 是不是带了 Sort这套方法能让你在几秒内定位到拖后腿的节点。