database

PostgreSQL 统计信息与查询计划器：ANALYZE、pg_statistic 与代价模型

系统剖析 PostgreSQL 查询计划器的统计信息基础与代价估算模型。涵盖直方图与高频值（MCV）的采集原理、ANALYZE 手动触发与 autovacuum_analyze 自动维护机制、pg_stats 与 pg_statistic 系统目录的结构差异、数据倾斜导致的 Cost 偏差识别、扩展统计（多元统计与函数依赖统计）的创建与使用，以及 pg_hint_plan 扩展对执行计划的干预手段。

Leeting Yan 6 分钟阅读 2979 字

PostgreSQL 的查询计划器不是"猜"出执行计划的，它依赖一套精确的统计信息来估算每个操作的成本——行数估算、选择性估算、连接顺序成本，全由统计数字驱动。当统计信息过时或缺失时，一个合理的索引查询可能退化为全表扫描；当数据严重倾斜时，计划器按均匀分布假设给出的估算会与实际偏差数个数量级。理解统计信息的采集、存储与代价模型，是调优执行计划的前提。

核心认知：ANALYZE 不是可选的运维操作，而是计划器做出正确决策的数据来源。如果你没有定时 ANALYZE 的习惯——或 autovacuum_analyze 被错误调低——每一个执行计划都建立在错误估算之上。

一、统计信息概览

1.1 统计信息的作用

计划器使用统计信息回答三个核心问题：

  1. 这张表有多少行？ —— pg_class.reltuples
  2. 这个 WHERE 条件过滤后还剩多少行？ —— 直方图、MCV、ndistinct
  3. 两个表连接后有多少行？ —— 相关性（correlation）与连接选择性

1.2 统计信息来源

ANALYZE 采样 → 写入 pg_statistic → 通过 pg_stats 视图暴露
                           ↑
                    用户可见，但需 superuser
                    权限才能读 pg_statistic
存储位置权限要求说明
pg_statisticsuperuser原始统计目录，含实际采样值
pg_stats表 SELECT 权限即可视图，隐藏敏感数据（按配置）

二、ANALYZE 与采样机制

2.1 ANALYZE 的工作原理

ANALYZE users;
-- 针对单表采样（默认约 300 × default_statistics_target 行）

ANALYZE;
-- 扫描全库所有符合条件的表

ANALYZE (VERBOSE) users;
-- 输出采样详细过程

ANALYZE 的执行过程：

  1. 按一定算法随机采样全表数据
  2. 对每一列计算：null_frac、avg_width、n_distinct、most_common_vals（MCV）、histogram_bounds（直方图边界）
  3. 将结果写入 pg_statistic 系统目录

2.2 default_statistics_target

SHOW default_statistics_target;  -- 默认 100

-- 全局提高采样精度（更多统计对象，更好的估算）
ALTER SYSTEM SET default_statistics_target = 200;
SELECT pg_reload_conf();
值采样行数（约）适用场景
103000 行快速、粗略
100（默认）30000 行通用场景
500150000 行高选择性条件、倾斜数据
1000300000 行极高精度要求（空间换精度）

2.3 autovacuum_analyze 自动触发

-- 触发条件：
-- 修改行数 > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × reltuples

SHOW autovacuum_analyze_threshold;   -- 默认 50
SHOW autovacuum_analyze_scale_factor; -- 默认 0.1

对于 100 万行的表，行数变化超过 50 + 0.1 × 1000000 = 100050 行才触发 ANALYZE。这对大型分区表可能过于懒惰：

-- 大表单独调低比例
ALTER TABLE events SET (autovacuum_analyze_scale_factor = 0.01);

2.4 手动控制分析时机

-- 批量导入后立刻更新统计
COPY events FROM '/data/events.csv' WITH (FORMAT csv);
ANALYZE events;

-- 对分区表分析所有分区
ANALYZE events_2026_01, events_2026_02, events_2026_03;

三、pg_stats 与 pg_statistic 解读

3.1 pg_stats 结构

SELECT schemaname, tablename, attname,
       n_distinct, most_common_vals, most_common_freqs,
       histogram_bounds, correlation
FROM pg_stats
WHERE tablename = 'users' AND attname = 'status';
字段含义
n_distinct列中不同值数量（负数表示比例，正数表示绝对数）
most_common_valsMCV 列表（最频繁的值）
most_common_freqsMCV 对应的频率
histogram_bounds直方图桶边界，描述值的分布范围
correlation物理顺序与逻辑顺序的相关系数（用于 Index Scan vs Sequential Scan 估算）

