第 17 章性能调优：从一行 SQL 到一台机器的完整调优地图

目标读者：能写 SQL，跑过 EXPLAIN，但一遇到「线上慢了怎么办」就不知道从哪儿下手的同学。
学完你会：有一套自上而下、由 SQL 到硬件的诊断流程；会用 pg_stat_statements、auto_explain 找慢 SQL；能给一台新机器写出靠谱的 postgresql.conf；知道 PgBouncer 为什么必装。

0. 导读：性能调优的「第一性原理」

很多人一说「PG 慢了」第一反应是改 shared_buffers、加 work_mem，然后发现没用、再改回来。这是典型的**「凭直觉调优」**，最后只会变成玄学。

正确的姿势只有一句话：

先观测（observe），找到瓶颈在哪一层，再针对性优化。

PG 的性能瓶颈一共就 4 层，自上而下：

┌─────────────────────────────────────┐
│ 1. SQL 层      (慢 SQL、N+1、深翻)   │  ← 80% 的问题在这里
├─────────────────────────────────────┤
│ 2. 索引 / 统计信息 (缺索引、坏统计)   │
├─────────────────────────────────────┤
│ 3. 服务端配置 (shared_buffers / 连接) │
├─────────────────────────────────────┤
│ 4. 硬件 / OS  (磁盘 IOPS、网络、CPU)  │
└─────────────────────────────────────┘

经验比例：80% 的「慢」都能在 SQL + 索引层解决，不要一上来就动 postgresql.conf。换句话说：你以为的「PG 慢」，绝大多数是「SQL 烂」。

本章就按这 4 层从上往下讲，最后再给你一套慢查询排查链路和生产监控方案。

1. 调优方法论：先观测，再下手

1.1 「测量优先」的生活类比

想象你家水管漏水，地上湿了。你的第一反应应该是「先关总闸」「再去看哪一段渗水」，而不是「我猜是 3 楼那段」就直接砸墙。
但很多人调 PG 就跟「直接砸墙」一样：没看任何指标，先把 shared_buffers 翻倍试试。

1.2 必备的三个观测工具

工具	作用	一句话
`pg_stat_statements`	聚合所有 SQL 的执行次数 / 总时长 / 平均 / 缓存命中	「Top SQL 雷达」
`auto_explain`	自动记录慢查询的 EXPLAIN 计划到日志	「慢 SQL 黑匣子」
`EXPLAIN (ANALYZE, BUFFERS)`	看单条 SQL 的真实执行路径 + 真实读了多少 buffer	「显微镜」

外加 PG 自带的 pg_stat_* 视图（数据库、表、索引、bgwriter、replication、activity）做整体健康度监控。

1.3 一个调优会话的标准流程

接到告警：「下单接口 P99 从 80ms 变成 800ms」
打开 pg_stat_statements：按 total_exec_time 排序看 Top 10
找到罪魁 SQL，在 psql 里跑 EXPLAIN (ANALYZE, BUFFERS)
看是 Seq Scan / Sort on disk / Hash Join build > work_mem，对症下药
用 pg_stat_user_indexes / pg_stat_user_tables 验证修复效果
写一句 5 行总结到 wiki，下次别人也能复用

下一节先把 PG 的关键服务端参数讲清楚，否则连「优化方向」都找不到。

2. 关键服务端参数详解

📌 配置文件位置：postgresql.conf（一般在 $PGDATA 下），改了部分参数要 SELECT pg_reload_conf(); 或 pg_ctl reload，少数参数（带 requires restart）必须重启。
用 SHOW name; 或 SELECT * FROM pg_settings WHERE name = 'shared_buffers'; 查看当前值。

2.1 内存类参数（最重要）

`shared_buffers` — PG 自己的缓存池

ini

# 默认 128MB（小到没法用）
# 推荐：物理内存 × 25%
shared_buffers = 4GB

作用：PG 在内存里维护的「页缓存」，读 / 写表和索引都先经过这里，是 InnoDB Buffer Pool 的对应物。

为什么是 25% 而不是 80%？因为 Linux 的 OS Page Cache 也会缓存 PG 数据文件。你给 shared_buffers 80%，OS Page Cache 就只有 20%，反而总命中率下降；而且 PG 的 buffer manager 在大内存下管理开销会变大。25% 是社区多年验证的「甜点」。

📌 与 MySQL 的区别：MySQL InnoDB 推荐 innodb_buffer_pool_size 占 70%~80%，因为 MySQL 不太依赖 OS Page Cache（O_DIRECT 模式下完全绕过）。PG 是「双缓存」哲学。

`effective_cache_size` — 优化器对「总缓存」的认知

ini

effective_cache_size = 12GB   # 内存 50%~75%

作用：只影响代价估算，不实际分配任何内存。它告诉优化器「OS Page Cache + shared_buffers 总共大约有多少缓存可用」，优化器据此判断「索引扫描会不会大概率命中缓存」，从而决定 Index Scan vs Seq Scan。

调小了会怎样？优化器以为缓存少，就保守地少用索引（因为索引扫描默认假设要随机读磁盘）。调大了会怎样？优化器更激进地用索引。

`work_mem` — 每个 sort/hash 操作的内存上限

ini

work_mem = 16MB           # 默认 4MB，太小，几乎一定会落盘排序

作用：执行计划里每一个排序节点 / 哈希节点 / 位图节点最多用多少内存。超出就写临时文件（外排），慢一个数量级。

陷阱：这是「每节点」不是「每查询」。一条复杂 SQL 可能有 5 个 sort + 2 个 hash = 7 × work_mem；再算上 max_connections=200，最坏 200 × 7 × 16MB = 22GB，能把机器搞 OOM。

经验公式：

work_mem ≈ (可用内存 - shared_buffers - 1GB) / max_connections / 4

OLTP（短查询、高并发）：4MB ~ 16MB
OLAP（大查询、低并发）：64MB ~ 512MB
更好的做法：全局保守，关键大查询用 SET LOCAL work_mem = '256MB'; 临时调大

`maintenance_work_mem` — 维护操作专用内存

ini

maintenance_work_mem = 512MB   # 默认 64MB

作用：VACUUM、CREATE INDEX、ALTER TABLE、pg_dump --jobs 用。给大点没坏处，因为同时在跑的维护任务一般只有 1~2 个。256MB ~ 1GB 都合理。

`wal_buffers` — WAL 写出前的缓冲区

ini

wal_buffers = 16MB             # 默认 -1（自动 = shared_buffers/32，上限 16MB）

作用：事务写产生的 WAL 先到这块内存，再落盘。写多场景手动设到 16MB（默认 64MB shared_buffers 时只有 2MB，太小）。

2.2 IO / 优化器代价类

`random_page_cost` / `seq_page_cost`

ini

seq_page_cost = 1.0
random_page_cost = 1.1   # SSD：1.1；机械盘：4.0（默认）；云盘：1.5

为什么 SSD 一定要改？默认 4.0 是 2008 年机械盘时代的参数，假设「随机 IO 比顺序 IO 慢 4 倍」。SSD 上随机和顺序几乎一样快，不改这个参数，优化器会倾向选 Seq Scan，索引白建。

`effective_io_concurrency`

ini

effective_io_concurrency = 200   # SSD/NVMe；机械盘=1；云盘按队列深度估

作用：bitmap heap scan 时同时发起多少 IO。只对底层支持「异步预读」的存储有用。SSD 上调高能让大范围扫描提速 2~5 倍。

2.3 连接 / 并发类

`max_connections`

ini

max_connections = 200   # 不是越大越好！

陷阱：PG 是「一个连接一个进程」，每个连接占 5~10MB 内存 + 有 fork 开销。1000 个连接就是 10GB+ 内存被啃，且大量空闲连接的进程切换会拖垮 CPU。

正确做法：max_connections 控制在 200~500，前面接 PgBouncer 池化。应用层连接池打到 PgBouncer，PgBouncer 复用真实 PG 连接（详见第 6 节）。

📌 与 MySQL 的区别：MySQL 是「一个连接一个线程」，开销小得多，max_connections=2000 也能跑。PG 「连接池」不是可选优化，而是生产标配。

并行查询相关

ini

max_worker_processes = 16              # 后台 worker 上限
max_parallel_workers = 8               # 并行查询全局池
max_parallel_workers_per_gather = 4    # 单条 SQL 最多并行 worker

并行查询适合大表扫描 / 聚合，OLTP 一般不需要。设到 CPU 核数的一半比较合适。

2.4 WAL / Checkpoint 类

ini

wal_level = replica
max_wal_size = 4GB                       # 默认 1GB，写多场景调到 4~16GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9       # 把 checkpoint 写盘均匀摊到 90% 间隔
checkpoint_timeout = 15min
synchronous_commit = on                  # 写多场景可调成 off 或 local

synchronous_commit = off 的含义：事务 commit 立即返回成功，不等 WAL 落盘。最坏丢失最近几百毫秒的事务（永不损坏数据库），写吞吐能翻 2~3 倍。日志型业务（点击流、IoT）可以接受。

2.5 统计信息

ini

default_statistics_target = 100   # 默认 100；大表/复杂查询可到 500~1000

PG 的优化器是基于代价的（CBO），代价靠 ANALYZE 收集的直方图估算。default_statistics_target 控制采样行数（默认 100 桶 → 30000 行采样）。OLAP 场景调到 500 能显著改善大表 JOIN 的估算，但 ANALYZE 也会变慢。

2.6 Autovacuum

ini

autovacuum = on
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.05    # 表行数 5% 死元组就触发
autovacuum_analyze_scale_factor = 0.02

写多的大表要把 scale_factor 调小（默认 0.2 太懒）。第 8 章 MVCC 里讲过「死元组膨胀」，根源就是 autovacuum 跟不上写入速度。

2.7 一张「按内存档位」的速查表

内存	shared_buffers	effective_cache_size	work_mem	maintenance_work_mem
4GB	1GB	2.5GB	4MB	256MB
8GB	2GB	5GB	8MB	512MB
16GB	4GB	10GB	16MB	1GB
32GB	8GB	20GB	32MB	2GB
64GB	16GB	40GB	64MB	2GB

