查询优化与EXPLAIN

中间件

PostgreSQL

查询优化与EXPLAIN

读懂 EXPLAIN、管住行数估算、排好系统参数——慢查询优化的三板斧，外加 pg_stat_statements 和 PgBouncer 这两件生产必备装备。

EXPLAIN 的正确打开方式

裸 EXPLAIN 只看得到预测（planner 根据统计信息模拟出的计划树），不含执行、不含真实行数。实战标配是：

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

ANALYZE：真执行一遍，给每个节点补上 actual time / rows / loops。注意它会真的跑——INSERT/UPDATE/DELETE 要执行就包在 BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; 里（DDL 回滚不了，先 CREATE TABLE 想好）。
BUFFERS：给出每节点访问了多少 shared buffer（hit 命中 / read 磁盘读），是"慢在 IO 还是慢在 CPU"的分水岭，比时间稳（时间会抖）。
FORMAT JSON：机器可读，方便丢进 explain.dalibo.com 这类可视化对比工具；TEXT 适合终端速查。
TIMING OFF：节点计时要反复取高精度时钟，节点很多时有可感知的开销；只关心 BUFFERS 时可以关掉。
COSTS OFF：抹掉估算成本数字，调参/改写前后 diff 两份计划时特别干净；SETTINGS OFF 再隐去会话参数上下文。

输出里每行一个节点，缩进越深越先执行，核心三组数字：

cost=启动..总：单位是"抽象页"，不是毫秒——seq_page_cost=1.0、random_page_cost=4.0 折算出来的相对数。启动成本是出第一行前要付的代价（排序、聚合要先攒完全部输入），所以 ORDER BY+LIMIT 的场景下"总成本高但启动低"的计划可能反而快。
rows：估算行数。actual rows：真实行数。这两个对不上，后面一切算子选择都是歪的——这是排障的第一优先级线索。
loops：该节点被执行的次数。这是读 plan 第一大坑：actual time 和 rows 都是单次循环的平均值，总代价 = 值 × loops。nested loop 内侧节点 loops 几万很常见，看到 rows=1 loops=104231 别以为内表只扫了一行，是扫了十万次。

完整逐行解读一个典型计划（orders 近 7 天订单按金额取前 100，users 侧走主键）：

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total_amount, u.nickname
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.created_at > now() - interval '7 days'
ORDER BY o.total_amount DESC
LIMIT 100;

 Limit  (cost=35612.84..35615.34 rows=100 width=32) (actual time=412.5..413.1 rows=100 loops=1)
   ->  Sort  (cost=35612.84..35862.84 rows=100000 width=32) (actual time=412.4..412.7 rows=100 loops=1)
         Sort Key: o.total_amount DESC
         Sort Method: top-N heapsort  Memory: 64kB
         ->  Nested Loop  (cost=0.70..25862.84 rows=100000 width=32) (actual time=12.3..401.9 rows=104231 loops=1)
               ->  Index Scan using orders_created_idx on orders o  (cost=0.43..15862.84 rows=100000 width=24) (actual time=12.2..289.4 rows=104231 loops=1)
                     Index Cond: (created_at > (now() - '7 days'::interval))
                     Buffers: shared hit=7900 read=1042
               ->  Index Scan using users_pkey on users u  (cost=0.28..0.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=104231)
                     Index Cond: (id = o.user_id)
                     Buffers: shared hit=312693
 Planning:
   Buffers: shared hit=184
 Planning Time: 0.312 ms
 Execution Time: 413.287 ms

从内往外读：orders_created_idx 先扫出 10.4 万行（估算 10 万，很准）；内侧 users_pkey 每次 0.001ms 但被驱动了 104231 次——真实耗时 ≈ 0.001 × 104231 ≈ 100ms，全在 hit 上没有磁盘读，说明用户表热点页都在缓存里，这条路径的问题不是 IO 而是"循环次数太多"（换成 Hash Join 一次建哈希一次探测就没了，见下节）；Sort 用的是 top-N heapsort，只保留 100 行内存，很便宜；Limit 只是掐头。整条链路上"估算 vs 实际"都对得上，这是一份统计健康的 plan——读坏 plan 时反差会非常刺眼。

