数据库教程FGMT66-PostgreSQL执行计划与统计信息

## 前言

在数据库的日常运维与开发中，SQL查询性能始终是DBA和开发人员最常面对、也最棘手的问题之一。风哥在多年一线数据库运维和教学中反复看到这样一个现象：很多"跑不动"的SQL，语法完全没有问题，索引也都建了，但执行起来就是慢。究其根源，绝大多数时候并不是SQL写错了，而是优化器在"信息不足"或"信息失真"的情况下做出了错误的执行计划选择。一条SQL一旦执行计划选错，哪怕服务器硬件再好，也快不起来。因此，看懂执行计划、管好统计信息，是每个PostgreSQL使用者绕不开的核心技能。

本套风哥教程围绕"PostgreSQL执行计划与统计信息"展开系统讲解。全文分为三个部分。第一部分是理论基础，风哥会讲清楚基于代价的优化器到底是怎么工作的、执行计划由哪些节点构成、各种扫描和连接方式有什么特点、统计信息在系统表里是怎么组织的、成本参数又是如何参与计算的。第二部分是实战操作，风哥会带着大家从零开始，一步步完成环境准备、用EXPLAIN查看执行计划、解读计划中的关键指标、管理统计信息、创建扩展统计信息、诊断典型慢查询，并配合pg_stat_statements做全链路监控。第三部分是风哥针对本文总结，把要点收拢成可操作的清单。

需要说明的是，本套风哥教程所有实战操作均基于两台数据库主机 fgedu-net-cn1 与 fgedu-net-cn2 完成，服务器配置为64G内存、8CPU核心，数据库软件与数据目录统一安装在 /fgedudb 下，数据库实例名、数据库名及业务用户均采用 fgedudb/fgedudb/fgedu 作为示例。文中所有配置参数均按照64G内存、8CPU的硬件规格给出，读者可以根据自己机器的实际配置等比换算。

---

## 第一部分 理论基础

### 1.1 什么是基于代价的查询优化器

PostgreSQL采用基于代价的查询优化器（Cost-Based Optimizer，简称CBO）。理解CBO，是理解执行计划的前提。CBO的核心思想是：不再依靠一套写死的规则来决定"这条SQL必须怎么执行"，而是先枚举出所有可能的执行路径，给每条路径估算一个"代价"，然后选择代价最小的那一条作为最终执行计划。

CBO的工作大体可以拆成三个阶段。第一阶段是查询重写。SQL经过词法、语法分析生成语法树之后，优化器会对语法树做一系列等价变换，比如把视图展开成底层基表查询、把可以下推的过滤条件尽可能推到靠近数据源的位置、把能够常量折叠的表达式在规划期就计算好。这一阶段本身不产生执行计划，但决定了后续规划器看到的查询形态。第二阶段是计划生成。优化器在重写后的查询树上枚举各种可能的执行方案：每张表用哪种扫描方式，多张表之间按什么顺序连接，每对表用哪种连接算法。每枚举出一种组合，就调用代价计算函数给它打分。第三阶段是计划选择。在所有候选计划中选择总代价最小的那一个，输出为最终执行计划。

这里有一个值得展开的技术细节：多表连接顺序的枚举。当参与连接的表数量较少时，PostgreSQL默认使用动态规划算法，尽可能枚举所有连接顺序来保证找到全局最优解；但当表数量超过一定阈值（通常接近十张以上）时，连接顺序的组合数会爆炸式增长，继续穷举会耗费太长的规划时间，此时PostgreSQL会切换成遗传算法，用启发式的方式在较短时间内找出一个"足够好"的连接顺序，而不是严格意义上的全局最优。这个权衡是为了避免"规划时间比执行时间还长"的尴尬。

风哥想特别强调一点：CBO的"代价最优"完全建立在统计信息准确的前提之上。如果统计信息缺失、过时，或者被不合理的参数误导，优化器对行数的估算就会失真，进而连累对扫描方式、连接顺序、连接算法、排序策略等一系列判断。换句话说，执行计划出错，十有八九不是优化器的错，而是"喂给"优化器的统计信息出了问题。这也是本套风哥教程把"执行计划"和"统计信息"放在一起讲的根本原因。

### 1.2 执行计划的基本结构

一条SQL的执行计划以树状结构呈现。树的根节点是最终输出结果的节点，叶子节点通常是各种表扫描节点，中间节点则是连接、聚合、排序、过滤等操作节点。当我们用EXPLAIN命令查看执行计划时，输出会用缩进层次来表示这些节点之间的父子关系：缩进越深，说明节点越靠近数据源，执行时越靠前。

每个执行计划节点都包含几类关键信息，风哥逐一说明。第一是节点类型，它直接告诉我们这一步在做什么。Seq Scan表示顺序扫描整张表，Index Scan表示走索引再回表取数据，Index Only Scan表示仅靠索引就能拿到所有需要的列，Bitmap Index Scan表示先用索引构建位图、再按物理顺序读数据页，Hash Join表示哈希连接，Nested Loop表示嵌套循环连接，Merge Join表示合并连接，Sort表示排序，Aggregate表示聚合，Limit表示截断输出。识别节点类型，是读执行计划的第一步。

第二是代价信息。每个节点都会给出形如"cost=启动代价..总代价"的数字。启动代价指该节点开始输出第一行数据之前必须付出的代价，比如排序节点要先读完全部输入才能输出第一行，它的启动代价就很高；总代价指该节点输出全部结果行的累计代价。这两个数字是相对单位，不是真实的执行毫秒数，它们由后面的成本参数加权折算而来，用于在候选计划之间做横向比较。