上述 work_mem 假设 max_connections ≈ 200、OLTP 工作负载。OLAP 场景应该按「会话级 SET LOCAL」单独调大。

3. 慢查询排查链路（重点）

3.1 第一站：开慢日志

ini

# postgresql.conf
log_min_duration_statement = 1000        # 记录超过 1s 的 SQL（含参数）
log_lock_waits = on                      # 锁等待超 deadlock_timeout 也记日志
log_temp_files = 10240                   # 用了 >10MB 临时文件就记录（说明 work_mem 不够）
log_autovacuum_min_duration = 1000
log_line_prefix = '%t [%p] %u@%d app=%a '

reload 后日志会写到 log/postgresql-*.log，每条慢 SQL 都附带耗时和具体参数值。

3.2 第二站：`pg_stat_statements`（必装）

PG 自带的「SQL 维度统计聚合器」，按归一化的 SQL 模板聚合，所有 WHERE id = 1 和 WHERE id = 2 算同一条。

安装

ini

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

重启后：

sql

CREATE EXTENSION pg_stat_statements;

经典查询：找 Top 10 总耗时

sql

SELECT
    LEFT(query, 80)                                  AS query,
    calls,
    ROUND(total_exec_time::NUMERIC, 1)               AS total_ms,
    ROUND(mean_exec_time::NUMERIC, 2)                AS mean_ms,
    ROUND(100.0 * shared_blks_hit
                / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

字段含义：

total_exec_time：所有调用累计耗时（毫秒）
mean_exec_time：平均单次耗时
shared_blks_hit / read：从 shared_buffers 命中 / 从磁盘读取的块数

经验：

total 高 → 拖慢系统的元凶（常常是「快但调用很多次」的 SQL）
mean 高 → 单次特别慢（常常是缺索引）
hit_pct < 90% → 缓存命中率低，要么缺索引、要么 shared_buffers 太小

配套代码 code/01_pg_stat_statements_topn.py 演示了完整的三类排序输出。

3.3 第三站：`auto_explain`

pg_stat_statements 只告诉你「哪条 SQL 慢」，不告诉你「慢在哪个节点」。auto_explain 自动把慢查询的 EXPLAIN 计划记到日志：

ini

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = 500     # 超 500ms 的 SQL 自动记 EXPLAIN
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_format = text
auto_explain.log_nested_statements = on

重启后，所有超过 500ms 的查询都会带着完整 EXPLAIN ANALYZE 结果写进日志，凌晨翻日志就能定位问题，不用蹲守现场复现。

3.4 第四站：pgbadger（日志分析）

pgbadger 是个 Perl 脚本，把 PG 日志渲染成 HTML 报表：Top 慢 SQL、按小时分布、错误统计、临时文件统计、Connections graph 一应俱全。

bash

pgbadger /var/lib/postgresql/data/log/postgresql-*.log -o report.html
open report.html

线上每周跑一次，出一份报告，比啥都香。

4. SQL 优化技巧实战

4.1 EXPLAIN 速读三件套

sql

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <你的 SQL>;

ANALYZE：真的执行一遍（注意：UPDATE/INSERT/DELETE 也会真的执行！要包在 BEGIN ... ROLLBACK; 里）
BUFFERS：显示读了多少块、命中多少、读盘多少
VERBOSE：打印输出列、schema 名

读输出要盯三件事：

节点类型：Seq Scan（全表扫）/ Index Scan / Bitmap Heap Scan / Index Only Scan
estimated rows vs actual rows：差 10 倍以上 → 统计信息不准，跑 ANALYZE
Rows Removed by Filter：大说明索引选择度差或没用上索引

4.2 加索引：从 380ms 到 0.6ms

Before（无索引）：

sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM ch17_perf_orders
WHERE user_id = 12345 AND status = 1
ORDER BY created_at DESC LIMIT 20;

-- 输出：
-- Limit  ...
--   ->  Sort  ...
--         Sort Key: created_at DESC
--         ->  Seq Scan on ch17_perf_orders  (rows=200)
--               Filter: ((user_id = 12345) AND (status = 1))
--               Rows Removed by Filter: 999800
--               Buffers: shared hit=10000 read=8000
-- Execution Time: 380.2 ms

Rows Removed by Filter: 999800 就是「为了找 200 行付出了过滤 100 万行的代价」。

After：

sql

CREATE INDEX ch17_idx_perf_orders_us_st_ct
  ON ch17_perf_orders (user_id, status, created_at DESC);

-- 同样的 SQL：
-- Limit  ...
--   ->  Index Scan using ch17_idx_perf_orders_us_st_ct on ch17_perf_orders
--         Index Cond: ((user_id = 12345) AND (status = 1))
--         Buffers: shared hit=23
-- Execution Time: 0.6 ms

提速约 600 倍。复合索引顺序是 (user_id, status, created_at DESC)：等值列在前，排序列在后，正好匹配查询。

配套代码 code/05_explain_diff.py 自动跑前后对比。

4.3 删除多余索引

每个索引都会拖累写入速度（每次 INSERT/UPDATE 都要更新所有索引），且占空间。冗余索引找两类：

① 完全没用过的索引：

sql

SELECT s.schemaname, s.relname, s.indexrelname,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

idx_scan = 0 持续两周以上的索引，基本可以 DROP。

② 前缀重复：

如果同时有 (user_id) 和 (user_id, status)，前者是后者的前缀，几乎可以被替代（PG 的 B-Tree 能用复合索引的前缀做范围查询），只保留长的那个即可。

配套代码 code/02_index_audit.py 实现了这两类审计。

4.4 大表分页：从 OFFSET 到 keyset pagination

sql

-- 反例：OFFSET 越大越慢
SELECT * FROM events ORDER BY ts DESC LIMIT 50 OFFSET 100000;

PG 必须真的扫过前 10 万行再丢掉，深翻越来越慢。

sql

-- 正解：keyset pagination
-- 第一页
SELECT * FROM events ORDER BY ts DESC, id DESC LIMIT 50;
-- 后续页：记住上一页最后一行的 (ts, id)
SELECT * FROM events
WHERE (ts, id) < ($last_ts, $last_id)
ORDER BY ts DESC, id DESC LIMIT 50;

复合索引 (ts DESC, id DESC) 覆盖时，每页都是 O(log N + K)，与翻到第几页无关。

配套代码 code/03_keyset_pagination.py 实测：OFFSET 400000 用了 392ms，keyset 第 8001 页只要 1.1ms。

4.5 COUNT(*) 慢怎么办？

PG 的 MVCC 决定了 COUNT(*) 必须扫一遍可见元组（不像 MyISAM 有缓存计数）。

场景	解法
想要近似行数	`SELECT reltuples::BIGINT FROM pg_class WHERE relname='t';`（瞬时返回）
想要带 `WHERE` 的近似计数	用 `EXPLAIN` 解析 `rows=` 字段
想要精确计数（小表）	直接 `COUNT(*)`，注意要有索引扫不要全表扫
想要精确计数（大表，频繁查）	维护「计数器表」+ 触发器，写入时增减
想要分组精确计数	物化视图 `MATERIALIZED VIEW` + 定时刷新

4.6 批量写入用 COPY

COPY 是 PG 的「批量装载协议」，跳过 SQL 解析阶段：

写入方式	50000 行耗时	吞吐
单行 INSERT	6.5s	7,700 rows/s
多行 INSERT (1000/批)	0.55s	91,000 rows/s
COPY FROM STDIN	0.12s	416,000 rows/s

配套代码 code/04_copy_vs_insert.py 实测，psycopg v3 提供了 with cur.copy(...) 的优雅 API。

4.7 JSONB 查询要用 GIN 索引

sql

-- 反例：取 JSONB 内字段做等值不走索引
SELECT * FROM events WHERE payload->>'channel' = 'app';

-- 正解 ①：在表达式上建索引
CREATE INDEX idx_events_channel ON events ((payload->>'channel'));

-- 正解 ②：改写 SQL 用 @> 操作符 + GIN 索引
CREATE INDEX idx_events_payload_gin ON events USING gin (payload jsonb_path_ops);
SELECT * FROM events WHERE payload @> '{"channel": "app"}';

@> 是「包含」操作符，能直接用 GIN 索引；->> 取字段后比较只能用表达式索引。

4.8 子查询改 JOIN

sql

-- 反例：相关子查询，外层 N 行就触发 N 次内层
SELECT u.*, (SELECT count(*) FROM orders o WHERE o.user_id = u.id) AS cnt
FROM users u;

-- 正解：JOIN + GROUP BY，一次扫描搞定
SELECT u.*, COALESCE(o.cnt, 0) AS cnt
FROM users u
LEFT JOIN (SELECT user_id, count(*) AS cnt FROM orders GROUP BY user_id) o
       ON o.user_id = u.id;

PG 的优化器对很多相关子查询能自动改写成 JOIN，但不是所有，写 SQL 时主动改最稳妥。

4.9 避免 SELECT *

不仅是「网络传输浪费」，更关键的是：SELECT * 会让 Index Only Scan 失效。

Index Only Scan 要求所有要返回的列都在索引里。SELECT id, name + 索引 (id, name) 能走 Index Only；SELECT * 一定要回堆表。

5. 性能反模式 / 踩坑集

反模式	后果	对策
长事务（小时级）	MVCC 死元组无法回收 → 表膨胀 → 全表慢	拆短事务，别在事务里 sleep / 等外部 API
`max_connections = 5000` 不用池	进程切换爆炸 + 内存被啃	接 PgBouncer / Pgpool
关 autovacuum 自己手动 VACUUM	一定会忘记，最终 XID 回卷	autovacuum 永远开，只调阈值
大表 `ORDER BY x LIMIT 10` 没索引	全表 + 排序，极慢	在 `x` 上加索引（最好是 `DESC`）
`WHERE col::TEXT = '1'`	类型转换让索引失效	让两边类型匹配，或建表达式索引
`WHERE upper(name) = 'ALICE'`	函数调用让索引失效	表达式索引 `((upper(name)))` 或用 `citext`
JSONB 用 `->'k' = 'v'`	走不了 GIN	改 `@>` 或表达式索引
`LIKE '%abc%'`	走不了 B-Tree	装 `pg_trgm`，建 GIN/GiST 索引
`pg_dump` 在业务时段	长事务 + 重 IO	凌晨跑，或在备库跑

6. 连接管理：为什么必须有连接池

6.1 PG 进程模型的代价

psql ─tcp─►  postmaster  ─fork()─►  backend 进程 1（专门服务这个连接）
                          ─fork()─►  backend 进程 2
                          ─fork()─►  ...

每个 backend 是独立 OS 进程，启动 ≈ 5~15ms，常驻内存 5~10MB。1000 个连接 = 1000 个进程 = 5~10GB 进程开销 + N 万次/秒上下文切换。

6.2 PgBouncer（推荐）

PgBouncer 是个 C 写的轻量级连接池，单进程异步 IO，能把上层 1 万个客户端连接复用到下层 50 个 PG 真实连接。

三种池化模式（重点理解）：

模式	行为	适用场景
session	一个客户端连接占用一个真实连接，直到断开	等同没池化，仅做监控
transaction（推荐）	客户端事务开始时分配，事务结束归还	95% 业务场景
statement	每条 SQL 结束就归还（不允许多语句事务）	极特殊，几乎不用

Transaction pooling 限制：因为每个事务可能落在不同的真实连接上，所以不能用 SET、临时表、LISTEN/NOTIFY、prepared statement（除非配 server_reset_query）。绝大多数 ORM 默认就不用这些，影响小。

最简配置：

ini

# pgbouncer.ini
[databases]
learn_pg = host=127.0.0.1 port=5432 dbname=learn_pg

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = userlist.txt
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 50

应用连 localhost:6432 当成 PG 用即可。

6.3 Pgpool-II

功能更全：连接池 + 读写分离 + 负载均衡 + 自动故障切换。但配置比 PgBouncer 复杂得多，且性能略低。只有需要内置读写分离时才上 Pgpool-II，纯池化用 PgBouncer。

6.4 应用层池

Java：HikariCP（事实标准）
Python：SQLAlchemy 的 QueuePool、psycopg3 的 psycopg_pool
Node.js：pg.Pool

应用层池主要管「TCP 连接复用」，池大小不要超过 PgBouncer 的 default_pool_size。

7. 监控指标体系

7.1 关键 `pg_stat_*` 视图

视图	看什么
`pg_stat_database`	每个库的事务数、提交/回滚、死锁、临时文件
`pg_stat_user_tables`	每张表的 seq_scan / idx_scan / 写入行数 / 死元组
`pg_stat_user_indexes`	每个索引的扫描次数、扫到/返回行数
`pg_stat_bgwriter`	bg writer / checkpoint 写脏页统计
`pg_stat_replication`	备库延迟（write_lag / flush_lag / replay_lag）
`pg_stat_activity`	当前所有连接的状态、SQL、等待事件
`pg_stat_wal`（PG14+）	WAL 写入字节数、生成 WAL 速率
`pg_locks`	当前所有锁；与 `pg_stat_activity` JOIN 查锁等待
`pg_buffercache` 扩展	shared_buffers 里到底缓存了哪些页

7.2 几个常备 SQL

sql

-- 当前正在运行的长查询（>10s）
SELECT pid, age(clock_timestamp(), query_start), usename, state, query
FROM pg_stat_activity
WHERE state <> 'idle' AND query NOT ILIKE '%pg_stat_activity%'
  AND clock_timestamp() - query_start > interval '10 seconds'
ORDER BY query_start;

-- 缓存命中率（应 > 99%）
SELECT
  sum(blks_hit)*100.0 / NULLIF(sum(blks_hit) + sum(blks_read), 0) AS hit_ratio
FROM pg_stat_database;

-- 表的死元组比例（>20% 该 VACUUM）
SELECT relname,
       n_live_tup, n_dead_tup,
       ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 1000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 20;

-- 最伤 IO 的表（buffer hit rate 最低）
SELECT relname,
       heap_blks_read, heap_blks_hit,
       ROUND(100.0 * heap_blks_hit / NULLIF(heap_blks_hit + heap_blks_read, 0), 2) AS hit_pct
FROM pg_statio_user_tables
WHERE heap_blks_read + heap_blks_hit > 10000
ORDER BY hit_pct ASC;

7.3 接入 Prometheus + Grafana

事实标准：prometheus_community/postgres_exporter

bash

docker run --name pg_exporter -d \
  -e DATA_SOURCE_NAME="postgresql://postgres:pwd@host.docker.internal:5432/postgres?sslmode=disable" \
  -p 9187:9187 \
  quay.io/prometheuscommunity/postgres-exporter

Prometheus scrape :9187/metrics
Grafana 导入 dashboard ID 9628（"PostgreSQL Database"）即可看几十个图：QPS、连接数、缓存命中、复制延迟、长事务、死元组等。

生产建议：再加一个 pgwatch2 或 Datadog DBM 做 Top SQL 切片。

8. 常见问题排查决策树

线上报警「DB 慢」
    │
    ├── pg_stat_activity 看是否有长事务 / 锁等待
    │      └── 是 → 找应用方杀 / 优化业务，杀 PID 用 pg_terminate_backend()
    │
    ├── pg_stat_statements 看 Top 10 total_exec_time
    │      ├── 单条占比 > 50% → EXPLAIN ANALYZE 这条 SQL
    │      └── 平摊很均 → 看是否 QPS 上来了（业务高峰）
    │
    ├── 缓存命中率 < 95% → 看 shared_buffers 是否过小 / 是否在大表扫
    │
    ├── 写入慢 → 看 checkpoint_warning 日志、bgwriter 写脏页统计
    │
    ├── 复制延迟 → pg_stat_replication 的 replay_lag
    │
    └── CPU 100% → top 看是 backend 还是 autovacuum；
                  perf top 看 PG 内部热点函数

9. 与 MySQL 的对比

维度	MySQL（InnoDB）	PostgreSQL
缓存池	Buffer Pool（推荐 70~80% 内存）	shared_buffers（推荐 25%）+ OS Page Cache
进程模型	一连接一线程	一连接一进程，必须连接池
慢查询日志	`slow_query_log` + `long_query_time`	`log_min_duration_statement`
SQL 统计	Performance Schema / `events_statements_*`	`pg_stat_statements` 扩展
自动 EXPLAIN	无原生（需 ProxySQL / sys 模式拼装）	`auto_explain` 扩展
缓存命中率指标	`Innodb_buffer_pool_read_requests / reads`	`pg_stat_database.blks_hit / read`
优化器代价模型	较弱，靠 hint	CBO 较强，但不支持 hint（用 `pg_hint_plan` 扩展）
MVCC 行为	undo log，旧版本丢到 undo 表空间	版本元组在表内，靠 VACUUM 清理
日志分析工具	pt-query-digest	pgbadger
连接池	一般用 ProxySQL	一般用 PgBouncer

10. 小结

方法论：观测 → 定位 → 优化 → 复测，永远不要凭直觉调 GUC。
80% 的 PG 慢是 SQL/索引问题，不是配置问题。pg_stat_statements 是必装入口。
关键参数四件套：shared_buffers（内存 25%）、effective_cache_size（60%）、work_mem（按公式估）、random_page_cost（SSD 调 1.1）。
PG 必须有连接池：进程模型决定了 max_connections 不能开太大，前面接 PgBouncer transaction pooling。
慢日志 + auto_explain + pgbadger 是事后排查铁三角。
写入用 COPY、深翻用 keyset、JSONB 用 GIN、模糊搜索用 pg_trgm：都是把「全表扫」换成「索引扫」。
监控接 postgres_exporter + Grafana，配合 Top SQL 切片工具一份完整可观测性。

记住一句话：「能读懂 EXPLAIN，胜过会改十个 GUC」。

🎮 配套演示

用浏览器打开 ./17_performance/demo.html，跟着可视化动画再走一遍本章核心概念。
配套代码在 ./17_performance/code/，每个脚本都可以独立 python xxx.py 运行，先跑 init.sql 准备百万行数据。

11. 面试高频题

Q1：`shared_buffers` 设多大合适？为什么不是越大越好？

考察点：PG 缓存哲学、与 MySQL 的差异、对 OS Page Cache 的认知。

答案：

推荐 25% 物理内存（4GB ~ 16GB 这个区间，绝对值上限一般不超过 32GB）。
为什么不是 80%？ PG 是双缓存哲学：自己的 shared_buffers 只是「热数据」缓存，依赖 Linux 的 OS Page Cache 做二级缓存。给 shared_buffers 80% 会挤掉 Page Cache，反而降低总命中率。
再大边际收益骤降：PG 的 buffer manager（clock-sweep 算法）在大内存下管理开销变大，且 checkpoint 时要刷的脏页变多，IO 突刺更严重。
改完必须重启：shared_buffers 是 requires restart 的参数，不是 reload。
怎么判断够不够大？ 看 pg_stat_database.blks_hit / (blks_hit + blks_read)，目标 > 99%。如果热数据集本身就 > 内存，加再多也没用，应该上读写分离 / 分区。
与 MySQL 对比：MySQL InnoDB 推荐 70~80%，因为 InnoDB 用 O_DIRECT 模式绕过 OS Page Cache，是「单缓存」哲学。两边的最佳实践数值不能照搬。

加分项：能说出「pg_buffercache 扩展可以查 shared_buffers 里具体缓存了哪些表/索引页」。

Q2：为什么 PG 必须用连接池？PgBouncer 的 transaction pooling 模式有什么坑？

考察点：PG 进程模型、PgBouncer 三种模式、ORM 兼容性。

答案：

PG 是「一个连接一个进程」模型：每个 backend 是独立 OS 进程，启动开销 5~15ms，常驻 5~10MB 内存。1000 连接就是 1000 进程，fork 开销 + 进程间上下文切换会拖垮 CPU，内存也撑不住。
MySQL 是线程模型：开销小一个数量级，所以 MySQL 一般不用池子也能跑大几千连接。PG 不用池子在生产是高危的。
PgBouncer 三种模式：
- session（默认）：客户端连接持续占用一个真实连接，相当于没池
- transaction（推荐）：事务开始时分配真实连接，事务结束归还。能把 5000 客户端复用到 50 真实连接
- statement：每条 SQL 完就归还，不允许多语句事务，几乎没人用
transaction pooling 的坑：因为每个事务可能落在不同的真实连接上：
- 不能用 SESSION 级状态：SET、临时表、LISTEN/NOTIFY、advisory lock（除非用 set_local/在事务内）
- prepared statement 默认会失效（PG 14+ 配 server_reset_query_always 或用客户端 prepare 模式可以缓解）
- 应用层连接池大小不能超过 PgBouncer pool_size（否则后端会先满）
Pgpool-II：池 + 读写分离 + 负载均衡，功能全但复杂；纯池化场景一律 PgBouncer。

加分项：知道 pgbouncer/show pools、show stats 命令；知道 PG 14+ 引入了 idle_in_transaction_session_timeout 来兜底解决「应用拿了连接不还」。

Q3：`work_mem` 怎么设？为什么不能简单粗暴地调到 1GB？

考察点：work_mem 是「每节点」语义、并发放大效应、临时文件落盘。

答案：

作用域：work_mem 是「执行计划中每一个排序 / 哈希 / 位图节点」的内存上限，不是「每查询」。一条复杂 SQL 可能有 5 个 sort + 2 个 hash = 7 × work_mem。
被并发放大：再乘上 max_connections，最坏 200 × 7 × 1GB = 1.4TB，机器 OOM。
经验公式：work_mem ≈ (可用内存 - shared_buffers - 1GB OS) / max_connections / 4。OLTP 4~16MB，OLAP 64~512MB。
更优做法：全局保守，关键大查询会话级 SET LOCAL work_mem = '256MB'（事务结束自动还原）。
怎么知道太小？看 log_temp_files = 10240（>10MB 临时文件就记日志）；执行计划里出现 Sort Method: external merge Disk: ...kB 就是落盘排序，慢一个数量级。
与 hash join 的关系：HashJoin 的 build 端要全装进 work_mem，超出会 batch 多轮，复杂度从 O(N+M) 变 O(N+M+磁盘 IO)。

加分项：知道 PG 13+ 引入了 hash_mem_multiplier，可以单独给 hash 节点放大 work_mem 而不影响排序节点。

Q4：怎么用 PG 自带工具排查一条慢 SQL？请描述完整链路

考察点：pg_stat_statements、auto_explain、EXPLAIN、pg_stat_activity 联动。

答案：

第一步：看慢日志。log_min_duration_statement = 1000 记录所有 > 1s 的 SQL；auto_explain 自动附 EXPLAIN 计划。

第二步：pg_stat_statements 找 Top SQL。

sql

SELECT query, calls, total_exec_time, mean_exec_time,
       shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

看 total 找「总耗时大户」，看 mean 找「单次最慢」，看 blks_read 找「最伤 IO」。

第三步：拿到具体 SQL，跑 EXPLAIN (ANALYZE, BUFFERS, VERBOSE)。重点看：
- 节点类型（Seq Scan 还是 Index Scan）
- estimated rows vs actual rows（差 10 倍以上 → ANALYZE 收集统计信息）
- Rows Removed by Filter 大 → 索引选择度差
- Sort Method: external → work_mem 不够

第四步：看现场状态。

sql

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle';

看是不是被锁等待（wait_event_type = 'Lock'）。

第五步：对症修复。缺索引就 CREATE INDEX，统计信息不准就 ANALYZE，长事务就杀进程，配置不当就调 GUC。
最后：用 pgbadger 跑一份周报，看趋势变化。

加分项：知道 EXPLAIN ANALYZE 对 INSERT/UPDATE/DELETE 也会真的执行，需要包在 BEGIN ... ROLLBACK；知道 PG 14+ 的 compute_query_id 让 pg_stat_statements 和 auto_explain 能通过 queryid 关联。

Q5：业务说「OFFSET 10000 LIMIT 50 越来越慢」，怎么优化？

考察点：OFFSET 实现原理、keyset pagination、复合索引设计。

答案：

为什么 OFFSET 慢：PG 必须真的扫过前 N 行再丢掉，深翻越来越慢。OFFSET 100000 LIMIT 50 ≈ 扫 10 万行 + 丢 + 取 50 行。
解法 1：keyset pagination（推荐）。记住上一页最后一行的排序键，下一页用 WHERE (sort_key) < ($last_value) 直接定位：
sql
```
-- 第一页
SELECT * FROM events ORDER BY ts DESC, id DESC LIMIT 50;
-- 后续页
SELECT * FROM events
WHERE (ts, id) < ($last_ts, $last_id)
ORDER BY ts DESC, id DESC LIMIT 50;
```
1
2
3
4
5
6
配合复合索引 (ts DESC, id DESC)，每页 O(log N + K)，与「翻到第几页」无关。
为什么要带 id 当 tie-breaker：如果 ts 不唯一，(ts) < $last_ts 会漏掉 / 重复同一秒内的多行。复合排序键必须严格全序。
解法 2：游标分页。前端不再传「页码」，改传「上一页最后一行的 cursor」（base64 编码 sort_key），RESTful 风格。GitHub API、Slack API 都是这种设计。
解法 3：物化排名。如果一定要支持「跳到第 N 页」，预计算 ROW_NUMBER() 写到一张物化视图，定时刷新。
绝对不能用的反例：在大表上 ORDER BY rand() LIMIT N、在 OFFSET 上拼公式跳页。

加分项：能说出「无限滚动场景天然适合 keyset，不存在『跳页』需求」；能给出复合索引顺序的考量（等值列在前、范围/排序列在后）。

Q6：`autovacuum` 是干嘛的？关掉自己手动 VACUUM 行不行？

考察点：MVCC 死元组、XID 回卷、autovacuum 阈值参数。

答案：

autovacuum 的作用：后台进程自动跑 VACUUM（清理死元组，回收空间）+ ANALYZE（更新统计信息）。
触发条件（每个表独立）：
- VACUUM：n_dead_tup > autovacuum_vacuum_threshold + scale_factor × n_live_tup（默认 50 + 20% 表行数）
- ANALYZE：类似公式，默认 10%
- 防 XID 回卷：表年龄超 autovacuum_freeze_max_age（默认 2 亿）强制触发，无法绕过
为什么不能关：
- 死元组持续累积 → 表膨胀 → 全表慢、磁盘爆
- XID 回卷：PG 的事务 ID 是 32 位，绕了一圈就会撞旧元组的 xmin，整个数据库进入只读模式自我保护，必须停服手动 VACUUM 抢救
- 统计信息不更新 → 优化器估算不准 → 用错执行计划
正确姿势：永远开 autovacuum，只调阈值不关开关：
- 写多的大表：autovacuum_vacuum_scale_factor = 0.05（5% 死元组就触发）
- 大表的 ANALYZE 慢：调高 default_statistics_target 让采样更准但不更频繁
- autovacuum 抢不到 IO：增加 worker 数 autovacuum_max_workers、调小 autovacuum_vacuum_cost_delay
何时手动 VACUUM (VERBOSE, ANALYZE)？ 大批量删除 / UPDATE 之后立刻跑一次，加快回收；导出前跑 ANALYZE 让 pg_dump 估算准确。
VACUUM FULL 会重写整张表 + 加 ACCESS EXCLUSIVE 锁（业务停摆），生产慎用，更推荐 pg_repack 扩展。

加分项：能说出 PG 13+ 的 vacuum_index_cleanup、PG 14+ 的并行 vacuum；知道 pg_stat_progress_vacuum 可实时看 VACUUM 进度。

Q7：`random_page_cost` 默认 4.0 在 SSD 上为什么必须改？

考察点：优化器代价模型、SSD vs HDD 的随机/顺序 IO 比、参数级联效应。

答案：

代价模型：PG 的 CBO 把每个执行节点的代价拆成：

cost = startup_cost + run_cost
     = CPU 代价 + IO 代价
IO 代价 = 顺序读页数 × seq_page_cost + 随机读页数 × random_page_cost

默认 4.0 的来源：2008 年时代，机械盘随机 IO 慢于顺序 IO 4 倍是合理估计。
SSD/NVMe 的现实：随机 IOPS 与顺序 IOPS 几乎相同（差距 < 1.5 倍）。
不改的后果：优化器认为「索引扫描会有大量随机 IO，代价高」，会倾向选 Seq Scan，结果你建了索引也用不上。
推荐值：
- SSD / NVMe：random_page_cost = 1.1
- 云盘（中等延迟）：1.5
- 机械盘：保留 4.0
配套改的还有 effective_io_concurrency：SSD 设到 200+，让 bitmap heap scan 同时发多路 IO；机械盘只能设 1。
怎么验证生效：改完跑一条原本走 Seq Scan 的 SQL 看 EXPLAIN，应该改成 Index Scan。

加分项：知道每张表可以单独 ALTER TABLESPACE ssd_ts SET (random_page_cost = 1.1)，混合存储时用得上；知道 PG 12+ 引入的 cpu_operator_cost / cpu_tuple_cost 也会影响计划。

Q8：怎么给一台 16GB / 8 核 / SSD 的新机器写 `postgresql.conf`？

考察点：参数综合调优、各档位经验值。

答案：

ini

# ===== 内存 =====
shared_buffers = 4GB                       # 内存 25%
effective_cache_size = 12GB                # 内存 75%
work_mem = 16MB                            # (16-4-1)/200/4 ≈ 14, 取 16
maintenance_work_mem = 1GB                 # 不超过 2GB

# ===== WAL =====
wal_buffers = 16MB
max_wal_size = 4GB                         # 写多场景调大避免频繁 checkpoint
min_wal_size = 1GB
checkpoint_completion_target = 0.9
synchronous_commit = on                    # 金融场景 on，日志类可 off

# ===== IO/优化器 =====
random_page_cost = 1.1                     # SSD 必改
effective_io_concurrency = 200             # SSD 推荐
seq_page_cost = 1.0

# ===== 并行 =====
max_worker_processes = 8                   # = CPU 核数
max_parallel_workers = 4
max_parallel_workers_per_gather = 2

# ===== 连接 =====
max_connections = 200                      # 配合 PgBouncer，不要更大
idle_in_transaction_session_timeout = 60s

# ===== 统计 =====
default_statistics_target = 100            # OLAP 调到 500

# ===== Autovacuum =====
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.05      # 写多表更激进

# ===== 日志 =====
log_min_duration_statement = 1000
log_lock_waits = on
log_temp_files = 10240
log_autovacuum_min_duration = 1000

# ===== 扩展 =====
shared_preload_libraries = 'pg_stat_statements,auto_explain'

加分项：能说出「这只是起点，最终值要根据 pg_stat_statements、监控指标迭代调整」；提到不同业务（OLTP/OLAP/HTAP）应该有不同的基线模板。

🔗 延伸阅读

第 6 章索引与查询优化：本章「冗余索引扫描」「键集分页」依赖的索引选择基础知识。
第 8 章事务与 MVCC：长事务/HOT 链/膨胀如何把性能压到地板，与本章 autovacuum 调参互补。
第 10 章 WAL 与参数：shared_buffers / wal_buffers / checkpoint_* 这些 GUC 的语义来源，搞懂背景再调参。

下一章预告：第 18 章「常用扩展生态」—— PostGIS、pgvector、TimescaleDB、pg_cron……PG 真正的「秘密武器」。

🎬 可视化演示

演示加载缓慢或样式异常？点此在新标签页打开 ↗

💻 示例代码

python

"""01_pg_stat_statements_topn.py —— 第 17 章配套代码 #1

用途
    通过 pg_stat_statements 找出当前数据库中「最慢 / 最频繁 / 最耗 IO」的 Top N 条 SQL。
    这是生产环境慢 SQL 排查的第一站：与其凭直觉猜哪条 SQL 慢，不如让 PG 自己告诉你。

前置条件
    1. 已安装并启用 pg_stat_statements：
           shared_preload_libraries = 'pg_stat_statements'   # postgresql.conf
           CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
    2. 安装 psycopg：pip install "psycopg[binary]>=3.1"

运行方式
    python 01_pg_stat_statements_topn.py [--top 10] [--reset]

输出说明
    脚本会先打印「按总耗时排序」的 Top N，再打印「按平均耗时排序」的 Top N，
    再打印「按平均缓存命中率排序」最差的 Top N。
"""

from __future__ import annotations

import argparse
import sys

try:
    import psycopg
except ImportError:
    sys.exit('请先安装 psycopg v3：pip install "psycopg[binary]>=3.1"')


CONN_INFO = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"


SQL_TOTAL_TIME = """
SELECT
    queryid,
    LEFT(query, 80)                                  AS query,
    calls,
    ROUND(total_exec_time::NUMERIC, 1)               AS total_ms,
    ROUND(mean_exec_time::NUMERIC, 2)                AS mean_ms,
    rows,
    ROUND(
        100.0 * shared_blks_hit
              / NULLIF(shared_blks_hit + shared_blks_read, 0)
        , 2
    )                                                AS hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT %s
"""

SQL_MEAN_TIME = """
SELECT
    queryid,
    LEFT(query, 80) AS query,
    calls,
    ROUND(mean_exec_time::NUMERIC, 2)  AS mean_ms,
    ROUND(stddev_exec_time::NUMERIC,2) AS stddev_ms,
    rows
FROM pg_stat_statements
WHERE calls > 1            -- 调用过 1 次以上才有统计意义
ORDER BY mean_exec_time DESC
LIMIT %s
"""

SQL_LOWEST_HIT = """
SELECT
    queryid,
    LEFT(query, 80) AS query,
    calls,
    shared_blks_read,
    shared_blks_hit,
    ROUND(
        100.0 * shared_blks_hit
              / NULLIF(shared_blks_hit + shared_blks_read, 0)
        , 2
    ) AS hit_pct
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 100
ORDER BY hit_pct ASC NULLS LAST
LIMIT %s
"""


def banner(text: str) -> None:
    print()
    print("=" * 80)
    print(f" {text}")
    print("=" * 80)


def show(cur, sql: str, top_n: int, headers: list[str]) -> None:
    cur.execute(sql, (top_n,))
    rows = cur.fetchall()
    if not rows:
        print("  (空) —— 当前 pg_stat_statements 没有满足条件的记录")
        return
    widths = [max(len(str(h)), max((len(str(r[i])) for r in rows), default=0))
              for i, h in enumerate(headers)]
    line = "  " + "  ".join(f"{h:<{widths[i]}}" for i, h in enumerate(headers))
    print(line)
    print("  " + "  ".join("-" * w for w in widths))
    for r in rows:
        print("  " + "  ".join(f"{str(r[i]):<{widths[i]}}" for i in range(len(headers))))


def ensure_extension(cur) -> None:
    cur.execute("CREATE EXTENSION IF NOT EXISTS pg_stat_statements")


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("--top", type=int, default=10, help="Top N，默认 10")
    parser.add_argument("--reset", action="store_true", help="先重置 pg_stat_statements 再退出")
    args = parser.parse_args()

    try:
        conn = psycopg.connect(CONN_INFO, autocommit=True)
    except psycopg.OperationalError as exc:
        sys.exit(f"连接失败：{exc}")

    with conn, conn.cursor() as cur:
        try:
            ensure_extension(cur)
        except psycopg.errors.UndefinedFile:
            sys.exit(
                "❌ pg_stat_statements 未编译进 PG，请检查 contrib 模块是否安装：\n"
                "   apt install postgresql-contrib  或  yum install postgresql-contrib"
            )

        if args.reset:
            cur.execute("SELECT pg_stat_statements_reset()")
            print("✅ pg_stat_statements 已重置。")
            return

        # 让脚本本身的 SQL 也产生一些样本，方便没数据时也能看到东西
        for _ in range(3):
            cur.execute("SELECT count(*) FROM ch17_perf_orders WHERE status = 1")
            cur.execute("SELECT * FROM ch17_perf_orders WHERE id = 12345")

        banner(f"Top {args.top} · 总耗时最高（找「占用 DB 时间最多」的 SQL）")
        show(cur, SQL_TOTAL_TIME, args.top,
             ["queryid", "query", "calls", "total_ms", "mean_ms", "rows", "hit_pct"])

        banner(f"Top {args.top} · 平均耗时最高（找「单次最慢」的 SQL）")
        show(cur, SQL_MEAN_TIME, args.top,
             ["queryid", "query", "calls", "mean_ms", "stddev_ms", "rows"])

        banner(f"Top {args.top} · 缓存命中率最低（找「最伤 IO」的 SQL）")
        show(cur, SQL_LOWEST_HIT, args.top,
             ["queryid", "query", "calls", "blks_read", "blks_hit", "hit_pct"])

        print("\n💡 提示：")
        print("   · total_ms = mean_ms × calls，先看 total 找「拖慢系统的元凶」；")
        print("   · 再看 mean_ms 找「单次特别慢」的 SQL（可能是缺索引）；")
        print("   · 命中率 < 90% 说明该 SQL 在频繁访问磁盘，要么缺索引、要么 shared_buffers 太小。")


if __name__ == "__main__":
    main()

python

"""02_index_audit.py —— 第 17 章配套代码 #2

用途
    自动审计当前数据库的索引健康度，重点输出：
        ① 从未被使用过的索引（idx_scan = 0），是「光占空间还拖慢写入」的浪费
        ② 重复 / 冗余索引：索引 A 是索引 B 的前缀
        ③ 大表上没有索引的列：依据 pg_stats.n_distinct 推断「高基数列」候选
        ④ 索引膨胀粗略估算（精确膨胀需要 pgstattuple 扩展）

预期输出
    一张表格：
        schema | table         | index            | size   | scans | 建议
        public | ch17_perf_idx_demo | ch17_idx_demo_user    | 22 MB  |     0 | 未使用，建议 DROP
        public | ch17_perf_idx_demo | idx_demo_user_st | 31 MB  |     0 | 与 ch17_idx_demo_user_status_amt 前缀重复，建议合并
"""

from __future__ import annotations

import sys

try:
    import psycopg
except ImportError:
    sys.exit('请先安装 psycopg v3：pip install "psycopg[binary]>=3.1"')


CONN_INFO = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"


SQL_UNUSED = """
SELECT
    s.schemaname,
    s.relname  AS table_name,
    s.indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
    s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique         -- 唯一索引一般不能删（保业务约束）
  AND NOT i.indisprimary        -- 主键不能删
ORDER BY pg_relation_size(s.indexrelid) DESC
"""


SQL_DUPLICATE_PREFIX = """
WITH idx AS (
    SELECT
        n.nspname  AS schemaname,
        c.relname  AS table_name,
        ic.relname AS index_name,
        i.indexrelid,
        i.indrelid,
        ARRAY(
            SELECT pg_get_indexdef(i.indexrelid, k + 1, true)
            FROM generate_subscripts(i.indkey, 1) k
            ORDER BY k
        ) AS cols,
        pg_relation_size(i.indexrelid) AS bytes
    FROM pg_index i
    JOIN pg_class c   ON c.oid = i.indrelid
    JOIN pg_class ic  ON ic.oid = i.indexrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname NOT IN ('pg_catalog','information_schema')
      AND NOT i.indisprimary
      AND NOT i.indisunique
)
SELECT
    a.schemaname, a.table_name,
    a.index_name AS shorter_idx,
    b.index_name AS longer_idx,
    pg_size_pretty(a.bytes) AS shorter_size,
    pg_size_pretty(b.bytes) AS longer_size
FROM idx a
JOIN idx b
  ON a.indrelid = b.indrelid
 AND a.indexrelid <> b.indexrelid
 AND array_length(a.cols, 1) < array_length(b.cols, 1)
 AND a.cols = b.cols[1:array_length(a.cols,1)]
ORDER BY a.bytes DESC
"""


SQL_TABLE_NO_INDEX_CANDIDATE = """
SELECT
    s.schemaname,
    s.tablename,
    s.attname AS column_name,
    s.n_distinct,
    pg_size_pretty(pg_relation_size((s.schemaname||'.'||s.tablename)::regclass)) AS table_size
FROM pg_stats s
JOIN pg_class c ON c.relname = s.tablename
WHERE s.schemaname NOT IN ('pg_catalog','information_schema')
  AND pg_relation_size((s.schemaname||'.'||s.tablename)::regclass) > 50 * 1024 * 1024  -- > 50MB 才看
  AND s.n_distinct > 1000                                                             -- 高基数列
  AND NOT EXISTS (
      SELECT 1
      FROM pg_index i
      JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
      WHERE i.indrelid = (s.schemaname||'.'||s.tablename)::regclass
        AND a.attname  = s.attname
  )
ORDER BY pg_relation_size((s.schemaname||'.'||s.tablename)::regclass) DESC
"""


def banner(t: str) -> None:
    print()
    print("=" * 80)
    print(f" {t}")
    print("=" * 80)


def print_table(headers: list[str], rows: list[tuple]) -> None:
    if not rows:
        print("  (无)")
        return
    widths = [max(len(str(h)), max((len(str(r[i])) for r in rows), default=0))
              for i, h in enumerate(headers)]
    print("  " + "  ".join(f"{h:<{widths[i]}}" for i, h in enumerate(headers)))
    print("  " + "  ".join("-" * w for w in widths))
    for r in rows:
        print("  " + "  ".join(f"{str(r[i]):<{widths[i]}}" for i in range(len(headers))))


def main() -> None:
    try:
        conn = psycopg.connect(CONN_INFO, autocommit=True)
    except psycopg.OperationalError as exc:
        sys.exit(f"连接失败：{exc}")

    with conn, conn.cursor() as cur:
        banner("① 从未被使用过的索引（idx_scan = 0）")
        cur.execute(SQL_UNUSED)
        print_table(["schema", "table", "index", "size", "scans"], cur.fetchall())

        banner("② 前缀重复 / 冗余索引")
        cur.execute(SQL_DUPLICATE_PREFIX)
        print_table(
            ["schema", "table", "shorter_idx", "longer_idx", "shorter_size", "longer_size"],
            cur.fetchall(),
        )

        banner("③ 大表上「高基数 + 无索引」的列（建索引候选）")
        cur.execute(SQL_TABLE_NO_INDEX_CANDIDATE)
        print_table(
            ["schema", "table", "column", "n_distinct", "table_size"],
            cur.fetchall(),
        )

        banner("④ 各表索引/数据 体积比（看膨胀）")
        cur.execute("""
            SELECT
                schemaname,
                relname,
                pg_size_pretty(pg_relation_size(relid))                              AS table_size,
                pg_size_pretty(pg_indexes_size(relid))                               AS idx_size,
                ROUND(pg_indexes_size(relid)::NUMERIC
                      / NULLIF(pg_relation_size(relid),0), 2)                       AS idx_to_table_ratio
            FROM pg_stat_user_tables
            WHERE pg_relation_size(relid) > 10*1024*1024
            ORDER BY pg_indexes_size(relid) DESC
            LIMIT 20
        """)
        print_table(["schema", "table", "table_size", "idx_size", "ratio"], cur.fetchall())

        print("\n💡 经验值：")
        print("   · idx_to_table_ratio > 1   → 索引比表还大，多半有冗余索引")
        print("   · idx_scan = 0 持续 N 周  → 该索引在生产里真的没人用，可以 DROP")
        print("   · 同 indrelid 下，A 的列是 B 的前缀 → A 几乎可以被 B 替代")


if __name__ == "__main__":
    main()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174

python

"""03_keyset_pagination.py —— 第 17 章配套代码 #3

用途
    对比「OFFSET 巨大值的传统分页」与「键集分页 keyset pagination」的真实性能。
    OFFSET 越大越慢，因为 PG 必须先扫过前 OFFSET 行再丢弃。
    keyset 永远只扫一个索引区间，时间复杂度与 page 大小成正比，与「翻到第几页」无关。

依赖
    需要先运行 init.sql 准备 ch17_sorted_events 表（约 50w 行 + 复合索引）。

预期输出（示意，机器不同数值不同）
    OFFSET 0      LIMIT 50  ->   1.2 ms
    OFFSET 100000 LIMIT 50  ->  98.3 ms
    OFFSET 400000 LIMIT 50  -> 392.7 ms
    keyset 第 1 页           ->   1.0 ms
    keyset 第 2000 页（条件下沉）->   1.1 ms
"""

from __future__ import annotations

import sys
import time

try:
    import psycopg
except ImportError:
    sys.exit('请先安装 psycopg v3：pip install "psycopg[binary]>=3.1"')


CONN_INFO = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
PAGE_SIZE = 50


def timeit(cur, sql: str, params: tuple = ()) -> tuple[float, list]:
    """同一条 SQL 跑 3 次取最小值，去掉冷启动影响。"""
    best = float("inf")
    rows: list = []
    for _ in range(3):
        t0 = time.perf_counter()
        cur.execute(sql, params)
        rows = cur.fetchall()
        cost = (time.perf_counter() - t0) * 1000.0
        best = min(best, cost)
    return best, rows


def main() -> None:
    try:
        conn = psycopg.connect(CONN_INFO, autocommit=True)
    except psycopg.OperationalError as exc:
        sys.exit(f"连接失败：{exc}")

    with conn, conn.cursor() as cur:
        cur.execute("SELECT count(*) FROM ch17_sorted_events")
        total = cur.fetchone()[0]
        print(f"ch17_sorted_events 总行数：{total:,}")
        print(f"分页大小：{PAGE_SIZE} 行")

        # ---------- 一、OFFSET 分页 ----------
        print("\n[A] 传统 OFFSET 分页")
        print(f"{'OFFSET':>10} {'rows':>6}   {'耗时(ms)':>10}")
        for off in (0, 1_000, 10_000, 100_000, 400_000):
            cost, rows = timeit(
                cur,
                """
                SELECT id, happened_at, payload
                FROM ch17_sorted_events
                ORDER BY happened_at DESC, id DESC
                OFFSET %s LIMIT %s
                """,
                (off, PAGE_SIZE),
            )
            print(f"{off:>10,} {len(rows):>6}   {cost:>10.2f}")

        # ---------- 二、键集分页 ----------
        print("\n[B] 键集分页（keyset pagination）")
        print("    每次记住「上一页最后一行的 (happened_at, id)」")
        print("    新一页：WHERE (happened_at, id) < (?, ?) ORDER BY ... LIMIT N")
        print(f"{'第几页':>6} {'rows':>6}   {'耗时(ms)':>10}")

        # 模拟翻 5 页（每页都很快），再「跳到第 8000 页」也依然快
        last_ts, last_id = None, None
        for page in range(1, 6):
            if last_ts is None:
                cost, rows = timeit(
                    cur,
                    """
                    SELECT id, happened_at, payload
                    FROM ch17_sorted_events
                    ORDER BY happened_at DESC, id DESC
                    LIMIT %s
                    """,
                    (PAGE_SIZE,),
                )
            else:
                cost, rows = timeit(
                    cur,
                    """
                    SELECT id, happened_at, payload
                    FROM ch17_sorted_events
                    WHERE (happened_at, id) < (%s, %s)
                    ORDER BY happened_at DESC, id DESC
                    LIMIT %s
                    """,
                    (last_ts, last_id, PAGE_SIZE),
                )
            print(f"{page:>6} {len(rows):>6}   {cost:>10.2f}")
            if rows:
                last_id, last_ts = rows[-1][0], rows[-1][1]

        # 跳到「第 8000 页」（下沉条件 = 第 8000 页第 1 行的边界）
        # 我们用 OFFSET 取出第 8000*PAGE_SIZE 行的 (happened_at, id) 作为边界，
        # 然后用 keyset 取下一页 → 这一步即使在 50w 行规模下也不到几 ms。
        boundary_off = 8000 * PAGE_SIZE
        if boundary_off < total:
            cur.execute(
                """
                SELECT happened_at, id FROM ch17_sorted_events
                ORDER BY happened_at DESC, id DESC
                OFFSET %s LIMIT 1
                """,
                (boundary_off - 1,),
            )
            row = cur.fetchone()
            if row:
                bts, bid = row
                cost, rows = timeit(
                    cur,
                    """
                    SELECT id, happened_at, payload
                    FROM ch17_sorted_events
                    WHERE (happened_at, id) < (%s, %s)
                    ORDER BY happened_at DESC, id DESC
                    LIMIT %s
                    """,
                    (bts, bid, PAGE_SIZE),
                )
                print(f"{8001:>6} {len(rows):>6}   {cost:>10.2f}  (深翻一样快！)")

        # ---------- 三、EXPLAIN 直观对比 ----------
        print("\n[C] EXPLAIN 对比（看『扫了多少行』）")
        for label, sql, params in (
            ("OFFSET 400000",
             "EXPLAIN ANALYZE SELECT * FROM ch17_sorted_events "
             "ORDER BY happened_at DESC, id DESC OFFSET 400000 LIMIT 50",
             ()),
            ("keyset",
             "EXPLAIN ANALYZE SELECT * FROM ch17_sorted_events "
             "WHERE (happened_at, id) < (%s,%s) "
             "ORDER BY happened_at DESC, id DESC LIMIT 50",
             (last_ts, last_id)),
        ):
            print(f"\n--- {label} ---")
            cur.execute(sql, params)
            for r in cur.fetchall():
                print(" ", r[0])

        print("\n💡 总结：")
        print("   · OFFSET N LIMIT K → PG 必须扫过前 N 行才丢，深翻越来越慢")
        print("   · keyset 用复合索引精确定位边界，O(log N + K)，与页码无关")
        print("   · 注意排序键必须是「严格全序」，否则要带主键当 tie-breaker")


if __name__ == "__main__":
    main()

python

"""04_copy_vs_insert.py —— 第 17 章配套代码 #4

用途
    对比三种写入方式的吞吐：
        ① 单行 INSERT（最慢，每行一次网络往返）
        ② 多行 INSERT（一次发 1000 行，省网络往返）
        ③ COPY  FROM STDIN（PG 的「批量装载协议」，写入速度最快）
    输入数据：模拟 50000 行订单。

预期结果（量级，机器越好差距越大）
    单行 INSERT       :   50000 rows  /  6.5 s  →   7,700 rows/s
    多行 INSERT(1000) :   50000 rows  /  0.55 s →  91,000 rows/s
    COPY  FROM STDIN  :   50000 rows  /  0.12 s → 416,000 rows/s

结论
    生产里凡是「批量导入 / 数据迁移 / ETL」都应该用 COPY；
    REST API 里收到的数组写入也尽量攒批用多行 INSERT。
"""

from __future__ import annotations

import io
import random
import sys
import time
from datetime import datetime, timedelta, timezone

try:
    import psycopg
except ImportError:
    sys.exit('请先安装 psycopg v3：pip install "psycopg[binary]>=3.1"')


CONN_INFO = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
ROWS = 50_000
BATCH = 1000


def gen_rows(n: int):
    base = datetime.now(tz=timezone.utc)
    for i in range(n):
        yield (
            random.randint(1, 50_000),
            random.randint(1, 1000),
            random.randint(0, 4),
            round(random.random() * 9999 + 1, 2),
            base - timedelta(seconds=i),
            f"perf_note_{i}",
        )


def setup(cur) -> None:
    cur.execute("DROP TABLE IF EXISTS ch17_perf_write_demo")
    cur.execute(
        """
        CREATE UNLOGGED TABLE ch17_perf_write_demo (
            id           BIGSERIAL PRIMARY KEY,
            user_id      BIGINT,
            product_id   BIGINT,
            status       SMALLINT,
            amount       NUMERIC(12,2),
            created_at   TIMESTAMPTZ,
            note         TEXT
        )
        """
    )


def truncate(cur) -> None:
    cur.execute("TRUNCATE ch17_perf_write_demo RESTART IDENTITY")


def bench_single_insert(cur, rows) -> float:
    truncate(cur)
    t0 = time.perf_counter()
    sql = ("INSERT INTO ch17_perf_write_demo "
           "(user_id,product_id,status,amount,created_at,note) "
           "VALUES (%s,%s,%s,%s,%s,%s)")
    for r in rows:
        cur.execute(sql, r)
    return time.perf_counter() - t0


def bench_multi_insert(cur, rows, batch: int = BATCH) -> float:
    truncate(cur)
    t0 = time.perf_counter()
    buf: list = []
    for r in rows:
        buf.append(r)
        if len(buf) >= batch:
            cur.executemany(
                "INSERT INTO ch17_perf_write_demo "
                "(user_id,product_id,status,amount,created_at,note) "
                "VALUES (%s,%s,%s,%s,%s,%s)",
                buf,
            )
            buf.clear()
    if buf:
        cur.executemany(
            "INSERT INTO ch17_perf_write_demo "
            "(user_id,product_id,status,amount,created_at,note) "
            "VALUES (%s,%s,%s,%s,%s,%s)",
            buf,
        )
    return time.perf_counter() - t0


def bench_copy(cur, rows) -> float:
    truncate(cur)
    t0 = time.perf_counter()
    with cur.copy(
        "COPY ch17_perf_write_demo "
        "(user_id,product_id,status,amount,created_at,note) FROM STDIN"
    ) as cp:
        for r in rows:
            cp.write_row(r)
    return time.perf_counter() - t0


def fmt(n_rows: int, sec: float) -> str:
    rps = n_rows / sec if sec > 0 else float("inf")
    return f"{n_rows:>7,} rows  /  {sec:6.2f} s  →  {rps:>10,.0f} rows/s"


def main() -> None:
    try:
        conn = psycopg.connect(CONN_INFO, autocommit=True)
    except psycopg.OperationalError as exc:
        sys.exit(f"连接失败：{exc}")

    with conn, conn.cursor() as cur:
        setup(cur)

        print(f"基准：{ROWS:,} 行写入对比（表为 UNLOGGED 以排除 WAL 噪声）\n")

        rows1 = list(gen_rows(ROWS))
        cost = bench_single_insert(cur, rows1)
        print("① 单行 INSERT          :", fmt(ROWS, cost))

        rows2 = list(gen_rows(ROWS))
        cost = bench_multi_insert(cur, rows2, BATCH)
        print(f"② 批量 INSERT batch={BATCH} :", fmt(ROWS, cost))

        rows3 = list(gen_rows(ROWS))
        cost = bench_copy(cur, rows3)
        print("③ COPY FROM STDIN      :", fmt(ROWS, cost))

        print("\n💡 结论：")
        print("   · 单行 INSERT 慢 ≠ PG 慢，是「客户端往返 + 解析 + 计划」开销")
        print("   · 批量 INSERT 一次发 N 行，能把 RTT 摊薄成 1/N")
        print("   · COPY 走的是专门的二进制协议，跳过 SQL 解析阶段，最快")
        print("   · 如果要保 ACID 持久性，去掉 UNLOGGED 改成普通表，COPY 仍然显著快")


if __name__ == "__main__":
    main()

python

"""05_explain_diff.py —— 第 17 章配套代码 #5

用途
    自动比较「加索引前 vs 加索引后」同一条 SQL 的 EXPLAIN (ANALYZE, BUFFERS) 结果，
    凸显「Seq Scan → Index Scan」的飞跃。

测试场景
    SQL: SELECT * FROM ch17_perf_orders WHERE user_id = ? AND status = 1 ORDER BY created_at DESC LIMIT 20
    1) 不带索引：必然 Seq Scan + 在 WHERE 处过滤
    2) 加复合索引 (user_id, status, created_at DESC) 后：Index Scan + Limit 提前停

预期输出
    --- BEFORE ---
    Limit  ...
      ->  Sort  ...
            ->  Seq Scan on ch17_perf_orders  (rows=...) (cost=...)
                  Filter: ((user_id = N) AND (status = 1))
                  Rows Removed by Filter: 999800
                  Buffers: shared hit=12345 read=6789
    Planning Time: ...
    Execution Time: 380.2 ms

    --- AFTER  ---
    Limit  (...)
      ->  Index Scan using idx_perf_orders_us_st_ct on ch17_perf_orders
            Index Cond: ((user_id = N) AND (status = 1))
            Buffers: shared hit=23
    Planning Time: ...
    Execution Time: 0.6 ms
"""

from __future__ import annotations

import random
import sys
import textwrap

try:
    import psycopg
except ImportError:
    sys.exit('请先安装 psycopg v3：pip install "psycopg[binary]>=3.1"')


CONN_INFO = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
INDEX_NAME = "idx_perf_orders_us_st_ct"
TARGET_SQL = """
SELECT id, user_id, product_id, status, amount, created_at
FROM ch17_perf_orders
WHERE user_id = %s AND status = 1
ORDER BY created_at DESC
LIMIT 20
"""


def explain(cur, sql: str, params: tuple) -> str:
    cur.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) " + sql, params)
    return "\n".join(r[0] for r in cur.fetchall())