并行计划里还有一层 loops 陷阱：Gather/Gather Merge 的 loops 是 worker 数，内侧节点报的是每 worker 平均——真实总行数同样要乘回去（rows=8700 loops=2 ≈ 17400 行）。另外注意 Workers Planned: 2, Workers Launched: 0：池子里没有空闲 worker（max_worker_processes 被别处吃满），并行计划退化成串行，这是"昨天还快今天就慢"的经典鬼故事。EXPLAIN 本身看不到等锁，锁等待要在执行期间查 pg_stat_activity（wait_event_type='Lock'，见 05-事务与锁.md）。

算子地图

扫描三件套

Index Scan：逐条回表，适合"命中行少且能利用索引序（排序/防排序）“的场景；回表是随机 IO，命中行多了成本飙升。
Index Only Scan：列都在索引里、且 visibility map 覆盖良好时完全不碰堆表——但 vacuum 欠账会让 VM 变脏、退回逐行检查堆（见 06-MVCC与Vacuum.md）。
Bitmap (Heap) Scan：先攒位图再按页序回表，把随机 IO 变顺序 IO。单条件命中约 1%–10% 行、或者多条件 OR（BitmapOr 合并两个索引）时它常是最优解——OR 两个索引列时 planner 常常就给你这个。
Seq Scan：不是原罪。小表、或大表要取超过 ~10% 的行时它比索引便宜（顺序读 + 无回表）。看到 Seq Scan 先问"是不是该有索引”，再问"是不是有索引也用不上"。

两个 OR 条件各有一个索引时，理想形态长这样（先各自攒位图、合并后按页序回表，等价于"用两个索引跑一个 IN"）：

 Bitmap Heap Scan on orders  (cost=.. rows=9200) (actual rows=9183)
   Recheck Cond: ((status = 'paid') OR (channel = 'web'))
   ->  BitmapOr
         ->  Bitmap Index Scan on orders_status_idx
               Index Cond: (status = 'paid')
         ->  Bitmap Index Scan on orders_channel_idx
               Index Cond: (channel = 'web')

Join 三种

Nested Loop：外侧每行驱动内侧一次。最优场景：内侧有强索引、外侧行数少；无等值条件的连接（<、范围）也常只剩它。最惨场景：外侧估小了，内侧被驱动百万次——所以 loops 大时先看外侧 rows 估算歪没歪。
Hash Join：小侧建哈希、大侧探测，仅适用等值连接。两表都得扫，但各一次，O(n+m) 且不吃排序。哈希表放不下 work_mem 会分批溢盘（见下）。
Merge Join：两侧按连接键排序后归并。等值大表连接、且两侧本来就有序（有序索引、已按该键 ORDER BY）时最划算，否则为排序付的钱不如 Hash。

聚合与排序

HashAggregate：任意输入顺序，内存哈希去重分组——GROUP BY 的默认最优。
GroupAggregate：要求输入已按分组键有序，所以常搭配该键的索引出现。它不占哈希内存、边读边出，数据有索引序或哈希放不进 work_mem 时胜出。
Sort：快排/堆排序；Sort Method: external merge Disk: 25000kB 意味着排不进 work_mem、溢盘了——性能悬崖信号。
IncrementalSort（PG 13+）：ORDER BY a, b 而索引只按 a 有序时，按 a 分组、组内小排序，比整体 Sort 便宜——看到它说明 planner 在利用部分有序性。

其他常客

Materialize：把子树结果缓存供多轮重扫，主要出现在 repeated subplan（如某些 NL 内侧）。缓存过大说明该改写 SQL。
Aggregate/WindowAgg/SubqueryScan：CTE 在 PG 12+ 默认被内联展开（不再自动物化），WITH x AS MATERIALIZED (...) 可强制物化当优化围栏——用不用得看谓词下推和 join 顺序谁占优，详见 03-SQL查询实战.md、07-高级SQL.md。
Lock/等待：plan 里看不到"正在等谁"，但看到节点时间远超其子节点时间之和，通常是中间夹了等待或 spill。