第三是行数估算。rows字段表示优化器预估该节点将返回多少行。这个数字直接来自统计信息，是判断统计信息是否准确、执行计划是否靠谱的最重要指标之一。第四是实际执行信息。当我们使用EXPLAIN ANALYZE时，PostgreSQL会真实地把这条SQL执行一遍，并在每个节点上附带actual time（实际耗时）、actual rows（实际行数）、actual loops（该节点实际循环执行次数）。把"预估行数"和"实际行数"放在一起对比，统计信息准不准、执行计划选得对不对，就一目了然了。

### 1.3 表扫描节点类型

PostgreSQL支持多种表扫描方式，优化器根据统计信息和成本参数自动选择最合适的一种。风哥逐个讲解。

顺序扫描（Seq Scan）是最朴素的扫描方式：从表的第一页开始，按物理顺序把所有数据页从头读到尾。它的特点是磁盘访问完全顺序化、预读效率高，因此在两种场景下最优：一是表非常小，顺序读一遍代价极低；二是查询需要返回表中大部分行（比如过滤条件命中超过百分之二三十的数据），顺序扫描避免了大量随机回表。很多初学者一看到Seq Scan就紧张，其实在低选择性查询下，Seq Scan恰恰是最优选择。

索引扫描（Index Scan）的路径是：先在索引里定位到满足条件的索引项，再根据索引项里记录的物理位置（即堆元组的页面和偏移），回表读取完整的元组数据。它的优势在于当查询只返回少量行时，可以避免扫描整张表；但代价是每一步都伴随一次随机I/O，如果回表次数过多，累积的随机I/O代价可能反超顺序扫描。

位图索引扫描（Bitmap Index Scan + Bitmap Heap Scan）是一种折中方案，分两步执行：第一步，用索引把所有满足条件的堆页面位置记录到一个位图里；第二步，按照页面的物理顺序，把位图标记过的数据页整页读出来。这样做的好处是，既利用索引缩小了需要访问的数据页范围，又把随机I/O转换成接近顺序的I/O。它最适合返回行数处于"中等规模"的查询，既不想走纯索引的随机回表，又没必要全表扫描。

仅索引扫描（Index Only Scan）是效率最高的扫描方式。如果查询需要的所有列都包含在某一个索引里，PostgreSQL可以直接从索引本身返回数据，完全不需要回表。但要实现这一点有两个前提：一是索引必须覆盖查询的所有列；二是相关数据页在可见性映射（Visibility Map，VM）里被标记为"对所有事务都可见"，这样优化器才能放心地不去回表确认元组可见性。如果VM信息不足，即使索引覆盖了所有列，优化器也可能被迫退化为普通Index Scan。

### 1.4 连接节点类型

当SQL涉及多张表连接时，PostgreSQL主要支持三种连接算法，各有适用场景。

嵌套循环连接（Nested Loop Join）的逻辑最直观：外层表每读取一行，就到内层表里去查找与之匹配的行。它的成本等于"外层行数 × 内层单次查找成本"。因此它最擅长两种场景：一是外层结果集很小（可能就几十行），即使每行都要查一次内层，总代价也很低；二是内层表有非常高效的索引，单次查找代价极小。反过来，一旦外层结果集很大，嵌套循环的总代价就会随外层行数线性放大，性能往往很差。

哈希连接（Hash Join）分为两个阶段：构建阶段，扫描第一张表，把连接键作为哈希键，在内存里构建一张哈希表；探测阶段，扫描第二张表，对每一行用连接键做哈希，去哈希表里查找匹配项。哈希连接的优势在于，无论两张表多大，它都只需要各扫描一次，非常适合"大表连大表"或"没有合适索引"的场景。但它的软肋是内存：如果构建哈希表所需内存超过了work_mem上限，哈希表会分批溢写到磁盘，每溢出一批都要多一次往返，性能显著下降。

合并连接（Merge Join）要求两张表都按连接键排好序，然后像"归并排序"那样，用两个指针同步推进，逐行配对。它的最大优势是稳定：只要输入有序，扫描过程是顺序且一次性的，不需要额外构建哈希表。因此它适合两类场景：一是两张表的输入天然有序，二是一对多连接需要严格按序输出的情况。但要注意，如果两张表本来无序、需要现场排序，那排序本身的代价就可能抵消合并连接带来的优势。

### 1.5 统计信息的内部结构

PostgreSQL的统计信息实际存储在系统表 pg_statistic 中，但DBA很少直接查这张系统表，而是查它的视图 pg_stats。pg_stats 把统计信息整理成易读的字段，每个字段对应一张表里的一个列。

pg_stats 中最核心的几个字段，风哥逐个讲清楚。most_common_vals（MCV，最常见值列表）及其配套的 most_common_freqs（最常见值频率）：它记录该列中出现频率最高的若干个具体值，以及每个值出现的频率占比。当查询用"列 = 某个值"做等值过滤时，如果这个值正好落在MCV列表里，优化器就能直接查频率得到非常准的行数估算；如果不在MCV里，优化器只能用别的方式近似。因此MCV对等值查询的行数估算准确度起着决定性作用。

histogram_bounds（直方图边界值）：它把该列的数据取值范围切分成若干个桶，每个桶的边界值记下来，桶内认为大致等量。直方图主要用于处理范围查询（大于、小于、介于之间）的行数估算：优化器用过滤边界去定位落在哪个桶、占桶内多大比例，从而估算出命中行数。对于数据分布均匀的列，直方图的估算效果很好；对于分布畸形的列，效果会打折，这时需要靠MCV来兜底。