3.2 直方图与 MCV 的配合

-- 假设 status 列有 'active'、'inactive'、'pending'
-- MCV 保存了三个高频值及其频率，不在 MCV 中的值用直方图估算

SELECT attname,
       array_length(most_common_vals::text::text[], 1) AS mcv_count,
       most_common_vals::text AS mcv_values,
       most_common_freqs AS mcv_freqs
FROM pg_stats
WHERE tablename = 'users' AND attname = 'status';

估算 “status = ‘active’” 的选择性：

  1. 先查 'active' 是否在 most_common_vals 中 —— 是，返回对应频率
  2. 若不在 MCV 中，则用直方图估算：在直方图分布中定位所在区间，按均匀分布假设计算

3.3 不均匀分布导致的估算偏差

-- 统计信息假设：不在 MCV 中的值按均匀分布
-- 但数据实际上可能极度倾斜

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'on_hold';
-- 计划器估算：50 rows
-- 实际执行：50000 rows

出现这种偏差，说明 'on_hold' 不在 MCV 中，但值高度集中在某几个 bucket 中。提高 default_statistics_target 或针对该列单独增加统计目标：

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

四、数据倾斜与 Cost 偏差

4.1 估算值与实际值对比

-- 检查计划器估算准确性：
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE created_at >= '2026-09-01'
  AND created_at < '2026-10-01'
  AND status = 'completed';

输出中的 rows= 是计划器估算的行数，actual rows= 是实际行数。两者相差 10 倍以上即说明统计信息失真。

4.2 识别高偏差列

-- 查看某张表所有列的 n_distinct，与真实值对比
SELECT attname, n_distinct
FROM pg_stats
WHERE tablename = 'orders';

-- 与真实值对比
SELECT count(DISTINCT user_id) AS real_ndistinct FROM orders;

4.3 直方图桶数估算

直方图桶数默认等于 default_statistics_target。当列值分布跨越多个数量级（如用户年龄 1100 岁 vs 订单金额 1100000 元），均匀直方图可能不够精确：

ALTER TABLE orders ALTER COLUMN total_amount SET STATISTICS 500;
ANALYZE orders;

五、扩展统计（Extended Statistics）

PostgreSQL 10+ 引入了扩展统计，解决单列统计无法表达列间关系的问题。

5.1 多元统计（ndistinct 与 functional dependency）

-- 创建多元 ndistinct 统计：统计 (country, city) 的组合唯一值数量
CREATE STATISTICS IF NOT EXISTS st_orders_location
ON country, city
FROM orders;

ANALYZE orders;  -- 必须重新分析才能收集扩展统计

创建后，计划器在估算 WHERE country = 'CN' AND city = 'Shanghai' 时，会使用真实的联合唯一值数，而非两个独立选择性的简单乘积（独立假设）。

5.2 函数依赖统计

-- 如果 city 完全由 country 决定（如 ShangHai 一定属于 CN）
CREATE STATISTICS IF NOT EXISTS st_orders_depend
ON country, city
FROM orders;

-- PostgreSQL 14+ 支持创建时指定统计类型
CREATE STATISTICS st_orders_location (dependencies, ndistinct, mcv)
ON country, city
FROM orders;

5.3 MCV 多元统计

-- 多元 MCV：收集 (country, city) 的高频组合
CREATE STATISTICS st_orders_mcv (mcv)
ON country, city
FROM orders;

ANALYZE orders;

多元 MCV 对存在高频组合的表特别有效，比如电商场景中 (state = 'delivered', payment_method = 'card') 非常常见，单列 MCV 无法捕捉这种联合模式。

5.4 查看扩展统计

-- 扩展统计的定义
SELECT stxname, stxkeys, stxkind
FROM pg_statistic_ext
WHERE stxrelid = 'orders'::regclass;

-- 扩展统计的详细值（需要 superuser）
SELECT stxname, stxkeys, stxkind
FROM pg_statistic_ext_data;

六、pg_hint_plan 干预执行计划

6.1 当计划器估算出错时的兜底方案

-- 安装扩展（每个数据库）
CREATE EXTENSION IF NOT EXISTS pg_hint_plan;

6.2 常用 Hint 语法

-- 强制使用索引扫描
/*+ IndexScan(users idx_users_email) */
SELECT * FROM users WHERE email = 'test@example.com';

-- 强制 NestLoop 连接顺序
/*+ NestLoop(o u) */
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.email = 'alice@example.com';