并行计划的标准形态——外层是完整结果，内层是"每 worker 份额 × worker 数"：

 Gather  (cost=1000.00..45123.56 rows=998342 width=24) (actual rows=998342 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on logs  (cost=.. rows=332781 width=24) (actual rows=332781 loops=3)

332781 × 3（1 leader + 2 worker）≈ 998342，对不上就是读错了。Workers Launched: 0 则表示并行退化为串行。

行数估算是万恶之源

planner 的数学核心是 行数 × 选择率 链，选择率来自 ANALYZE 收集的 pg_stats（直方图 + MCV 最常值列表 + n_distinct）。所以：

数据大变后计划突变，第一件事是 ANALYZE 表名;，并确认 autovacuum 的 analyze 分支跟得上（autovacuum_analyze_threshold / autovacuum_analyze_scale_factor，默认删改 20% 行触发——十亿行表等于 2 亿行变更才自动 analyze，欠账可见 pg_stat_user_tables.last_analyze/last_autoanalyze，vacuum 侧见 06-MVCC与Vacuum.md）。
单列采样桶数由 default_statistics_target（默认 100）控制，某列数据分布刁钻（长尾、相关值多）可对该列单独提：ALTER TABLE customers ALTER COLUMN city SET STATISTICS 1000; ANALYZE customers;（下面的地理列用独立演示表 customers(id bigint, city text, province text)，与示例底座无关）。全局调大要掂量 ANALYZE 成本。

planner 假设列与列之间相互独立。 这个假设一天 break 三次。典型事故：

SELECT ... FROM customers WHERE city = '杭州' AND province = '浙江';
-- 选择率按独立算：P(city)×P(province) 远小于真实的 P(city|province)
-- 估算 12 行，实际 50000 行 → planner 兴冲冲选了 Nested Loop，外侧驱动内侧 5 万次

plan 上的症状就是刺眼的估算/实际背离：

 Nested Loop  (cost=0.70..3.94 rows=12 width=32) (actual rows=50213 loops=1)
   ->  Seq Scan on customers  (cost=0.00..3.94 rows=12 width=24) (actual rows=50213 loops=1)
         Filter: ((city = '杭州') AND (province = '浙江'))
         Rows Removed by Filter: 1949787

rows=12 对上 actual rows=50213，差四千倍——外侧每一层驱动次数都被这个低估欺骗了。

PG 10+ 的解法是扩展统计——让 ANALYZE 同时采样列组合：

CREATE STATISTICS st_customers_geo (dependencies, ndistinct, mcv)
  ON city, province FROM customers;
ANALYZE customers;   -- 必须重跑才生效

三种统计类型各治一种病：ndistinct 修 GROUP BY a, b 的组合基数估算，dependencies 修函数依赖列（city→province）的 AND 条件低估，mcv 修"值对一起出现在高频行里"的估算。PG 14 起还能对表达式建统计：CREATE STATISTICS st_lower (ndistinct) ON lower(email) FROM users;——给表达式索引配套用很合适。验证有没有用：SELECT stxname, stxstatistics FROM pg_statistic_ext，对比前后 plan 的 rows。

注意估算歪了不只有 join 算法一种症状，还会导致"选错索引、Limit 策略激进/保守、并行度乱给"。先修统计，再动查询——这是本章的方法论主线。

让坏计划变好：改写、内存与限制器

改写优先于调参

OR 两个索引列 → 改写成 UNION ALL 两段各走各的索引，或确认 planner 已给 BitmapOr；裸 OR 常逼出 Seq Scan。
WHERE id IN (SELECT ...) 大子查询 → EXISTS（半连接）或直接 JOIN，语义要拿准（IN 遇 NULL 的三值逻辑见 03-SQL查询实战.md）。
大 OFFSET 深分页 → keyset 分页（WHERE (created_at, id) < ($1, $2) ORDER BY ... LIMIT n），每页都是 O(页长) 而不是 O(偏移)——配方见 03-SQL查询实战.md。
SELECT * → 只取必要列：少回宽表堆页、给覆盖索引/Index Only Scan 留门、少传网络字节。

work_mem 与溢盘

work_mem（默认 4MB）是每个排序/哈希节点、每个 worker 各付一份的预算，不是全局上限——并发高时别激进调大实例值，否则 OOM 给你看。观测手段：plan 里的 Sort Method: external merge / Hash Join ... Batches: 64，以及会话内打开这两个开关再复跑：

SET log_temp_files = 0;     -- 每个溢盘临时文件（KB）都落日志
SET track_io_timing = on;   -- 让 wait_event 与 pg_stat 里的 IO 时间可信

治法是三层：局部 SET work_mem = '64MB'; 只喂这条重查询、ALTER ROLE report SET work_mem='32MB'; 只喂报表角色、最后才考虑实例级。

join 搜索空间

多表连接是排列爆炸，planner 在 from_collapse_limit/join_collapse_limit（默认都是 8）内穷举，超过 12 表左右（geqo_threshold）切换成遗传算法 GEQO——表一多计划质量会肉眼可见地抖，EXPLAIN 对比"手写 join 顺序 + join_collapse_limit=1“是排查大 SQL 的标准动作，不要当成日常调参。

enable_* 的正确用法

SET enable_seqscan = off;、enable_nestloop = off; 这类开关只用于诊断：想知道"是不是存在一条更好的路径被 planner 无视了"时关掉当前算子逼它换路，对比一下成本——若换路确实便宜很多，答案几乎总是统计信息歪了或索引没建对，回去修因，别把 enable_* 写进配置文件上线：那是把 planner 的眼睛缝上。真需要逐条钉死计划（如无法改 SQL 的三方系统），用扩展 pg_hint_plan 走 hint 路线，它是创可贴不是药，同样别滥用。

系统级参数（承接 00 章）

# 内存三件套（16GB 独享机示例）
shared_buffers = 4GB              # 默认 128MB 太小；经验值 = 内存的 25%
effective_cache_size = 12GB       # 仅给 planner 的提示：OS 页缓存 + shared_buffers 合计可用缓存
# 内核配合：Linux 开 huge_pages=try（vm.nr_hugepages），vm.overcommit_memory=2 按 PG 口径配足

SSD/NVMe 必调：random_page_cost 默认 4.0（为机械盘时代写的），SSD 降到 1.1–1.5 后，索引扫描/回表在 planner 眼里立刻便宜，很多"为什么不走索引"当场消失。
IO 并发：effective_io_concurrency 指导 bitmap scan 与预读的并发度，机械盘时代默认 1，PG 18 起默认调到 16，NVMe/云盘可到 200–1000。
PG 18 异步 IO：新引入 AIO 子系统，顺序扫描、bitmap heap 扫描、vacuum、分析等读路径全面受益。io_method 取 worker（默认，io_workers 缺省 3）/ io_uring（需 --with-liburing 编译，内核支持时最优）/ sync（退回旧行为）；io_combine_limit 控制 IO 合并块大小；pg_aios 视图看句柄。高并发 NVMe 机器值得开 io_uring，容器/老内核用默认 worker 即可。
并行度：max_worker_processes（默认 8，实例总预算）、max_parallel_workers（默认 8，并行查询池）、max_parallel_workers_per_gather（默认 2，单条查询上限）、并行触发门槛 min_parallel_table_scan_size（默认 8MB，并行度按 size×3 递增折算）。OLTP 机器并行预算常被报表吃干，检查 Workers Launched 与它是否匹配。

pg_stat_statements 与 auto_explain

找"谁在烧数据库"用 pg_stat_statements：postgresql.conf 加 shared_preload_libraries = 'pg_stat_statements'（要重启），再 CREATE EXTENSION pg_stat_statements;。它按标准化后的语句指纹聚合（字面量归一为 $n），PG 13+ 核心列：calls / total_exec_time / mean_exec_time / rows / shared_blks_*。

SELECT calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       left(query, 72) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

读法：total 定优先级（它告诉你该优化谁——一条 5ms 跑一百万次的接口比一条 5s 一天跑两次的报表重要得多），mean 定手段（mean 高是单条慢，去 EXPLAIN；calls 高是量大，去看 N+1、缓存、批量化）。专抓 N+1 的变体：

-- 单条很快但调用次数爆炸的指纹，多半是循环里发 SQL
SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 60) AS query
FROM pg_stat_statements
WHERE calls > 10000
ORDER BY calls DESC
LIMIT 10;