n_distinct：该列distinct值的个数。这个字段有两种表示法：正值代表"实际有N个不同值"；负值代表"不同值数量约占总行数的比例"，比如-0.5表示不同值约占总行数的一半。n_distinct直接影响GROUP BY去重、以及连接时对连接基数的估算。

correlation（相关性）：该列数据的物理存储顺序与逻辑排序顺序之间的相关性，取值范围-1到1。值为1表示物理存储顺序与逻辑排序完全一致，此时按该列做范围查询可以利用物理顺序的连续性，代价估算会更乐观；值为负1表示完全反向；接近0表示完全乱序。correlation主要影响范围查询和排序的代价估算。

需要特别指出的是，单列统计信息有一个天然短板：它假设各列之间相互独立。当多个查询条件涉及的列之间存在强相关性时，单列统计信息会把两个条件的选择性相乘，导致行数估算严重失真。针对这种情况，PostgreSQL提供了扩展统计信息（CREATE STATISTICS），可以记录多列之间的函数依赖关系和多列组合的distinct值，风哥会在实战部分专门演示。

### 1.6 成本参数与64G内存环境的配置思路

PostgreSQL通过一组成本参数来调节代价计算模型，这些参数直接左右优化器对各种执行路径的"偏好"。风哥列出最常用的几个，并给出64G内存、8CPU环境下的配置建议。

seq_page_cost：顺序读取一个数据页的代价，默认1.0，作为所有成本计价的基准。random_page_cost：随机读取一个数据页的代价，默认4.0。它代表随机I/O相对顺序I/O的代价倍数。在机械硬盘上，随机I/O确实比顺序I/O慢数倍，默认4.0基本合理；但在SSD上，随机读和顺序读的差距大幅缩小，如果仍保持4.0，优化器会过度"害怕"索引扫描的随机回表、偏向选择顺序扫描，从而错过更优的索引路径。因此SSD环境建议把random_page_cost调到2.0甚至1.5。cpu_tuple_cost（处理每行元组的CPU代价，默认0.01）、cpu_index_tuple_cost（处理每个索引条目的CPU代价，默认0.005）、cpu_operator_cost（执行一次操作符或函数调用的CPU代价，默认0.0025）。

effective_cache_size：操作系统层面可用于文件缓存的内存量估算。这个参数并不真实分配内存，它只是让优化器知道"大概有多少数据能留在缓存里"，从而影响它对哈希表容量、位图规模以及重复扫描的代价估算。在64G内存服务器上，风哥建议设为16GB到24GB。work_mem：单个排序操作或哈希操作可使用的内存上限。排序、哈希连接、聚合操作各自独立使用work_mem，当数据量超过它时，多余部分会溢写到磁盘临时文件，性能骤降。但它不是越大越好——每个并发会话的每个排序/哈希都可能各占一份work_mem，设得过大反而容易把内存撑爆。在64G内存、考虑一定并发的情况下，风哥建议设为8MB到16MB。maintenance_work_mem：用于VACUUM、CREATE INDEX、ANALYZE等维护操作的独立内存。这些操作往往单实例运行，可以给得比较宽裕，风哥建议设为1GB到2GB，能明显加快建索引和vacuum的速度。

风哥把64G/8CPU环境下的推荐组合整理如下：seq_page_cost保持1.0；random_page_cost按存储介质取2.0（SSD）或4.0（机械盘）；effective_cache_size设为16GB；work_mem设为8MB；maintenance_work_mem设为1GB；autovacuum_max_workers设为3到4。这些数值在后续实战中会反复用到，读者可先记住。

风哥 itpux-com

---

## 第二部分 实战操作

### 2.1 环境准备

本套风哥教程的实战环境为两台服务器，主机名分别为 fgedu-net-cn1 和 fgedu-net-cn2，每台为64G内存、8CPU核心。PostgreSQL软件安装在 /fgedudb 目录，数据目录为 /fgedudb/data，实例名为 fgedudb，业务数据库名为 fgedudb，业务用户名为 fgedu。

先登录 fgedu-net-cn1，切换到数据库管理用户并设置环境变量：

```bash

ssh fgedu@fgedu-net-cn1

su - postgres

export PGDATA=/fgedudb/data

export PATH=/fgedudb/bin:$PATH

```

连接本地数据库：

```bash

psql -h 127.0.0.1 -p 5432 -U fgedu -d fgedudb

```

确认版本与连接信息：

```sql

SELECT version();

SELECT current_user, current_database(), inet_server_addr(), inet_server_port();

```

接下来风哥创建两张用于实战演示的业务表：订单表orders和用户表users，并灌入一定量的测试数据，模拟一个电商场景。

```sql

CREATE TABLE orders (

    order_id      bigint PRIMARY KEY,

    user_id       bigint NOT NULL,

    order_status  smallint NOT NULL,

    amount        numeric(12,2) NOT NULL,

    create_time   timestamp NOT NULL,

    product_cat   varchar(50),

    province      varchar(20),

    city          varchar(20)

);

CREATE TABLE users (

    user_id    bigint PRIMARY KEY,

    user_name  varchar(50) NOT NULL,

    province   varchar(20),

    city       varchar(20),

    reg_time   timestamp NOT NULL

);

```

灌入测试数据：orders表生成100万行，users表生成10万行。

