SELECT relname, relpages, reltuples, relallvisible
FROM pg_class
WHERE relname = 'orders';
```text

| 字段 | 含义 | 优化器用途 |
|------|------|-----------|
| `relpages` | 表的页面数（由 ANALYZE 估算） | Seq Scan 的 I/O 代价：`relpages × seq_page_cost` |
| `reltuples` | 表的行数（由 ANALYZE 估算） | 所有选择率的基数（所有 `rows` 估计都来自这个值） |
| `relallvisible` | 全可见页面数（VM 中的标记） | Index-Only Scan 代价计算：可见页面可跳过 heap fetch |

这三个字段是优化器估算的起点。`relpages` 和 `reltuples` 由 `ANALYZE` 通过采样估算，不是精确值——对大表来说，误差可能显著。

### pg_statistic：每一列的数据画像

`pg_statistic` 存储每一列的数据分布统计，是优化器做选择率估算的核心输入。它的表结构设计得比较特殊——用 "slot" 机制存储不同类型的统计值，避免了一个列只有一个统计表示的限制：

```sql
-- 查看某表某列的统计信息（通过 pg_stats 视图，它是 pg_statistic 的可读封装）
SELECT attname, n_distinct, null_frac, avg_width,
       most_common_vals, most_common_freqs,
       histogram_bounds, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

#define STATISTIC_KIND_MCV          1   // Most Common Values + 频率
#define STATISTIC_KIND_HISTOGRAM    2   // 等深直方图（equi-depth histogram）
#define STATISTIC_KIND_CORRELATION  3   // 物理行序与逻辑值序的相关系数
#define STATISTIC_KIND_MCELEM       4   // 扩展统计的 MCV（多列）
#define STATISTIC_KIND_DECHIST      5   // 扩展统计的直方图（数组类型）
```text

三个核心统计类型：

**MCV（Most Common Values）**：存储最高频的值及其出现频率。如果查询条件是 `WHERE status = 'active'`，优化器先在 MCV 列表中查找 `'active'`，命中则直接取对应频率；未命中则用直方图估算。

**Histogram（等深直方图）**：将数据按值排序后分成 N 个桶，每个桶包含约等量的行（不是等宽）。存储的是每个桶的上界值。PG 的直方图是等深（equi-depth）不是等宽（equi-width）——这意味着在数据倾斜时，高频值的桶边界更窄，从而保持较好的分辨率。

**Correlation**：物理行序与列值逻辑序的皮尔逊相关系数，范围 $[-1, 1]$。如果值为 $1$ 或 $-1$，说明列值与物理存储顺序高度相关——这意味着 Index Scan 的随机 I/O 代价比随机假设要小，优化器会更倾向选索引扫描。此值由 `analyze.c` 中的 `compute_correlation()` 计算。

`stadistinct` 的负数表示法：当值为 -0.3 时，表示去重值数量约为 `reltuples × 0.3`。这是对"列中不同值数量随行数线性增长"这种情况的优化——当表增长时，不需要重新 ANALYZE 也能大致推算新的去重数。

---

## 二、ANALYZE 的采样流程

ANALYZE 的入口在 `src/backend/commands/analyze.c` 的 `do_analyze_rel()`。不是读全表，而是采样。

### 采样算法

PG 使用的是两阶段采样：

1. **页面采样**：随机选取 `targrows / rows_per_page` 个页面。`targrows` 默认是 `300 × default_statistics_target`（即 30000 行）。如果表有 100 个页面、期望采样 3000 行，则可能会选中约 30 个页面。

2. **行采样（Reservoir Sampling）**：在每个选中页面中，用 Vitter's Algorithm Z 做水库采样（reservoir sampling），保证每一行被选中的概率相等。

```c
// src/backend/commands/analyze.c, acquire_sample_rows() 简化流程
static int
acquire_sample_rows(Relation onerel, int elevel,
                    HeapTuple *rows, int targrows,
                    double *totalrows, double *totaldeadrows)
{
    // 1. 获取表的总页面数
    totalblocks = RelationGetNumberOfBlocks(onerel);

    // 2. 计算采样页面数
    //    targrows 默认 = 300 * default_statistics_target
    samplerows = targrows;

    // 3. 循环：随机选取一个页面 → 读取所有行 → Reservoir Sampling 选入 rows[]
    for (;;) {
        targblock = random() % totalblocks;   // 随机页面

        heapam_scan_analyze_next_block(...);   // 读取页面

        // 对每一行，Vitter's Algorithm Z
        if (rowstoskip < 0)
            reservoir_sample_add(row, rows, samplerows);
    }

    // 4. 估算总行数
    *totalrows = liverows * totalblocks / scanned_pages;
    *totaldeadrows = deadrows * totalblocks / scanned_pages;
}