-- 强制 Parallel 执行
/*+ Parallel(orders 4) */
SELECT count(*) FROM orders WHERE status = 'completed';
Hint含义
SeqScan(table)强制顺序扫描
IndexScan(table index)强制索引扫描
IndexOnlyScan(table index)强制 Index Only Scan
BitmapScan(table index)强制 Bitmap Index Scan
NestLoop(t1 t2)强制 NestLoop 连接
HashJoin(t1 t2)强制 Hash Join
MergeJoin(t1 t2)强制 Merge Join
Parallel(table n)强制 n 个并行 worker

6.3 Hint 的适用边界

pg_hint_plan 应作为临时干预手段，而非长期方案。真正合理的做法是：

  1. 先用 ANALYZE 确保统计信息新鲜
  2. 检查是否需要多元扩展统计
  3. 检查是否需要调整连接参数或索引
  4. 统计信息充分后计划仍然错误，才考虑加 hint

常见问题（FAQ）

ANALYZE 会锁表吗？

不会阻塞读写。ANALYZE 只获取 SHARE UPDATE EXCLUSIVE 锁（与 VACUUM、CREATE INDEX CONCURRENTLY 共享），不阻塞 SELECT 或 DML，但会阻塞 DDL。

为什么表有索引但计划器不选 Index Scan？

最常见的原因是统计信息过时（reltuples 与实际行数差异巨大），导致计划器低估了 Index Scan 的受益（或高估了 Sequential Scan 的成本）。先 ANALYZE 再看计划。

pg_hint_plan 的优先级高于 ANALYZE 吗？

是的。hint 是直接干预执行计划的选择，排在统计估算之后。一旦加了 hint，计划器就按 hint 指定的方式执行。

扩展统计会增加多少空间？

微乎其微。扩展统计存储在 pg_statistic_ext 和 pg_statistic_ext_data 中，通常每个统计对象只占用几 KB，对性能无影响。

相关阅读

延伸阅读

完整示例（一键复制）

-- ========== 1. ANALYZE 操作 ==========
ANALYZE users;
ANALYZE (VERBOSE) orders;
ANALYZE;  -- 全库

-- ========== 2. 查看表统计概览 ==========
SELECT relname, reltuples, relpages
FROM pg_class
WHERE relname IN ('users', 'orders', 'products')
ORDER BY reltuples DESC;

-- ========== 3. 查看列级统计（pg_stats） ==========
SELECT attname,
       n_distinct,
       array_length(most_common_vals::text::text[], 1) AS mcv_count,
       left(most_common_vals::text, 60) AS mcv,
       most_common_freqs[1:3] AS top_3_freqs,
       left(histogram_bounds::text, 60) AS histogram,
       correlation
FROM pg_stats
WHERE tablename = 'users'
ORDER BY attname;

-- ========== 4. 对比估算行数与实际行数 ==========
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE created_at >= '2026-09-01'
  AND created_at < '2026-10-01';

-- ========== 5. 提高单列统计精度 ==========
ALTER TABLE orders ALTER COLUMN total_amount SET STATISTICS 500;
ANALYZE orders;

-- ========== 6. 多元扩展统计 ==========
CREATE STATISTICS st_orders_location (dependencies, ndistinct, mcv)
ON country, city
FROM orders;

ANALYZE orders;

-- 查看扩展统计
SELECT stxname, stxkeys, stxkind
FROM pg_statistic_ext
WHERE stxrelid = 'orders'::regclass;

-- ========== 7. pg_hint_plan 常用 Hint ==========
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_hint_plan;

-- 强制索引扫描
/*+ IndexScan(users idx_users_email) */
SELECT * FROM users WHERE email = 'test@example.com';

-- 强制连接方式
/*+ HashJoin(o u) */
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';

-- 强制并行
/*+ Parallel(orders 4) */
SELECT count(*) FROM orders WHERE status = 'completed';

-- ========== 8. 估算准确性检查 ==========
SELECT
  relname,
  reltuples AS estimated_rows,
  (SELECT count(*) FROM orders) AS actual_rows,
  round(100.0 * abs(reltuples - (SELECT count(*) FROM orders))
        / GREATEST((SELECT count(*) FROM orders), 1), 2) AS error_pct
FROM pg_class
WHERE relname = 'orders';

继续阅读

探索更多技术文章

浏览归档，发现更多关于系统设计、工具链和工程实践的内容。

全部文章 返回首页

「database」更多文章

  1. Supabase 平台与 PostgreSQL 边缘函数实践
  2. PostgreSQL 事件触发器与审计日志实现
  3. Kubernetes 上 PostgreSQL 运维与 CloudNativePG 实战