```sql

INSERT INTO users

SELECT 

    i,

    'user_' || i,

    (ARRAY['四川','广东','浙江','北京','上海','江苏','山东','河南'])[1 + (random()*7)::int],

    (ARRAY['成都','广州','杭州','北京','上海','南京','济南','郑州'])[1 + (random()*7)::int],

    timestamp '2020-01-01' + (random() * 1800) * interval '1 day'

FROM generate_series(1, 100000) i;

INSERT INTO orders

SELECT 

    i,

    1 + (random()*100000)::bigint,

    (ARRAY[1,2,3,4,5])[1 + (random()*4)::int],

    round((random()*5000 + 10)::numeric, 2),

    timestamp '2024-01-01' + (random()*365) * interval '1 day',

    (ARRAY['电子产品','服装','食品','家居','图书','美妆'])[1 + (random()*5)::int],

    (ARRAY['四川','广东','浙江','北京','上海','江苏','山东','河南'])[1 + (random()*7)::int],

    (ARRAY['成都','广州','杭州','北京','上海','南京','济南','郑州'])[1 + (random()*7)::int]

FROM generate_series(1, 1000000) i;

```

创建常用索引：

```sql

CREATE INDEX idx_orders_user_id ON orders(user_id);

CREATE INDEX idx_orders_status ON orders(order_status);

CREATE INDEX idx_orders_create_time ON orders(create_time);

CREATE INDEX idx_orders_province ON orders(province);

CREATE INDEX idx_orders_amount ON orders(amount);

```

Frist收集统计信息：

```sql

ANALYZE orders;

ANALYZE users;

```

到这里，实战环境就绪。风哥需要提醒一句：上面这批数据里，province和city两列是故意用同一组下标生成的，也就是说"四川"基本对应"成都"、"广东"基本对应"广州"，两列存在强相关性，这是后续演示扩展统计信息时用到的伏笔。

风哥教程 113257174

### 2.2 用EXPLAIN查看执行计划

EXPLAIN是查看执行计划的基础命令。风哥从最简单的用法开始。

等值查询主键：

```sql

EXPLAIN SELECT * FROM orders WHERE order_id = 12345;

```

输出大致如下：

```

Index Scan using orders_pkey on orders  (cost=0.42..8.44 rows=1 width=56)

  Index Cond: (order_id = 12345)

```

这表示优化器选择了主键索引扫描，预估返回1行。cost=0.42..8.44表示启动代价0.42、总代价8.44。

再看一个全表聚合的例子：

```sql

EXPLAIN SELECT count(*) FROM orders;

```

```

Aggregate  (cost=20500.00..20500.01 rows=1 width=8)

  ->  Seq Scan on orders  (cost=0.00..18000.00 rows=1000000 width=0)

```

优化器选择了顺序扫描，因为count(*)必须读取所有行，没有任何索引能直接给出总数。

接着演示EXPLAIN的几个常用选项。EXPLAIN ANALYZE会真实执行SQL，并显示每个节点的实际耗时和实际行数：

```sql

EXPLAIN ANALYZE 

SELECT * FROM orders WHERE user_id = 54321 ORDER BY create_time DESC LIMIT 20;

```

输出中除了预估信息，还会多出actual time、actual rows、actual loops，以及末尾的Planning Time和Execution Time：

```

Limit  (cost=... rows=20) (actual time=0.050..0.080 rows=20 loops=1)

  ->  Sort  (...)

        Sort Key: create_time DESC

        ->  Index Scan using idx_orders_user_id on orders  (...)

              Index Cond: (user_id = 54321)

Planning Time: 0.350 ms

Execution Time: 0.120 ms

```

风哥郑重提醒：EXPLAIN ANALYZE会真正执行SQL。如果SQL是INSERT、UPDATE、DELETE等写操作，这些修改会真实落库。对写操作想看清执行计划又不想真的改动数据，可以放在事务里执行后ROLLBACK，或者干脆用不带ANALYZE的EXPLAIN。

EXPLAIN BUFFERS显示缓冲区命中与磁盘读取情况：

```sql

EXPLAIN (ANALYZE, BUFFERS) 

SELECT * FROM orders WHERE amount > 3000 ORDER BY amount LIMIT 100;

```

输出中会出现"Buffers: shared hit=xxx read=xxx"。shared hit表示从共享缓冲区直接命中，read表示真正从磁盘读取。这个数字是判断SQL是否产生大量物理I/O的关键依据：如果read很大而hit很小，说明数据不在缓存里、每次都要走磁盘，是典型的I/O瓶颈信号。

EXPLAIN VERBOSE显示更详细的信息，包括每个节点的输出列清单：

```sql

EXPLAIN VERBOSE 

SELECT order_id, amount FROM orders WHERE province = '四川';

```

网上搜索风哥教程可以学习全套数据库教程

### 2.3 解读执行计划中的关键指标

很多初学者看执行计划时只关心"有没有走索引"，风哥认为这远远不够。正确读计划，要从几个维度综合判断。

第一，对比预估行数与实际行数。用EXPLAIN ANALYZE时，每个节点都有rows（预估）和actual rows（实际）。两者相差在10倍以内，统计信息基本可信；相差超过100倍，说明统计信息严重失真，必须重新ANALYZE或调整统计精度。这是最快的"体检"手段。

风哥现场演示一个统计信息失真的场景。往orders表里插入一批 order_status=99 的订单（这批数据占总量比例很小，但分布特殊）：

```sql

INSERT INTO orders

SELECT 

    1000000 + i,

    1 + (random()*100000)::bigint,

    99,

    round((random()*1000 + 10)::numeric, 2),

    timestamp '2025-01-01' + (random()*30) * interval '1 day',

    '测试',

    '四川',

    '成都'

FROM generate_series(1, 10000) i;

```