// selfuncs.c, eqsel() 的简化逻辑
Datum eqsel(PG_FUNCTION_ARGS) {
    // 1. 查 MCV 列表：如果查询常量在 MCV 中，直接取对应频率
    if (get_attstatsslot(&sslot, statsTuple,
                         STATISTIC_KIND_MCV, ...)) {
        // mcv_match: 查找常量在 MCV 中的位置
        selec = sslot.numbers[mcv_match];  // 该值的频率
    }
    // 2. 不在 MCV 中：使用直方图估算
    else if (get_attstatsslot(&sslot, statsTuple,
                              STATISTIC_KIND_HISTOGRAM, ...)) {
        selec = ineq_histogram_selectivity(...);
    }
    // 3. 只有 MCV 没有直方图：对非 MCV 值使用均匀假设
    else {
        selec = (1.0 - sum_of_mcv_freqs) /
                (stadistinct - mcv_count);
    }

    // 4. 最后：乘以 (1 - stanullfrac) 排除 NULL
    selec *= (1.0 - stanullfrac);
}
```text

`scalarineqsel()`（范围条件：`<`, `>`, `<=`, `>=`）的处理更复杂：

1. 先查 MCV，累加满足条件的 MCV 值的频率
2. 剩余未覆盖的行数部分，通过直方图插值估算
3. 直方图假设桶内均匀分布——在桶内做线性插值

```c
// selfuncs.c, ineq_histogram_selectivity() 的简化逻辑
// hist: [10, 20, 30, 40, 50] → 4 个桶
// 查询: WHERE x < 25
// → 桶 1 [10, 20] 完全在范围内 → +1/4
// → 桶 2 [20, 30] 部分在范围内 → (25-20)/(30-20) * 1/4 = 0.5 * 1/4
// → 总选择率 = 0.25 + 0.125 = 0.375

