# 第 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 万行时序事件，已建复合索引）

2. 确认 `pg_stat_statements` 已启用（init.sql 会 `CREATE EXTENSION`，但生产环境最好把它写进 `postgresql.conf` 的 `shared_preload_libraries`）。

3. 安装 Python 客户端：

   ```bash
   pip install "psycopg[binary]>=3.1"
   ```

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

   ```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 抖动会污染结果。