此时故意不执行ANALYZE，直接查 order_status=99：

```sql

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_status = 99;

```

观察输出里预估行数与实际行数的差异。因为新插入的数据还没被ANALYZE吸收进统计信息，优化器对 order_status=99 行数的估算会明显偏小，甚至可能误以为这个值不存在而严重低估。

现在手动执行ANALYZE，再对比：

```sql

ANALYZE orders;

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_status = 99;

```

重新查看后，预估行数应明显逼近实际行数。这个对比直观地证明了"统计信息过期会让执行计划失真"。

第二，关注节点的循环次数loops。在嵌套循环连接里，外层每读一行，内层节点就要执行一次，loops就表示内层节点实际执行了几次。如果外层结果集很大，loops会是一个很大的数，那么内层单次代价×loops才是真实总代价。只看单次cost会严重低估实际开销。

第三，关注排序方式。Sort节点如果显示"Sort Method: External Merge Disk: xxx kB"，说明排序把数据写到了磁盘临时文件，是work_mem不够的信号。解决办法要么调大work_mem，要么改写SQL、让结果在索引里已经有序。

第四，关注哈希连接的内存使用。Hash节点如果出现"Hash Batches: N"且N大于1，说明哈希表溢写到了磁盘。理想情况是batches=1，表示全部在内存中完成。batches越大，哈希连接的性能损失越明显。

风哥数据库教程 itpux-com

### 2.4 统计信息管理实战

#### 2.4.1 查看统计信息

通过pg_stats视图查看列级统计信息：

```sql

SELECT 

    attname,

    n_distinct,

    correlation,

    most_common_vals,

    most_common_freqs,

    histogram_bounds

FROM pg_stats

WHERE tablename = 'orders'

ORDER BY attname;

```

风哥建议重点关注：order_id的n_distinct应为-1（基本唯一）；user_id的n_distinct应为正数且接近真实用户数；order_status的MCV里会列出1到5各占的比例；province的MCV里会列出8个省份各自的占比；create_time的correlation应接近1（因为按时间顺序插入），而province的correlation应接近0（乱序）。

#### 2.4.2 调整统计信息收集精度

统计信息的精度由参数default_statistics_target控制，默认值为10。这个值决定MCV列表保留多少个最常见值、直方图切多少个桶。对大部分列，默认10已经够用；但对数据分布不均匀、且频繁作为查询条件的关键列，可以单独提高精度：

```sql

ALTER TABLE orders ALTER COLUMN amount SET STATISTICS 100;

ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 100;

```

设置后重新ANALYZE才生效：

```sql

ANALYZE orders;

```

验证调整效果：

```sql

SELECT attname, n_distinct, correlation 

FROM pg_stats 

WHERE tablename = 'orders' AND attname IN ('amount','user_id');

```

风哥建议：不要全局盲目调高default_statistics_target，否则所有表的ANALYZE都会变慢、pg_statistic也会膨胀；而是对"行数估算经常出错的列"单独设置STATISTICS，精准施策。

#### 2.4.3 扩展统计信息（CREATE STATISTICS）

当多个查询条件的列存在相关性时，单列统计信息会按"列独立"假设把各条件选择性相乘，导致行数估算严重偏小。实战环境里province和city是强相关的，风哥用它来演示。

先看没有扩展统计信息时，带两列条件的查询：

```sql

EXPLAIN ANALYZE 

SELECT * FROM orders WHERE province = '四川' AND city = '成都';

```

因为province=四川本身约命中12.5%（1/8）的行，city=成都在单列统计里也约12.5%，独立假设把两者相乘得到约1.6%的估算；但实际因为两列强相关，命中比例接近12.5%，所以预估行数会明显小于实际行数。

创建扩展统计信息，同时记录函数依赖和组合distinct值：

```sql

CREATE STATISTICS stat_orders_prov_city (dependencies, ndistinct)

ON province, city FROM orders;

```

重新ANALYZE使扩展统计信息生效：

```sql

ANALYZE orders;

```

再次执行同样查询，对比行数估算是否变准：

```sql

EXPLAIN ANALYZE 

SELECT * FROM orders WHERE province = '四川' AND city = '成都';

```

dependencies类型会记录"province能决定city的取值范围"这种函数依赖，让优化器知道两列不是独立事件；ndistinct类型记录两列组合的去重组合数。两者配合，能显著改善多列等值查询的行数估算。

查看已创建的扩展统计信息：

```sql

SELECT * FROM pg_statistic_ext;

```

#### 2.4.4 autovacuum自动统计信息收集

PostgreSQL通过autovacuum守护进程自动执行VACUUM和ANALYZE。当一张表的数据变化量超过阈值时，autovacuum会自动触发ANALYZE更新统计信息。

触发ANALYZE的阈值公式为：autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × 表行数。只要插入/更新/删除的累计行数超过这个值，就会触发一次analyze。

相关配置参数（写在postgresql.conf）：

```ini

# 启用autovacuum

autovacuum = on

# 触发自动analyze的阈值：基础阈值50 + 比例因子0.1*表行数

autovacuum_analyze_threshold = 50

autovacuum_analyze_scale_factor = 0.1

# 64G/8CPU环境建议的并发维护进程数

autovacuum_max_workers = 4

# 维护工作内存，加速analyze与vacuum

maintenance_work_mem = 1GB

```