// costsize.c，关键代价常量（PG 17）
double seq_page_cost        = DEFAULT_SEQ_PAGE_COST;         // 1.0
double random_page_cost     = DEFAULT_RANDOM_PAGE_COST;      // 4.0
double cpu_tuple_cost       = DEFAULT_CPU_TUPLE_COST;        // 0.01
double cpu_index_tuple_cost = DEFAULT_CPU_INDEX_TUPLE_COST;  // 0.005
double cpu_operator_cost    = DEFAULT_CPU_OPERATOR_COST;     // 0.0025
double parallel_setup_cost  = DEFAULT_PARALLEL_SETUP_COST;   // 1000.0
double parallel_tuple_cost  = DEFAULT_PARALLEL_TUPLE_COST;   // 0.1
```text

| 常量 | 物理意义 |
|------|---------|
| `seq_page_cost` | 在顺序 scan 中读一个页面的代价。设为 1.0 作为基准。 |
| `random_page_cost` | 在随机位置读一个页面的代价。HDD 上寻道 + 旋转延迟约是顺序读的 4 倍；NVMe SSD 上这个比率接近 1.0。 |
| `cpu_tuple_cost` | 处理一行（检查可见性、解包 HeapTuple）的 CPU 代价 |
| `cpu_index_tuple_cost` | 处理一条索引项的 CPU 代价（比 tuple 小，因为只在 index page 中操作） |
| `cpu_operator_cost` | 一次 `=`、`<`、`>` 等运算符的 CPU 代价 |
| `effective_cache_size` | 优化器对总缓存（shared_buffers + OS page cache）的估算，用于判断 Index Scan 时有多少索引页面可能已在缓存中 |

### Seq Scan 的代价公式

`cost_seqscan()` 中对一个表做顺序扫描的代价：

$$C_{\text{seq}} = N_{\text{pages}} \times \text{seq\_page\_cost} + N_{\text{tuples}} \times \text{cpu\_tuple\_cost}$$

还有启动成本（startup cost），如 `cost_seqscan()` 中设置的 `startup_cost = 0`（Seq Scan 可以马上返回第一行）。

### Index Scan 的代价公式

`cost_index()` 更复杂，分三部分：

**索引页面访问**：假设 B-Tree 中满足条件的 `N_{\text{index\_tuples}}` 个索引项分布在 `N_{\text{index\_pages}}` 个索引页面中。使用 Mackert-Lohman 公式估算需要访问多少个不同的索引页面。

**表页面访问**：每个满足条件的索引项指向一个 heap tuple。关键假设是这些 heap 页面的访问顺序近似随机（除非 correlation 接近 1）。`cost_index()` 使用以下公式估算随机访问的页面数：

$$N_{\text{heap\_pages}} = \min(N_{\text{total\_heap\_pages}}, \text{indexSelectivity} \times N_{\text{total\_heap\_pages}})$$

其中 indexSelectivity 是索引扫描的选择率。对于小选择率（如 0.001），`cost_index()` 使用更复杂的近似模型——当选择率很小时，即使随机访问，一个页面也可能被多次命中（同一页面可能包含多个匹配的 tuple），所以实际需要读取的页面数小于 `selectivity × total_pages`。这就是 Mackert-Lohman 公式的作用。

**CPU 代价**：

$$C_{\text{index}} = C_{\text{startup}} + C_{\text{index\_pages}} \times \text{random\_page\_cost} + C_{\text{heap\_pages}} \times \text{random\_page\_cost} + N_{\text{index\_tuples}} \times \text{cpu\_index\_tuple\_cost} + N_{\text{output\_tuples}} \times \text{cpu\_tuple\_cost} + N_{\text{qual\_ops}} \times \text{cpu\_operator\_cost}$$

### random_page_cost 在 SSD 上的问题

默认 `random_page_cost = 4.0` 来自 HDD 时代——随机 I/O 的寻道和旋转延迟使其约为顺序 I/O 的 4 倍。但 NVMe SSD 没有机械寻道，随机和顺序读的延迟比接近 1.0 ~ 1.5。

当 `random_page_cost` 保持 4.0 时，Index Scan 的代价被大幅高估。优化器会偏向 Seq Scan，即使索引扫描实际快得多。很多人发现 "EXPLAIN 显示 Seq Scan，但加 `SET enable_seqscan = off` 后 Index Scan 快 10 倍"——根因就是 `random_page_cost` 没适配存储。

**调整建议**：在 NVMe SSD 上设为 1.0 ~ 1.5；SATA SSD 上设为 1.5 ~ 2.5；HDD RAID 上保持 3.0 ~ 4.0。可以用 `pg_test_fsync` 测量实际随机/顺序 I/O 比率。

### effective_cache_size 的作用

`effective_cache_size` 不分配任何内存。它在 `cost_index()` 中影响一个关键判断：**一个随机访问的 heap 页面是否已在 OS cache 中**：

$$P_{\text{cached}} = \min\left(1, \frac{\text{effective\_cache\_size}}{N_{\text{index\_pages}} + N_{\text{heap\_pages}}}\right)$$

缓存的页面用 `seq_page_cost` 计代价，而非 `random_page_cost`。如果 `effective_cache_size` 设得太小（比如默认的 4GB，而实际 OS page cache 有 128GB），优化器会高估 Index Scan 的 I/O 代价，错误地选择 Seq Scan。

---

## 五、扩展统计（CREATE STATISTICS）：解决多列相关

`CREATE STATISTICS` 是 PG 10 引入的机制，用于解决"优化器假设列之间独立"的问题。

### 三种扩展统计类型

```sql
-- 创建多列统计对象
CREATE STATISTICS s1 (dependencies) ON a, b FROM t;
CREATE STATISTICS s2 (ndistinct)   ON a, b FROM t;
CREATE STATISTICS s3 (mcv)         ON a, b FROM t;