分析完用 SELECT pg_stat_statements_reset(); 清零再观察一段，避免历史累积误导。

“没进日志就抓不到线上偶发慢计划"交给 auto_explain：

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '200ms'   # 超过才记
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = off             # 省计时开销

注意 log_analyze=on 等于给每条触线查询跑 EXPLAIN ANALYZE，本身有开销且日志量可观，log_min_duration 别设太低；它是"事后验尸"工具，常态化开着要盯日志成本。

连接池与 max_connections

PG 一连接一进程（fork 模型，见 01-架构与核心概念.md）：每个 backend 常驻几 MB 起步、排序哈希还各吃一份 work_mem，连接上千时光上下文切换和共享内存就压垮吞吐——几千连接不是"并发”，是灾难。max_connections 默认 100 是保守线不是目标线，务必留出 superuser_reserved_connections、autovacuum 工作进程和复制连接的预算。

前端统一入口用 PgBouncer，三种 pool_mode：

session：一个客户端独占一条 server 连接直到断开。所有会话状态（SET、临时表、advisory lock、LISTEN/NOTIFY、prepared）都安全，但连接节省率最低。
transaction：每个事务结束后 server 连接归还池。利用率最高，副作用也最多：会话级状态全部不可靠——SET 会残留给下一个客户端、session advisory lock 拿不到语义、临时表丢失、LISTEN/NOTIFY 基本失联，且 prepared statement 因为绑在特定 server 连接上会直接报错（DEALLOCATE 也不归你控制）。
statement：一条语句一还，限制更多（事务不能跨语句），一般不选。