风哥提醒：在写入频繁的大表上，如果自动analyze不够及时，统计信息会与实际数据分布脱节。可以适当降低autovacuum_analyze_scale_factor（例如0.05）让自动analyze更频繁。但风哥不建议过度依赖自动analyze——对关键大表，在批量数据变更完成后手动执行ANALYZE，才是最可控、最可靠的做法。

上51CTO搜索风哥可以学习全套数据库教程

### 2.5 典型执行计划场景分析

#### 2.5.1 高选择性查询却走了全表扫描

当查询条件选择性高（应返回少量行）却走了Seq Scan时，多半是下面几个原因。风哥逐一演示。先看基础SQL：

```sql

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 99999;

```

user_id上有索引，正常应走Index Scan。如果出现Seq Scan，首先怀疑统计信息过期——执行：

```sql

ANALYZE orders;

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 99999;

```

如果ANALYZE后仍是Seq Scan，检查该列统计信息：

```sql

SELECT attname, n_distinct, most_common_vals 

FROM pg_stats 

WHERE tablename = 'orders' AND attname = 'user_id';

```

如果n_distinct估算异常（例如被高估），优化器会以为某个user_id返回很多行，从而选择全表扫描。此时可以单独提高该列统计精度后重新ANALYZE。

还有一个常见原因是random_page_cost设置过高，导致优化器"不敢"走索引的随机回表。临时调低验证：

```sql

SET random_page_cost = 2.0;

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 99999;

```

如果计划从Seq Scan切到Index Scan，说明确实是被成本参数误导。在SSD环境，把这个参数在配置里稳定调低是合理的。

#### 2.5.2 嵌套循环连接过慢

两表连接时，如果外层结果集大，Nested Loop会非常慢。风哥演示：

```sql

EXPLAIN ANALYZE 

SELECT o.order_id, u.user_name, o.amount 

FROM orders o 

JOIN users u ON o.user_id = u.user_id 

WHERE o.create_time >= '2024-06-01';

```

观察连接类型。如果走Nested Loop且外层orders返回了大量行，内层users被循环执行成千上万次，性能必然很差。此时可用开关诊断是否Hash Join更优：

```sql

SET enable_nestloop = off;

EXPLAIN ANALYZE 

SELECT o.order_id, u.user_name, o.amount 

FROM orders o 

JOIN users u ON o.user_id = u.user_id 

WHERE o.create_time >= '2024-06-01';

```

对比两种计划的Execution Time。风哥强调，enable_nestloop这类开关只用于诊断和临时调优，不建议作为长期配置。如果优化器经常选错连接类型，根本原因多半是统计信息不准或成本参数不合理，应从那两个方向入手。

#### 2.5.3 位图扫描的场景

位图扫描适合返回"中等规模"行数的查询。风哥演示：

```sql

EXPLAIN ANALYZE 

SELECT * FROM orders WHERE amount BETWEEN 1000 AND 2000;

```

amount上有索引，且命中比例约在5%到20%之间时，优化器很可能选择"Bitmap Index Scan + Bitmap Heap Scan"。执行流程是：先用索引把命中的数据页标记到位图，再按物理顺序读出这些页。观察计划里的Bitmap Heap Scan节点，它通过位图过滤数据页后顺序读取，兼顾了索引过滤和顺序I/O。

#### 2.5.4 排序与磁盘临时文件

当ORDER BY数据量超过work_mem时，排序溢写磁盘。风哥演示需要大量排序的场景：

```sql

EXPLAIN ANALYZE 

SELECT * FROM orders 

ORDER BY amount DESC 

LIMIT 100;

```

如果Sort节点显示"Sort Method: QuickSort Memory: xxx kB"，说明完全在内存完成；如果显示"Sort Method: External Merge Disk: xxx kB"，说明用了磁盘临时文件。查看当前work_mem：

```sql

SHOW work_mem;

```

临时调大后重测：

```sql

SET work_mem = '64MB';

EXPLAIN ANALYZE 

SELECT * FROM orders 

ORDER BY amount DESC 

LIMIT 100;

```

再次观察排序方式是否变为内存排序。风哥提醒，在64G内存环境work_mem建议8MB到16MB，因为每个会话的排序、哈希、聚合各自占用work_mem，设得过大在多并发下反而撑爆内存。

#### 2.5.5 仅索引扫描的触发条件

当查询所有列都包含在某个索引时，PostgreSQL可走Index Only Scan，无需回表。

```sql

EXPLAIN ANALYZE 

SELECT order_id, user_id FROM orders WHERE user_id = 54321;

```

idx_orders_user_id索引包含user_id列，且PostgreSQL的B-tree索引自动附带主键列order_id，因此这个查询应走Index Only Scan。如果显示的仍是Index Scan而非Index Only Scan，多半是因为可见性映射（VM）未覆盖所有页面，优化器不敢确定元组对当前事务可见、必须回表。执行一次VACUUM刷新VM：

```sql

VACUUM orders;

EXPLAIN ANALYZE 

SELECT order_id, user_id FROM orders WHERE user_id = 54321;

```

风哥 itpux-com

### 2.6 慢查询诊断全流程实战

风哥用一条"业务反馈很慢"的SQL，演示完整的诊断流程。

慢查询SQL：

```sql

SELECT o.order_id, u.user_name, o.amount, o.create_time

FROM orders o

JOIN users u ON o.user_id = u.user_id

WHERE o.province = '四川' 

  AND o.order_status = 2 

  AND o.amount > 500

ORDER BY o.create_time DESC

LIMIT 50;

```

第一步，先看执行计划：