-- 查看某个查询中哪些列之间有相关性
-- EXPLAIN 中 "rows" 与 "actual rows" 差异大的列对，通常需要扩展统计

-- 查看已有扩展统计
SELECT stxname, stxkeys, stxddependencies, stxndistinct, stxdmcv
FROM pg_statistic_ext
JOIN pg_statistic_ext_data ON pg_statistic_ext.oid = pg_statistic_ext_data.stxoid
WHERE stxrelid = 'orders'::regclass;
```text

有用扩展统计的典型场景：
- 城市和邮政编码
- 产品类别和子类别
- 订单创建日期和发货日期（虽然不是函数依赖，但高度相关）
- `WHERE a = x AND b = y` 类型的多条件查询，且实际结果集远大于或远小于估算

创建扩展统计后需要执行 `ANALYZE` 才会生效。统计信息存储在 `pg_statistic_ext_data` 中，与 `pg_statistic_ext` 分离（PG 12 的 catalog 分离改进）。

---

## 六、统计信息不准：一个糟糕计划的解剖

下面构造一个生产中常见的场景：统计信息严重滞后导致优化器选错计划。

### 场景构造

```sql
-- 1. 建表，插入 100 万行均匀数据
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type TEXT,
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO events (event_type)
SELECT 'view' FROM generate_series(1, 990000);

-- 创建索引
CREATE INDEX idx_event_type ON events(event_type);
ANALYZE events;

-- 此时统计信息：'view' 占 ~99%, 选择率估值正确
-- EXPLAIN 会正确选择 Seq Scan（几乎所有行都要返回）
EXPLAIN SELECT * FROM events WHERE event_type = 'view';
-- 预期：Seq Scan on events  (rows=990000)

-- 2. 插入 10000 行 'purchase'（占总数据不到 1%）
INSERT INTO events (event_type)
SELECT 'purchase' FROM generate_series(1, 10000);

-- 但没有 ANALYZE！
-- pg_statistic 中只有 'view' 的统计
-- 优化器不知道有 'purchase' 这个值

-- 3. 查询 'purchase' 事件
EXPLAIN SELECT * FROM events WHERE event_type = 'purchase';
-- 优化器估算：没有 'purchase' 的 MCV → 用直方图或默认假设
-- 结果可能估算为 rows=1 或一个很小的值
-- → 选择了 Index Scan（以为只返回很少的行）
-- 实际返回：10000 行
-- 此时：10000 行的 Index Scan + heap fetch 可能比 Seq Scan 慢得多

SELECT
    schemaname || '.' || relname AS table_name,
    n_live_tup,
    n_mod_since_analyze,
    last_analyze,
    last_autoanalyze,
    ROUND(100.0 * n_mod_since_analyze / NULLIF(n_live_tup, 0), 2) AS pct_modified