transaction 模式下要让 pgx 活得下去，三选一：PgBouncer 升到 1.21+ 并开启协议级 prepared statement 支持（max_prepared_statements 给每条连接的上限，要按 pgx 侧 StatementCacheCapacity ≤ 服务端预算规划）；或 pgx 降执行模式（QueryExecModeExec 等，见 08-Go客户端pgx.md）；或干脆 session 模式。一份最小可用配置长这样：

[databases]
shop = host=127.0.0.1 port=5432 dbname=shop

[pgbouncer]
pool_mode = transaction
max_client_conn = 5000         ; 客户端侧可以敞开
default_pool_size = 40         ; 真正连到 PG 的 server 连接预算
min_pool_size = 5
max_prepared_statements = 512  ; 与 pgx StatementCacheCapacity 对齐

max_client_conn 与 default_pool_size 的落差就是池化的意义：五千个空闲客户端，只让四十条连接压在 PG 上。

双层池的配比公式：每实例 pgxpool.MaxConns × 实例数 ≤ PgBouncer default_pool_size(按库) < 有效 max_connections。Go 侧 pgxpool 管进程内复用，PgBouncer 管跨实例削峰，两层各调各的（pgxpool 参数见 08-Go客户端pgx.md）。

高频慢查询处方

场景	原因	动作
`ORDER BY x LIMIT n` 全表扫+大排序	索引方向/列序没对上	按排序列建索引（方向一致）、复合条件+排序用 `(过滤列, 排序列)`；验证 plan 里 Seq Scan+Sort 消失，见 `04-索引实战.md`
大表 `COUNT(*)` 永远慢	必扫全表（MVCC 语义）	能接受近似值用 `SELECT reltuples::bigint FROM pg_class WHERE relname='orders'`（统计估算）；要精确按维度就维护物化视图（见 `07-高级SQL.md`）
接口 P99 高但单条 SQL 都不慢	ORM 惯性 N+1	pg_stat_statements 里找"同指纹高 calls”；改批量 `IN`/JOIN，gorm 用 Preload，见 `gorm/` 手册
昨天还好好的今天慢	autovacuum-analyze 欠账，统计过期	`SELECT last_analyze, last_autoanalyze, n_mod_since_analyze FROM pg_stat_user_tables WHERE relname='orders';` 补 `ANALYZE`，长期修 autovacuum 周期，见 `06-MVCC与Vacuum.md`
查询看起来"卡住"不是慢是等锁	锁等待混进慢查询指标	`pg_stat_activity` 看 `wait_event_type='Lock'` 找持锁源头，见 `05-事务与锁.md`；`log_lock_waits=on`
索引存在却不用 / 用了反而慢	索引膨胀、btree 深度异常	`pg_stat_user_indexes` 看 idx_scan，膨胀检查见 `06-MVCC与Vacuum.md`；`REINDEX (CONCURRENTLY)` 重建
多表 join 计划忽好忽坏	超过 12 表进 GEQO 启发式搜索，或估算抖	固定 join 顺序诊断（`join_collapse_limit=1`）找最优排列，再考虑拆 SQL / 物化中间结果