```sql

EXPLAIN ANALYZE 

SELECT o.order_id, u.user_name, o.amount, o.create_time

FROM orders o

JOIN users u ON o.user_id = u.user_id

WHERE o.province = '四川' 

  AND o.order_status = 2 

  AND o.amount > 500

ORDER BY o.create_time DESC

LIMIT 50;

```

第二步，按上节学到的方法逐项检查：预估行数与实际行数差距多大？差距超10倍就先ANALYZE。连接类型是什么？Nested Loop的loops次数是否很大？排序是否用了磁盘临时文件？能否用索引替代排序？有没有Seq Scan？缺不缺合适索引？

第三步，针对发现的问题优化。例如：province列选择性差（只有8个省份），单独建province索引意义不大；但order_status和amount组合起来选择性更好，可以建复合索引：

```sql

CREATE INDEX idx_orders_status_amount ON orders(order_status, amount);

```

如果业务上对"未完成订单"的按时间倒序查询很频繁，还可以用部分索引，只索引特定状态：

```sql

CREATE INDEX idx_orders_status_pending ON orders(create_time DESC) 

WHERE order_status = 2;

```

建索引后重新ANALYZE并复看计划：

```sql

ANALYZE orders;

EXPLAIN ANALYZE 

SELECT o.order_id, u.user_name, o.amount, o.create_time

FROM orders o

JOIN users u ON o.user_id = u.user_id

WHERE o.province = '四川' 

  AND o.order_status = 2 

  AND o.amount > 500

ORDER BY o.create_time DESC

LIMIT 50;

```

第四步，如果行数估算仍不准，考虑order_status与province的相关性，创建扩展统计信息：

```sql

CREATE STATISTICS stat_orders_status_prov (dependencies)

ON order_status, province FROM orders;

ANALYZE orders;

```

风哥总结：慢查询优化是一个"观察计划→定位瓶颈→调整索引/统计信息/参数→复看验证"的循环。每次修改后都必须重新EXPLAIN ANALYZE确认效果，不能改完就不管。

风哥教程 113257174

### 2.7 统计信息收集的常见误区

风哥把多年运维中遇到的关于统计信息的误区整理成清单，提醒读者避坑。

误区一：认为ANALYZE执行一次就一劳永逸。实际上一张表经历批量插入、批量更新、批量删除后，旧统计信息很快就过时。批量数据加载完成后必须手动ANALYZE。

误区二：盲目调高default_statistics_target。全局提高会拖慢所有表的ANALYZE、膨胀pg_statistic，大表尤其明显。正确做法是对出错的关键列单独SET STATISTICS。

误区三：忽略autovacuum日志。autovacuum的analyze如果被频繁取消或失败，统计信息会长期不更新。应定期检查日志：

```bash

grep "autovacuum" /fgedudb/data/log/postgresql-*.log | tail -20

```

误区四：在物理备库上执行ANALYZE。PostgreSQL的物理复制备库通常是只读的，不能执行ANALYZE，其统计信息由主库复制而来。备库执行计划看起来不对时，要回到主库检查和调整，而不是在备库上"补"统计信息。

误区五：把VACUUM和ANALYZE混为一谈。VACUUM负责清理死元组、更新可见性映射、回收空间；ANALYZE负责收集统计信息。虽然autovacuum会同时调度两者，但它们解决的是完全不同的问题。判断执行计划准不准，看的是ANALYZE；判断表有没有膨胀，看的是VACUUM。

风哥数据库教程 itpux-com

### 2.8 用pg_stat_statements监控SQL性能

除了EXPLAIN这种"单条SQL体检"手段，PostgreSQL还提供pg_stat_statements扩展，持续累积统计所有SQL的执行数据，帮助发现哪些SQL最耗资源。

先安装扩展：

```sql

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

```

要让它生效，postgresql.conf需配置（修改后需重启数据库）：

```ini

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000

pg_stat_statements.track = all

```

重启后查看最耗时的SQL排行：

```sql

SELECT 

    queryid,

    query,

    calls,

    total_exec_time,

    mean_exec_time,

    rows

FROM pg_stat_statements

ORDER BY total_exec_time DESC

LIMIT 10;

```

查看单次执行最慢的SQL：

```sql

SELECT 

    query,

    calls,

    mean_exec_time,

    max_exec_time

FROM pg_stat_statements

ORDER BY mean_exec_time DESC

LIMIT 10;

```

风哥建议：定期分析pg_stat_statements的输出，锁定那些"总耗时高"或"单次耗时高"的SQL，再逐条用EXPLAIN ANALYZE深入分析执行计划。这比漫无目的地优化所有SQL高效得多。在版本发布或性能基准测试前，需要清空统计：

```sql

SELECT pg_stat_statements_reset();

```

上51CTO搜索风哥可以学习全套数据库教程

### 2.9 双节点执行计划对比（fgedu-net-cn1 与 fgedu-net-cn2）

风哥在 fgedu-net-cn1 与 fgedu-net-cn2 两台主机上演示同一SQL在不同成本参数下的计划差异，帮助读者理解参数对优化器决策的影响。

先在 fgedu-net-cn2 上确认环境一致并查看参数：

```bash

ssh fgedu@fgedu-net-cn2

su - postgres

psql -h 127.0.0.1 -U fgedu -d fgedudb

```

查看当前成本参数：

```sql

SHOW random_page_cost;

SHOW seq_page_cost;

SHOW effective_cache_size;

SHOW work_mem;

```

假设 fgedu-net-cn2 使用SSD存储，把random_page_cost调低到2.0后执行：