def line(title: str) -> str:
    return "-" * 30 + f"  {title}  " + "-" * 30


def main() -> None:
    try:
        conn = psycopg.connect(CONN_INFO, autocommit=True)
    except psycopg.OperationalError as exc:
        sys.exit(f"连接失败：{exc}")

    with conn, conn.cursor() as cur:
        # 让结果稳定：选一个真的存在的 user_id
        cur.execute("SELECT user_id FROM ch17_perf_orders LIMIT 1")
        uid = cur.fetchone()[0]
        print(f"测试 SQL：{textwrap.dedent(TARGET_SQL).strip()}")
        print(f"参数 user_id = {uid}\n")

        # 0. 确保索引被删干净
        cur.execute(f"DROP INDEX IF EXISTS {INDEX_NAME}")
        cur.execute("ANALYZE ch17_perf_orders")

        # 1. BEFORE
        print(line("BEFORE: 没有索引"))
        plan_before = explain(cur, TARGET_SQL, (uid,))
        print(plan_before)

        # 2. CREATE INDEX
        print()
        print(line(f"CREATE INDEX {INDEX_NAME}"))
        cur.execute(
            f"CREATE INDEX {INDEX_NAME} "
            "ON ch17_perf_orders (user_id, status, created_at DESC)"
        )
        cur.execute("ANALYZE ch17_perf_orders")
        print(f"已创建复合索引 (user_id, status, created_at DESC)")

        # 3. AFTER
        print()
        print(line("AFTER: 有索引"))
        plan_after = explain(cur, TARGET_SQL, (uid,))
        print(plan_after)

        # 4. 提取 Execution Time 数值做对比
        def exec_time(plan: str) -> float | None:
            for ln in plan.splitlines():
                if "Execution Time" in ln:
                    try:
                        return float(ln.split(":")[-1].replace("ms", "").strip())
                    except ValueError:
                        return None
            return None

        before, after = exec_time(plan_before), exec_time(plan_after)
        if before and after:
            print()
            print(line("对比结论"))
            speedup = before / after
            print(f"  Execution Time:  {before:.2f} ms  →  {after:.2f} ms")
            print(f"  提速：约 {speedup:,.0f} 倍")

        # 5. 清理（可选，注释掉则保留索引方便后续 demo）
        # cur.execute(f"DROP INDEX IF EXISTS {INDEX_NAME}")