诊断流程小结

收到慢查询告警，按这个顺序走，别一上来就调参数：

先看资源大盘：OS 层 CPU/iowait/内存，PG 层 pg_stat_database 的 blks_hit/blks_read（缓存命中率掉了，优化 SQL 是缘木求鱼）。
先看当下：SELECT pid, now()-query_start AS age, state, wait_event_type, wait_event, left(query,60) FROM pg_stat_activity WHERE datname=current_database() ORDER BY age DESC; ——分清"慢在跑"还是"慢在等"，等锁直接转 05 章。
定位嫌疑语句：pg_stat_statements 按 total_exec_time 取 Top10，拿指纹和参数样本。
重放计划：EXPLAIN (ANALYZE, BUFFERS)，重点看三件事：估算/实际 rows 偏差倍数、loops 是否失控、Buffers read 占比（IO 型）还是全 hit（CPU 型）。
对统计：偏差大 → ANALYZE、看 default_statistics_target、考虑 CREATE STATISTICS；偏差小而计划仍坏 → 进第 5 步。
对索引：缺哪个索引、现有索引是否被列序/方向/类型不匹配挡在门外（04 章）；改写 SQL（OR/深 OFFSET/宽 SELECT）看能否给出更便宜的路径。
对资源：Sort Method: external merge/hash Batches 多 → work_mem 局部加码；Workers Launched < Planned → 并行预算。
最后看维护状态：长事务、死元组、索引/表膨胀、VM 比例（06 章）——这些会让所有前六步都"看起来没毛病但就是慢"。

实战建议

把 EXPLAIN (ANALYZE, BUFFERS) 练成条件反射；读 plan 先扫"est vs actual 偏差 > 10x"的行，再分析算子本身。
永远记住 rows 是乘法链：外侧估小会让 nested loop 内侧 loops 爆炸，估算失真是大多数"坏计划"的真凶——修统计优先于写 hint 式开关。
random_page_cost=1.1、合理 shared_buffers、按列提 STATISTICS、pg_stat_statements + auto_explain，这四件事是任何 SSD 生产库的基线配置。
连接数管理从架构层面做：实例 pgxpool 小池、PgBouncer transaction 大池、PG 侧 max_connections 克制，三层预算对账一次写进部署文档。
慢查询先分清"跑 vs 等"：pg_stat_activity 的 wait_event 是分流阀，等锁的查询优化 SQL 没有意义。
报表/导入/在线交易别共用一套池与 work_mem：用独立 database 或 ALTER ROLE ... SET work_mem 分档隔离。
任何参数改动都要有前后对照的 EXPLAIN 或 pg_stat_statements 快照——“感觉快了"不算数。

Go 客户端 pgx 复制与高可用