FROM pg_stat_user_tables
WHERE n_mod_since_analyze > 0
ORDER BY n_mod_since_analyze DESC
LIMIT 20;
```text

| 信号 | 含义 | 建议 |
|------|------|------|
| `n_mod_since_analyze > n_live_tup × 0.1` | 超过 10% 数据已变更 | 手动 `ANALYZE` |
| `last_analyze IS NULL` 且表非空 | 该表从未被 ANALYZE | 立即 `ANALYZE` |
| `last_autoanalyze` 是很久以前 | autovacuum analyze 可能未触发 | 检查 autovacuum 配置 |

### 第二步：检查 autovacuum analyze 的触发条件

```sql
-- 检查表的 autovacuum 相关设置
SELECT
    schemaname || '.' || relname AS table_name,
    reloptions
FROM pg_class
WHERE relname = 'your_table' AND reloptions IS NOT NULL;

-- 全局阈值
SHOW autovacuum_analyze_scale_factor;    -- 默认 0.1
SHOW autovacuum_analyze_threshold;        -- 默认 50

SELECT
    schemaname || '.' || relname AS table_name,
    n_live_tup,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    last_autoanalyze,
    CASE
        WHEN n_tup_upd + n_tup_del = 0 AND last_autoanalyze IS NOT NULL
             AND last_autoanalyze < now() - INTERVAL '1 day'
        THEN 'insert-only table, may need more frequent ANALYZE'
    END AS warning
FROM pg_stat_user_tables
WHERE n_live_tup > 100000
  AND n_tup_ins > n_live_tup * 0.1;
```text

**解决方案**：对 insert-only 表降低 `autovacuum_analyze_scale_factor`，或者使用 cron 定期执行 `ANALYZE`：

```sql
ALTER TABLE events SET (autovacuum_analyze_scale_factor = 0.01);

SHOW default_statistics_target;
-- 查看具体列的 settings
SELECT attname, attstattarget
FROM pg_attribute
WHERE attrelid = 'your_table'::regclass AND attstattarget >= 0;
```text

`default_statistics_target` 直接影响：
- MCV 列表的长度
- 直方图的桶数

默认值 100 对大多数场景足够。但对具有以下特征的列，应该调高：
- 去重值数量巨大（如用户 ID、订单号）
- 分布高度不均匀（如城市列中 90% 是 "Beijing"，需要更多桶来区分剩余城市）
- 查询常用作过滤条件且 `EXPLAIN` 中 `rows` 估计偏差超过 10 倍

调高时注意：每个 `target` 值增加约 300 个采样行和相应的存储开销。对于 1000 列的表，将全局 `default_statistics_target` 设到 1000 会导致 ANALYZE 耗时和 `pg_statistic` 大小线性增长。

### 第五步：对比 EXPLAIN 估算与实际执行

这是最直接的诊断手段：

```sql
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT * FROM events WHERE event_type = 'purchase';

-- 确保 auto_explain 在 shared_preload_libraries 中
-- 检查 pg_settings 中的配置
SELECT name, setting, source
FROM pg_settings
WHERE name LIKE 'auto_explain%';
```text

`auto_explain` 必须通过 `shared_preload_libraries` 加载并重启才能生效。如果只通过 `SET` 或 `ALTER SYSTEM SET` 改了参数但没有加到 `shared_preload_libraries`，`auto_explain` 模块根本没有加载，日志中不会有任何 plan 输出。

核查：

```bash
# 检查 postgresql.conf
grep shared_preload_libraries /path/to/postgresql.conf

# 需要包含 auto_explain
# shared_preload_libraries = 'pg_stat_statements, auto_explain'

-- 全局检查
SHOW autovacuum;
-- 表级检查
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'your_table' AND reloptions::text LIKE '%autovacuum%';
```text

**根因 C：autovacuum worker 不足或全部繁忙**

```sql
-- 查看当前运行的 autovacuum worker
SELECT pid, query, state, backend_start
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker';

-- 最大 autovacuum worker 数
SHOW autovacuum_max_workers;  -- 默认 3