```sql

SET random_page_cost = 2.0;

EXPLAIN ANALYZE 

SELECT * FROM orders WHERE amount > 4000 ORDER BY amount;

```

回到 fgedu-net-cn1（假设机械硬盘），保持random_page_cost=4.0执行同样SQL：

```sql

EXPLAIN ANALYZE 

SELECT * FROM orders WHERE amount > 4000 ORDER BY amount;

```

对比两边的执行计划：SSD环境下优化器更倾向选索引扫描（随机I/O成本被调低）；机械盘环境下可能倾向位图扫描或顺序扫描。同样的SQL、同样的数据，只因存储介质不同、参数不同，最优计划就不同——这正是成本参数的意义。

风哥最后把64G内存、8CPU环境下的推荐配置再列一遍，作为本套风哥教程的参考基线：effective_cache_size=16GB、work_mem=8MB、maintenance_work_mem=1GB、random_page_cost按介质取2.0（SSD）或4.0（机械盘）、autovacuum_max_workers=4、autovacuum_analyze_scale_factor=0.1（写入频繁的表可单独调低）。

### 2.10 并行执行计划的认识

在64G内存、8CPU的服务器上，PostgreSQL的并行查询能力值得单独认识，因为它在执行计划里会体现为一类特殊的节点形态。并行执行的核心思想是：把一个顺序执行的操作（扫描、聚合、连接）拆成多个部分，由多个并行worker同时执行，最后由一个leader进程收集结果。这能充分利用8个CPU核心，让原本要跑很久的扫描和聚合明显提速。

并行查询是否被启用，由一组参数共同决定。max_parallel_workers_per_gather控制单个Gather节点最多能并行的worker数，在8CPU环境下风哥建议设为4到8；max_parallel_workers控制整个数据库全局并行的worker总数；min_parallel_table_scan_size和min_parallel_index_scan_size则规定了多小的表不值得并行——表太小或索引太小，并行拆分的通信开销反而比串行更大，优化器会自动放弃。此外，并行还会受work_mem影响，因为每个worker都要独立占用work_mem。

用EXPLAIN看并行计划，会看到Gather节点。风哥演示：

```sql

EXPLAIN ANALYZE 

SELECT count(*) FROM orders WHERE amount > 2000;

```

如果走了并行，计划里会出现类似下面的结构：

```

Finalize Aggregate  (cost=... rows=1)

  ->  Gather  (cost=... rows=...)

        Workers Planned: 4

        ->  Partial Aggregate  (cost=...)

              ->  Parallel Seq Scan on orders  (...)

```

Workers Planned: 4表示规划了4个并行worker，Partial Aggregate表示每个worker各算一部分，Finalize Aggregate表示leader把各worker的部分结果汇总。Parallel Seq Scan表示这张表的顺序扫描被拆成了多份并行执行。

查询是否并行、并行几个worker，本身也依赖统计信息：优化器先用统计信息估算出扫描的行数和代价，再判断并行带来的加速是否值得。如果一张表的统计信息显示它很小，优化器就会觉得并行不划算，从而选择串行。因此，哪怕只关心并行，统计信息依然是一切判断的地基。

风哥提醒，并行不是越多越好。worker之间需要通信、需要独立内存，8CPU环境下并行度建议与核心数匹配或略低，避免因内存和调度开销得不偿失。遇到"明明8核却没用上并行"的情况，优先检查max_parallel_workers_per_gather是否被调低、表是否被统计信息误判为太小，以及Gather节点周围有没有阻塞并行的操作（例如无法并行的排序或函数）。

网上搜索风哥教程可以学习全套数据库教程

---

## 第三部分 风哥针对本文总结

本套风哥教程系统讲解了PostgreSQL执行计划与统计信息的原理与实战。风哥把要点收拢成一份可操作的清单，供读者回顾。

第一，执行计划是理解优化器决策的窗口。用EXPLAIN和EXPLAIN ANALYZE，可以看到优化器选了哪种扫描、哪种连接算法、是否用索引、预估与实际的差距。这是诊断慢查询的第一手资料，一切调优都要从这里开始。

第二，统计信息是执行计划正确性的基石。CBO完全依赖统计信息估算行数和代价，统计信息不准，再好的优化器也会选错路。管理统计信息的核心手段是：用ANALYZE刷新、用ALTER COLUMN SET STATISTICS提高关键列精度、用CREATE STATISTICS处理多列相关性、用autovacuum做自动刷新，缺一不可。

第三，常见的执行计划问题有规律可循。全表扫描往往是统计信息过期或索引选择性差；嵌套循环慢往往是外层结果集太大；排序用磁盘往往是work_mem不足；行数估算偏差大往往是统计信息过时或多列相关性未处理。记住这些规律，诊断就能对号入座。

第四，慢查询优化是持续循环，不是一次性的。要用pg_stat_statements发现瓶颈SQL，用EXPLAIN ANALYZE分析执行计划，定位根因，再调整索引、统计信息或参数，最后复看验证。每一步都必须回到执行计划确认效果。

第五，参数配置要贴合硬件。在64G内存、8CPU的服务器上，random_page_cost、effective_cache_size、work_mem、maintenance_work_mem、autovacuum_max_workers都要根据实际硬件和存储介质调整，不能直接套默认值上生产。

风哥希望读者学完本套风哥教程，能独立用EXPLAIN分析执行计划、能管理和优化统计信息、能诊断并解决常见SQL性能问题。执行计划与统计信息是PostgreSQL性能优化的入门必修课，掌握好这一关，后续的索引优化、查询重写、并行查询等高级主题才有坚实基础。