if __name__ == "__main__":
    main()

markdown

# 第 17 章 配套代码

本章演示 **pg_stat_statements Top SQL**、**冗余索引审计**、**键集分页**、**COPY vs INSERT 写入对比**、**EXPLAIN 计划差异**等性能调优实战。所有自建表都加了 `ch17_` 前缀（`ch17_perf_orders`、`ch17_perf_idx_demo`、`ch17_sorted_events`、`ch17_perf_write_demo`）以避免和其他章节冲突。

## 准备工作

1. 跑一次 init.sql 初始化百万行测试数据：

   ```bash
   psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql

会建：

ch17_perf_orders（约 100 万行电商订单表）
ch17_perf_idx_demo（含冗余/未使用索引的对照表）
ch17_sorted_events（约 50 万行时序事件，已建复合索引）

确认 pg_stat_statements 已启用（init.sql 会 CREATE EXTENSION，但生产环境最好把它写进 postgresql.conf 的 shared_preload_libraries）。
安装 Python 客户端：
bash
```
pip install "psycopg[binary]>=3.1"
```
1

（可选）通过环境变量覆盖默认连接：

bash

export PG_DSN="host=127.0.0.1 port=5432 dbname=learn_pg user=postgres password=postgres"

脚本一览（推荐运行顺序）

脚本	一句话说明	关键 PG 特性
`01_pg_stat_statements_topn.py`	跑一组慢 SQL 后从 `pg_stat_statements` 拉 Top-N	`pg_stat_statements`、`pg_stat_statements_reset()`
`02_index_audit.py`	列出 `ch17_perf_idx_demo` 的重复索引、未使用索引	`pg_stat_user_indexes`、前缀索引判定
`03_keyset_pagination.py`	对比 OFFSET 翻页 vs 键集分页（基于 `ch17_sorted_events`）	复合索引下的 seek-pagination、`Index Scan Backward`
`04_copy_vs_insert.py`	单条 INSERT / 批量 INSERT / COPY FROM STDIN 三种写入方式压测	`psycopg.copy()`、`UNLOGGED TABLE`
`05_explain_diff.py`	同一 SQL 在加索引前后的 EXPLAIN ANALYZE 对比	执行计划读懂、`Seq Scan` → `Index Scan` 的成本变化

预期输出（节选 04）

① 单条 INSERT          :  50000 rows / 4.32 s →   11,574 rows/s
② 批量 INSERT (5000批)  :  50000 rows / 0.55 s →   90,909 rows/s
③ COPY FROM STDIN      :  50000 rows / 0.12 s →  416,666 rows/s

常见报错与依赖

connection refused → PG 未启动。
relation "ch17_perf_orders" does not exist → 未运行 ../init.sql。
pg_stat_statements must be loaded via shared_preload_libraries → 编辑 postgresql.conf 加上 shared_preload_libraries='pg_stat_statements'，重启 PG。
ERROR: extension "pg_buffercache" is not available → 装 contrib 包：apt install postgresql-contrib-XX。
百万行 INSERT 太慢 → 把 init.sql 里的 1000000 改小再跑（比如 100000）。
04 脚本要看到明显速差，建议在本地物理盘上跑，云盘 IO 抖动会污染结果。

01_pg_stat_statements_topn.py ↗ · 02_index_audit.py ↗ · 03_keyset_pagination.py ↗ · 04_copy_vs_insert.py ↗ · 05_explain_diff.py ↗ · README.md ↗

第 17 章 性能调优：从一行 SQL 到一台机器的完整调优地图 ​

0. 导读：性能调优的「第一性原理」 ​

1. 调优方法论：先观测，再下手 ​

1.1 「测量优先」的生活类比 ​

1.2 必备的三个观测工具 ​

1.3 一个调优会话的标准流程 ​

2. 关键服务端参数详解 ​

2.1 内存类参数（最重要） ​

shared_buffers — PG 自己的缓存池 ​

effective_cache_size — 优化器对「总缓存」的认知 ​

work_mem — 每个 sort/hash 操作的内存上限 ​

maintenance_work_mem — 维护操作专用内存 ​

wal_buffers — WAL 写出前的缓冲区 ​

2.2 IO / 优化器代价类 ​

random_page_cost / seq_page_cost ​

effective_io_concurrency ​

2.3 连接 / 并发类 ​

max_connections ​

并行查询相关 ​

2.4 WAL / Checkpoint 类 ​

2.5 统计信息 ​

2.6 Autovacuum ​

2.7 一张「按内存档位」的速查表 ​

3. 慢查询排查链路（重点） ​

3.1 第一站：开慢日志 ​

3.2 第二站：pg_stat_statements（必装） ​

安装 ​

经典查询：找 Top 10 总耗时 ​

3.3 第三站：auto_explain ​

3.4 第四站：pgbadger（日志分析） ​

4. SQL 优化技巧实战 ​

4.1 EXPLAIN 速读三件套 ​

4.2 加索引：从 380ms 到 0.6ms ​

4.3 删除多余索引 ​

4.4 大表分页：从 OFFSET 到 keyset pagination ​

4.5 COUNT(*) 慢怎么办？ ​

4.6 批量写入用 COPY ​

4.7 JSONB 查询要用 GIN 索引 ​

4.8 子查询改 JOIN ​

4.9 避免 SELECT * ​

5. 性能反模式 / 踩坑集 ​

6. 连接管理：为什么必须有连接池 ​

6.1 PG 进程模型的代价 ​

6.2 PgBouncer（推荐） ​

6.3 Pgpool-II ​

6.4 应用层池 ​

7. 监控指标体系 ​

7.1 关键 pg_stat_* 视图 ​

7.2 几个常备 SQL ​

7.3 接入 Prometheus + Grafana ​

8. 常见问题排查决策树 ​

9. 与 MySQL 的对比 ​

10. 小结 ​

🎮 配套演示 ​

11. 面试高频题 ​

Q1：shared_buffers 设多大合适？为什么不是越大越好？ ​

Q2：为什么 PG 必须用连接池？PgBouncer 的 transaction pooling 模式有什么坑？ ​

Q3：work_mem 怎么设？为什么不能简单粗暴地调到 1GB？ ​

Q4：怎么用 PG 自带工具排查一条慢 SQL？请描述完整链路 ​

Q5：业务说「OFFSET 10000 LIMIT 50 越来越慢」，怎么优化？ ​

Q6：autovacuum 是干嘛的？关掉自己手动 VACUUM 行不行？ ​

Q7：random_page_cost 默认 4.0 在 SSD 上为什么必须改？ ​

Q8：怎么给一台 16GB / 8 核 / SSD 的新机器写 postgresql.conf？ ​

🔗 延伸阅读 ​

🎬 可视化演示 ​

💻 示例代码 ​

脚本一览（推荐运行顺序） ​

预期输出（节选 04） ​

常见报错与依赖 ​

第 17 章性能调优：从一行 SQL 到一台机器的完整调优地图

0. 导读：性能调优的「第一性原理」

1. 调优方法论：先观测，再下手

1.1 「测量优先」的生活类比

1.2 必备的三个观测工具

1.3 一个调优会话的标准流程

2. 关键服务端参数详解

2.1 内存类参数（最重要）

`shared_buffers` — PG 自己的缓存池

`effective_cache_size` — 优化器对「总缓存」的认知

`work_mem` — 每个 sort/hash 操作的内存上限

`maintenance_work_mem` — 维护操作专用内存

`wal_buffers` — WAL 写出前的缓冲区

2.2 IO / 优化器代价类

`random_page_cost` / `seq_page_cost`

`effective_io_concurrency`

2.3 连接 / 并发类

`max_connections`

并行查询相关

2.4 WAL / Checkpoint 类

2.5 统计信息

2.6 Autovacuum

2.7 一张「按内存档位」的速查表

3. 慢查询排查链路（重点）

3.1 第一站：开慢日志

3.2 第二站：`pg_stat_statements`（必装）

安装

经典查询：找 Top 10 总耗时

3.3 第三站：`auto_explain`

3.4 第四站：pgbadger（日志分析）

4. SQL 优化技巧实战

4.1 EXPLAIN 速读三件套

4.2 加索引：从 380ms 到 0.6ms

4.3 删除多余索引

4.4 大表分页：从 OFFSET 到 keyset pagination

4.5 COUNT(*) 慢怎么办？

4.6 批量写入用 COPY

4.7 JSONB 查询要用 GIN 索引

4.8 子查询改 JOIN

4.9 避免 SELECT *

5. 性能反模式 / 踩坑集

6. 连接管理：为什么必须有连接池

6.1 PG 进程模型的代价

6.2 PgBouncer（推荐）

6.3 Pgpool-II

6.4 应用层池

7. 监控指标体系

7.1 关键 `pg_stat_*` 视图

7.2 几个常备 SQL

7.3 接入 Prometheus + Grafana

8. 常见问题排查决策树

9. 与 MySQL 的对比

10. 小结

🎮 配套演示

11. 面试高频题

Q1：`shared_buffers` 设多大合适？为什么不是越大越好？

Q2：为什么 PG 必须用连接池？PgBouncer 的 transaction pooling 模式有什么坑？

Q3：`work_mem` 怎么设？为什么不能简单粗暴地调到 1GB？

Q4：怎么用 PG 自带工具排查一条慢 SQL？请描述完整链路

Q5：业务说「OFFSET 10000 LIMIT 50 越来越慢」，怎么优化？

Q6：`autovacuum` 是干嘛的？关掉自己手动 VACUUM 行不行？

Q7：`random_page_cost` 默认 4.0 在 SSD 上为什么必须改？

Q8：怎么给一台 16GB / 8 核 / SSD 的新机器写 `postgresql.conf`？

🔗 延伸阅读

🎬 可视化演示

💻 示例代码

脚本一览（推荐运行顺序）

预期输出（节选 04）

常见报错与依赖