SHOW autovacuum_vacuum_cost_delay;  -- 默认 2ms
SHOW autovacuum_vacuum_cost_limit;  -- 默认 200
```text

对于 auto-analyze，限速影响较小（ANALYZE 只是采样读，不来全表）。但极端限速仍然可能导致 worker 在某个表上停留过久。

---

## 九、临时表与统计信息的特殊行为

临时表（`CREATE TEMP TABLE`）的统计信息行为与普通表有本质差异：

### 临时表不受 autovacuum 管理

Autovacuum 只处理持久表（catalog 中 `relpersistence = 'p'`）。临时表的 `relpersistence = 't'`，autovacuum launcher 在遍历表列表时会跳过它们。这意味着：

- 临时表不会自动 ANALYZE
- 临时表不会自动 VACUUM
- `pg_stat_user_tables` 中**不会出现**临时表（临时表只在自己的 session 的 `pg_stat_user_tables` 中可见，对其他 session 不可见，也不会被 autovacuum 考虑）

### 临时表的 ANALYZE 必须显式执行

```sql
CREATE TEMP TABLE tmp_data AS
SELECT * FROM generate_series(1, 1000000) AS id;

-- 此时 pg_class 中 reltuples = -1（未知）
-- 优化器使用默认假设（reltuples = 1000 或类似硬编码值）

-- 必须手动执行
ANALYZE tmp_data;

-- 此时 pg_class 和 pg_statistic 才有数据
-- 注意：pg_statistic 中的统计只在创建了临时表的那个 session 中可见

SELECT relname, reltuples, relpages
FROM pg_class
WHERE relpersistence = 't';
```text

如果 `reltuples = -1`，必须 `ANALYZE`。

---

## 十、统计信息诊断 SQL 工具箱

以下查询可以组合成一个诊断脚本：

```sql
-- 1. 哪些表的统计信息可能过期
SELECT
    schemaname || '.' || relname AS table_name,
    n_live_tup,
    n_dead_tup,
    n_mod_since_analyze,
    last_analyze,
    last_autoanalyze,
    CASE
        WHEN last_analyze IS NULL THEN 'NEVER_ANALYZED'
        WHEN n_mod_since_analyze > n_live_tup * 0.1 THEN 'STALE'
        WHEN last_analyze < now() - INTERVAL '7 days'
             AND n_live_tup > 100000 THEN 'OLD'
        ELSE 'OK'
    END AS stats_status
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC NULLS LAST;

-- 2. 哪些 EXPLAIN 估算与实际行数偏差大（需要 pg_stat_statements）
-- 如果安装了 pg_stat_statements 且开启了 track_planning:
SELECT
    queryid,
    LEFT(query, 80) AS query_preview,
    calls,
    mean_exec_time,
    ROUND(shared_blks_read * 8192.0 / 1048576, 2) AS mb_read
FROM pg_stat_statements
WHERE mean_exec_time > 100  -- ms
ORDER BY mean_exec_time DESC
LIMIT 20;

-- 3. 检查是否有列从未 ANALYZE
SELECT
    c.relname AS table_name,
    a.attname AS col_name,
    coalesce(s.stanullfrac, -1) AS null_frac
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid
LEFT JOIN pg_statistic s ON s.starelid = c.oid AND s.staattnum = a.attnum
WHERE c.relkind = 'r'
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND s.starelid IS NULL
  AND EXISTS (SELECT 1 FROM pg_stat_user_tables t WHERE t.relid = c.oid AND t.n_live_tup > 0);

-- 4. 哪些表有 autovacuum 设置覆盖
SELECT
    schemaname || '.' || relname AS table_name,
    reloptions
FROM pg_class
WHERE relkind = 'r' AND reloptions IS NOT NULL;

-- 5. 扩展统计对象列表
SELECT
    stxnamespace::regnamespace::text AS schema,
    stxrelid::regclass::text AS table_name,
    stxname,
    stxkeys,
    stxkind
FROM pg_statistic_ext
ORDER BY stxnamespace, stxrelid, stxname;