PostgreSQL 查询优化器：如何选择最优执行计划

Souloss

公告

欢迎来到我的博客！这是一条示例公告

Learn More

标签

Souloss

公告

欢迎来到我的博客！这是一条示例公告

Learn More

标签

Souloss

公告

欢迎来到我的博客！这是一条示例公告

Learn More

标签

1840 字

5 分钟

PostgreSQL 查询优化器：如何选择最优执行计划

2023-01-26

原理

数据库

/

底层原理

前言#

当你向 PostgreSQL 发送一条 SELECT * FROM orders WHERE user_id = 42 时，数据库内部经历了怎样的旅程才返回结果？与 MySQL 不同，PostgreSQL 的优化器采用了截然不同的架构设计，其代价模型和统计信息系统更加精密。本文将深入 PostgreSQL 查询处理的全流程，揭示优化器选择执行计划的核心机制。

PostgreSQL 查询处理全流程#

flowchart TB subgraph 客户端 A[Client Application] end subgraph PostgreSQL Server B[Parser 语法解析] --> C[Analyzer 语义分析] C --> D[Rewriter 查询重写] D --> E[Planner 计划生成] E --> F[Executor 执行器] end subgraph 存储层 G[Shared Buffer Pool] H[Table Files] I[WAL] end A --> B F --> G G --> H G --> I

整个流程可以归纳为五个阶段：

1
SQL 文本
2
  │
3
  ▼
4
┌──────────┐    ┌──────────┐    ┌──────────┐    ┌──────────┐    ┌──────────┐
5
│  Parser   │───▶│ Analyzer │───▶│ Rewriter │───▶│ Planner  │───▶│ Executor │
6
│  语法解析  │    │ 语义分析  │    │ 查询重写  │    │ 计划生成  │    │  执行器   │
7
└──────────┘    └──────────┘    └──────────┘    └──────────┘    └──────────┘
8
  生成 Parse     生成 Query      应用 RULE       生成 Plan       执行 Plan
9
  Tree          Tree            系统             Tree

一、Parser：语法解析#

1.1 词法分析与语法分析#

PostgreSQL 使用手写的递归下降解析器，将 SQL 文本转换为语法树（Parse Tree）。

flowchart LR A["SQL 文本"] --> B[词法分析器 scan.l] B --> C["Token 流"] C --> D[语法分析器 gram.y] D --> E["Parse Tree"]

源码入口在 src/backend/parser/parser.c：

1
// src/backend/parser/parser.c - 简化
2
List *
3
raw_parser(const char *str, RawParseMode mode)
4
{
5
    // 初始化词法分析器
6
    yyscan_t scanner;
7
    core_yyscan_t yyscanner;
8

9
    yyscanner = scanner_init(str, &yyextra.core_yy_extra,
10
                             ScanKeywords, NumScanKeywords);
11

12
    // 调用语法分析器
13
    if (base_yyparse(yyscanner) != 0)
14
        // 语法错误处理...
15

16
    return yyextra.parsetree;
17
}

示例：解析 SELECT name FROM users WHERE id = 1

1
Parse Tree (简化表示):
2
┌─────────────────────────────────────┐
3
│ SelectStmt                          │
4
│ ├── targetList:                     │
5
│ │   └── ResTarget: name             │
6
│ ├── fromClause:                     │
7
│ │   └── RangeVar: users             │
8
│ └── whereClause:                    │
9
│     └── A_Expr: (=)                 │
10
│         ├── ColumnRef: id           │
11
│         └── A_Const: 1              │
12
└─────────────────────────────────────┘

1.2 词法分析的 Token 分类#

1
SQL: SELECT name FROM users WHERE id = 1
2

3
Token 流:
4
┌──────────┬────────────┬──────────────────┐
5
│ Token    │ 类型        │ 说明             │
6
├──────────┼────────────┼──────────────────┤
7
│ SELECT   │ 关键字      │ SQL 命令标识     │
8
│ name     │ 标识符      │ 列名引用         │
9
│ FROM     │ 关键字      │ 子句标识         │
10
│ users    │ 标识符      │ 表名引用         │
11
│ WHERE    │ 关键字      │ 条件子句         │
12
│ id       │ 标识符      │ 列名引用         │
13
│ =        │ 操作符      │ 比较运算         │
14
│ 1        │ 整数常量    │ 字面值           │
15
└──────────┴────────────┴──────────────────┘

二、Analyzer：语义分析#

2.1 分析流程#

语义分析阶段（也称 Parse Analysis）将 Parse Tree 转换为 Query Tree。这个阶段的核心逻辑在 src/backend/parser/analyze.c：

flowchart TB A[Parse Tree] --> B[解析 RangeVar 确定表对象] B --> C[构建 Range Table 范围表] C --> D[解析目标列 确定列类型] D --> E[解析 WHERE 条件 类型检查] E --> F[权限检查] F --> G[Query Tree]

2.2 范围表（Range Table）#

范围表是 PostgreSQL 查询树中的核心数据结构，记录查询涉及的所有关系（表、视图、子查询等）：

1
// src/include/nodes/parsenodes.h - 简化
2
typedef struct RangeTblEntry
3
{
4
    NodeTag     type;
5
    RTEKind     rtekind;       // 关系类型: 表、子查询、函数等
6
    Oid         relid;         // 表的 OID
7
    char       *relalias;      // 别名
8
    Alias      *eref;          // 有效列名列表
9
    bool        inh;           // 是否继承子表
10
    AclMode     requiredPerms; // 所需权限
11
} RangeTblEntry;

Query Tree 核心结构：

1
// src/include/nodes/parsenodes.h - 简化
2
typedef struct Query
3
{
4
    NodeTag     type;
5
    CmdType     commandType;   // SELECT, INSERT, UPDATE, DELETE
6
    List       *rtable;        // 范围表
7
    List       *targetList;    // 目标列
8
    Node       *jointree;      // 连接树 (FROM + WHERE)
9
    List       *groupClause;   // GROUP BY
10
    Node       *havingQual;    // HAVING
11
    List       *sortClause;    // ORDER BY
12
    Node       *limitOffset;   // OFFSET
13
    Node       *limitCount;    // LIMIT
14
} Query;

2.3 语义分析过程#

sequenceDiagram participant P as Parse Tree participant A as Analyzer participant S as System Catalog participant Q as Query Tree P->>A: 输入 SelectStmt A->>S: 查找表 users 的 OID S-->>A: 返回 pg_class 记录 A->>S: 获取表的列信息 S-->>A: 返回 pg_attribute 列表 A->>A: 解析列名 name, id A->>A: 类型检查 (id = 1) A->>S: 检查 SELECT 权限 S-->>A: 权限确认 A->>Q: 生成 Query Tree

系统目录查询：

1
-- 查看表信息 (等价于 Analyzer 的查询)
2
SELECT oid, relname, relnamespace, relkind
3
FROM pg_class WHERE relname = 'users';
4

5
-- 查看列信息
6
SELECT attname, atttypid, attnotnull, atthasdef
7
FROM pg_attribute
8
WHERE attrelid = 'users'::regclass AND attnum > 0;
9

10
-- 查看表权限
11
SELECT has_table_privilege('users', 'SELECT');

三、Rewriter：查询重写#

3.1 RULE 系统#

PostgreSQL 拥有独特的 RULE 系统，可以在查询重写阶段修改查询。重写器入口在 src/backend/rewrite/rewriteHandler.c：

flowchart TB A[Query Tree] --> B{查询目标是否 有 RULE?} B -->|有 INSTEAD 规则| C[替换为规则查询] B -->|有 ALSO 规则| D[追加规则查询] B -->|无规则| E[保持原查询] C --> F[递归处理] D --> F E --> F F --> G[重写后的 Query Tree]

3.2 视图展开#

视图查询重写是最常见的场景。当你查询一个视图时，重写器会将视图引用替换为视图定义：

1
-- 创建视图
2
CREATE VIEW active_users AS
3
SELECT id, name, email FROM users WHERE status = 'active';
4

5
-- 查询视图
6
SELECT name FROM active_users WHERE id > 100;

重写过程：

flowchart TB A["SELECT name FROM active_users WHERE id > 100"] --> B[发现 active_users 是视图] B --> C[获取视图定义] C --> D["替换为: SELECT name FROM (SELECT id, name, email FROM users WHERE status = 'active') WHERE id > 100"] D --> E[合并条件] E --> F["最终: SELECT name FROM users WHERE status = 'active' AND id > 100"]

3.3 重写示例：行安全策略#

1
-- 启用行级安全
2
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
3

4
-- 创建策略：用户只能看自己的订单
5
CREATE POLICY user_orders ON orders
6
    USING (user_id = current_user_id());
7

8
-- 当执行 SELECT * FROM orders 时
9
-- 重写器自动追加条件
10
-- 等价于: SELECT * FROM orders WHERE user_id = current_user_id()

四、Planner：计划生成（逻辑优化）#

4.1 优化器架构#

PostgreSQL 的优化器分为逻辑优化和物理优化两个层次。入口在 src/backend/optimizer/plan/planner.c：

flowchart TB A[重写后的 Query Tree] --> B[预处理] B --> C[子链接处理] C --> D[表达式简化] D --> E[连接预处理] E --> F[路径生成] F --> G[代价计算] G --> H[选择最优路径] H --> I[生成 Plan Tree]

1
// src/backend/optimizer/plan/planner.c - 简化
2
PlannedStmt *
3
planner(Query *parse, const char *query_string,
4
        int cursorOptions, ParamListInfo boundParams)
5
{
6
    PlannedStmt *result;
7

8
    // 核心规划入口
9
    result = standard_planner(parse, query_string,
10
                              cursorOptions, boundParams);
11
    return result;
12
}

4.2 子链接提升（SubLink Pullup）#

PostgreSQL 会尝试将子查询（SubLink）提升为半连接（Semi Join），以便优化器能选择更优的连接算法：

1
-- 原始查询
2
SELECT * FROM orders
3
WHERE user_id IN (SELECT id FROM users WHERE status = 'active');
4

5
-- 提升为 Semi Join
6
SELECT orders.*
7
FROM orders SEMI JOIN users
8
     ON orders.user_id = users.id AND users.status = 'active';

flowchart TB subgraph 提升前 A1[Seq Scan on orders] --> A2[Filter: SubLink] A2 --> A3[Seq Scan on users] end subgraph 提升后 B1[Hash Semi Join] B1 --> B2[Seq Scan on orders] B1 --> B3[Hash on users.id] B3 --> B4[Seq Scan on users] end

4.3 谓词下推（Predicate Pushdown）#

将过滤条件尽可能下推到扫描节点，减少上层处理的数据量：

1
SELECT o.*, u.name
2
FROM orders o
3
JOIN users u ON o.user_id = u.id
4
WHERE u.status = 'active' AND o.amount > 100;

优化过程：

1
优化前 (逻辑表示):
2
┌─────────────────────────────┐
3
│ Filter: u.status='active'   │
4
│   AND o.amount > 100        │
5
│  ┌────────────────────────┐ │
6
│  │ Join ON o.user_id=u.id │ │
7
│  │  ├── Seq Scan: orders  │ │
8
│  │  └── Seq Scan: users   │ │
9
│  └────────────────────────┘ │
10
└─────────────────────────────┘
11

12
优化后 (谓词下推):
13
┌─────────────────────────────┐
14
│ Join ON o.user_id = u.id    │
15
│  ├── Filter: amount > 100   │
16
│  │   └── Seq Scan: orders   │
17
│  └── Filter: status='active'│
18
│      └── Seq Scan: users    │
19
└─────────────────────────────┘

4.4 连接消除（Join Elimination）#

当连接不会影响结果时，优化器会消除多余的连接：

1
-- orders 表有外键约束: user_id REFERENCES users(id)
2
-- 如果查询不使用 users 的任何列
3
SELECT o.*
4
FROM orders o
5
JOIN users u ON o.user_id = u.id
6
WHERE o.amount > 1000;
7

8
-- 优化器检测到:
9
-- 1. users 的列未被使用
10
-- 2. 外键保证 user_id 一定存在
11
-- 消除连接:
12
SELECT * FROM orders WHERE amount > 1000;

4.5 DISTINCT 消除#

当优化器能证明结果已经唯一时，会移除 DISTINCT：

1
-- 主键列 DISTINCT 无意义
2
SELECT DISTINCT id, name FROM users;
3

4
-- 等价于
5
SELECT id, name FROM users;

五、Planner：计划生成（物理优化）#

5.1 代价模型#

PostgreSQL 的代价模型基于磁盘 I/O 和 CPU 消耗。代价单位不是时间，而是相对权重。核心实现在 src/backend/optimizer/path/costsize.c：

1
总代价 = 启动代价 + 运行代价
2

3
启动代价: 获取第一行之前的代价
4
运行代价: 获取所有剩余行的代价
5
总代价:   启动代价 + 运行代价

代价参数：

1
-- 查看代价参数
2
SHOW seq_page_cost;        -- 顺序读一个页的代价 (默认 1.0)
3
SHOW random_page_cost;     -- 随机读一个页的代价 (默认 4.0)
4
SHOW cpu_tuple_cost;       -- 处理一行的 CPU 代价 (默认 0.01)
5
SHOW cpu_index_tuple_cost; -- 处理一个索引项的代价 (默认 0.005)
6
SHOW cpu_operator_cost;    -- 执行一个操作符的代价 (默认 0.0025)
7
SHOW effective_cache_size; -- 预期可用的操作系统缓存 (默认 4GB)

代价计算示例：

1
顺序扫描 10000 行的表（1000 页）:
2

3
startup_cost = 0
4
run_cost = seq_page_cost * pages + cpu_tuple_cost * tuples
5
         = 1.0 * 1000 + 0.01 * 10000
6
         = 1000 + 100
7
         = 1100
8

9
索引扫描选择 10 行（B+树深度 3）:
10

11
startup_cost = 随机 I/O 读树节点
12
             = random_page_cost * tree_height
13
             = 4.0 * 3
14
             = 12
15

16
run_cost = random_page_cost * data_pages + cpu_tuple_cost * tuples
17
         = 4.0 * 10 + 0.01 * 10
18
         = 40 + 0.1
19
         = 40.1
20

21
total_cost = 12 + 40.1 = 52.1

5.2 统计信息#

PostgreSQL 依靠 pg_statistic 系统表中的统计信息来估算选择率和行数。统计信息由 ANALYZE 命令收集：

1
-- 手动收集统计信息
2
ANALYZE users;
3

4
-- 查看统计信息
5
SELECT attname, n_distinct, null_frac,
6
       avg_width, most_common_vals, most_common_freqs,
7
       histogram_bounds, correlation
8
FROM pg_stats
9
WHERE tablename = 'users';

统计信息字段含义：

1
┌───────────────────┬────────────────────────────────────────────┐
2
│ 字段               │ 说明                                       │
3
├───────────────────┼────────────────────────────────────────────┤
4
│ n_distinct         │ 不重复值的估计数量                          │
5
│ null_frac          │ NULL 值的比例                              │
6
│ avg_width          │ 列的平均字节宽度                            │
7
│ most_common_vals   │ 最常见值列表 (MCV)                         │
8
│ most_common_freqs  │ 最常见值的频率列表                          │
9
│ histogram_bounds   │ 等深直方图的边界值                          │
10
│ correlation        │ 物理顺序与逻辑顺序的相关性                  │
11
└───────────────────┴────────────────────────────────────────────┘

统计信息如何影响代价：

flowchart TB A["WHERE status = 'active'"] --> B[查找 MCV 列表] B --> C{status 在 MCV 中?} C -->|是| D[使用 most_common_freqs 选择率 = 0.7] C -->|否| E[使用直方图估算 选择率 = 1/ndistinct] D --> F[估算行数 = 总行数 × 选择率] E --> F

correlation 的作用：

1
correlation 接近 1.0: 物理存储顺序与索引顺序一致
2
  → 索引扫描的 I/O 更顺序化
3
  → 实际 random_page_cost 更低
4

5
correlation 接近 0.0: 物理存储随机
6
  → 索引扫描需要大量随机 I/O
7
  → 可能不如顺序扫描
8

9
correlation 接近 -1.0: 物理存储与索引顺序相反
10
  → 类似接近 0 的情况

5.3 扫描方式选择#

PostgreSQL 支持多种扫描方式，优化器根据统计信息和代价模型选择最优方式：

flowchart TB A[选择扫描方式] --> B{有可用索引?} B -->|否| C[Sequential Scan 顺序扫描] B -->|是| D{选择率高?} D -->|高返回大部分行| C D -->|低返回少量行| E{索引条件?} E -->|等值/范围| F[Index Scan 索引扫描] E -->|多个条件组合| G[Bitmap Scan 位图扫描] E -->|TID 条件| H[TID Scan TID 扫描]

顺序扫描（Sequential Scan）#

最简单的扫描方式，逐页读取整个表：

1
EXPLAIN SELECT * FROM users;
2

3
-- 结果:
4
-- Seq Scan on users  (cost=0.00..15.00 rows=1000 width=68)

代价计算逻辑（cost_seqscan）：

1
// 简化的代价计算
2
void cost_seqscan(Path *path, PlannerInfo *root,
3
                  RelOptInfo *baserel)
4
{
5
    double pages = baserel->pages;
6
    double tuples = baserel->tuples;
7

8
    // I/O 代价: 读所有页
9
    run_cost += seq_page_cost * pages;
10

11
    // CPU 代价: 处理每一行
12
    run_cost += cpu_tuple_cost * tuples;
13
}

索引扫描（Index Scan）#

通过 B+ 树索引查找数据，适合返回少量行的查询：

1
-- 假设 users.id 有主键索引
2
EXPLAIN SELECT * FROM users WHERE id = 42;
3

4
-- 结果:
5
-- Index Scan using users_pkey on users
6
--   (cost=0.28..8.29 rows=1 width=68)
7
--   Index Cond: (id = 42)

1
B+ 树索引查找过程:
2
┌─────────────┐
3
│ Root Page    │  Level 2
4
│ [10|20|30]   │
5
└──────┬───────┘
6
       │
7
  ┌────┼────────────┐
8
  ▼    ▼             ▼
9
┌────┐ ┌────┐      ┌────┐    Level 1
10
│<10 │ │10-20│     │20-30│   (Internal)
11
└──┬─┘ └──┬─┘      └──┬──┘
12
   │      │           │
13
   ▼      ▼           ▼
14
[Leaf]  [Leaf]      [Leaf]    Level 0
15
指向    指向         指向       (Leaf)
16
CTID    CTID        CTID
17
→ 表行   → 表行      → 表行

位图扫描（Bitmap Scan）#

当查询条件涉及多个索引或需要返回较多行时，PostgreSQL 使用位图扫描：

1
-- 假设有 idx_name(name) 和 idx_age(age) 两个索引
2
EXPLAIN SELECT * FROM users
3
WHERE name = 'Alice' AND age > 25;
4

5
-- 结果:
6
-- Bitmap Heap Scan on users
7
--   (cost=8.50..25.30 rows=5 width=68)
8
--   Recheck Cond: (name = 'Alice' AND age > 25)
9
--   -> BitmapAnd
10
--        -> Bitmap Index Scan on idx_name
11
--             Index Cond: (name = 'Alice')
12
--        -> Bitmap Index Scan on idx_age
13
--             Index Cond: (age > 25)

flowchart TB subgraph "Bitmap Scan 流程" A[索引扫描 idx_name] --> C["Bitmap AND"] B[索引扫描 idx_age] --> C C --> D[Bitmap Heap Scan] D --> E[按物理顺序 访问数据页] end

位图扫描 vs 索引扫描：

1
索引扫描: 适合返回极少行 (< 表的 1-5%)
2
  每找到一行就回表访问
3

4
位图扫描: 适合返回较多行 (5-20%)
5
  先构建位图,再按物理顺序批量回表
6
  减少随机 I/O
7

8
顺序扫描: 适合返回大量行 (> 20%)
9
  直接全表扫描更高效

六、连接算法#

6.1 Nested Loop Join#

最基本的连接算法。对外表的每一行，扫描内表寻找匹配：

flowchart TB A[遍历外表每一行] --> B{外表当前行} B --> C[扫描内表] C --> D{匹配连接条件?} D -->|是| E[输出连接结果] D -->|否| F[继续扫描内表] F --> C E --> G{内表扫描完?} G -->|是| H{外表还有行?} H -->|是| B H -->|否| I[连接完成] G -->|否| C

代价公式：

1
Nested Loop 代价:
2
  startup_cost = outer_startup_cost
3
  run_cost = outer_total_cost +
4
             inner_cost_per_row × outer_rows
5

6
如果内表有索引:
7
  inner_cost_per_row = 索引查找代价 (较低)
8

9
如果内表无索引:
10
  inner_cost_per_row = 内表全表扫描代价 (极高)

适用场景：外表小，内表有索引。

6.2 Merge Join#

两个表按连接键排序后进行归并连接：

sequenceDiagram participant O as 外表 (已排序) participant I as 内表 (已排序) participant R as 结果 Note over O,I: 两个游标同时前进 O->>O: 读取当前行 outer_row I->>I: 读取当前行 inner_row loop 直到某一表读完 alt outer_key < inner_key O->>O: 前进到下一行 else outer_key > inner_key I->>I: 前进到下一行 else outer_key == inner_key R->>R: 输出匹配行 I->>I: 前进到下一行 end end

1
Merge Join 示例 (连接键 = id):
2

3
外表 (按 id 排序):     内表 (按 id 排序):
4
  id | name              id | order_no
5
 ---+------             ---+---------
6
  1  | Alice              1  | ORD-001
7
  3  | Bob                2  | ORD-002
8
  5  | Carol              3  | ORD-003
9
                           5  | ORD-004
10

11
归并过程:
12
  外表 id=1, 内表 id=1 → 匹配 → 输出
13
  外表 id=3, 内表 id=2 → 内表前进
14
  外表 id=3, 内表 id=3 → 匹配 → 输出
15
  外表 id=5, 内表 id=5 → 匹配 → 输出

适用场景：数据已排序或需要排序后连接。

6.3 Hash Join#

PostgreSQL 中最常用的等值连接算法，尤其适合大表连接：

flowchart TB subgraph "阶段 1: Build" A[扫描内表 (较小的表)] --> B[计算 Hash 值] B --> C[构建 Hash Table] end subgraph "阶段 2: Probe" D[扫描外表 (较大的表)] --> E[计算 Hash 值] E --> F[在 Hash Table 中查找] F --> G{匹配?} G -->|是| H[输出连接结果] G -->|否| I[跳过] end C --> F

代价公式：

1
Hash Join 代价:
2
  startup_cost = inner_total_cost + hash_build_cost
3
  run_cost = (outer_total_cost - outer_startup_cost) +
4
             cpu_hash_cost × outer_rows +
5
             cpu_tuple_cost × result_rows
6

7
Hash Table 构建代价:
8
  hash_build_cost = cpu_operator_cost × inner_rows

1
EXPLAIN SELECT * FROM orders o
2
JOIN users u ON o.user_id = u.id;
3

4
-- 结果:
5
-- Hash Join
6
--   Hash Cond: (o.user_id = u.id)
7
--   -> Seq Scan on orders
8
--   -> Hash
9
--        -> Seq Scan on users

三种连接算法对比：

1
┌───────────────┬──────────────────┬───────────────────┬──────────────────┐
2
│ 算法           │ 适用场景          │ 时间复杂度         │ 内存需求         │
3
├───────────────┼──────────────────┼───────────────────┼──────────────────┤
4
│ Nested Loop   │ 小表 + 索引内表   │ O(M × log N)      │ 低               │
5
│ Merge Join    │ 已排序数据        │ O(M + N)          │ 中 (排序)        │
6
│ Hash Join     │ 大表等值连接      │ O(M + N)          │ 高 (Hash Table)  │
7
└───────────────┴──────────────────┴───────────────────┴──────────────────┘
8

9
M = 外表行数, N = 内表行数

七、并行查询#

7.1 并行查询架构#

PostgreSQL 从 9.6 开始支持并行查询，采用多进程模型（而非多线程）：

flowchart TB A[Leader 进程] --> B[Gather 节点] B --> C[Worker 1] B --> D[Worker 2] B --> E[Worker 3] C --> F[Parallel Seq Scan 处理块 1-33] D --> G[Parallel Seq Scan 处理块 34-66] E --> H[Parallel Seq Scan 处理块 67-100]

7.2 并行扫描#

Parallel Sequential Scan：将表的页面块分配给多个 Worker 并行扫描：

1
SET max_parallel_workers_per_gather = 4;
2

3
EXPLAIN SELECT count(*) FROM orders;
4

5
-- 结果:
6
-- Finalize Aggregate
7
--   -> Gather
8
--        Workers Planned: 3
9
--        -> Partial Aggregate
10
--             -> Parallel Seq Scan on orders

并行扫描的协调机制：

1
// src/backend/access/transam/parallel.c - 简化
2
// Worker 通过共享内存协调扫描进度
3
typedef struct ParallelBlockTableScanDescData
4
{
5
    BlockNumber phs_nblocks;       // 表的总块数
6
    pg_atomic_uint64 phs_startblock; // 起始块
7
    pg_atomic_uint64 phs_nallocated; // 已分配的块数
8
} ParallelBlockTableScanDescData;
9

10
// 每个 Worker 获取下一个要扫描的块
11
BlockNumber
12
parallel_seqscan_get_next(ParallelBlockTableScanDesc pscan)
13
{
14
    // 原子地递增已分配块数
15
    return pg_atomic_fetch_add_u64(&pscan->phs_nallocated, 1);
16
}

7.3 并行连接#

1
EXPLAIN SELECT * FROM orders o
2
JOIN users u ON o.user_id = u.id
3
WHERE o.amount > 1000;
4

5
-- 结果:
6
-- Gather Merge
7
--   Workers Planned: 2
8
--   -> Merge Join
9
--        Merge Cond: (o.user_id = u.id)
10
--        -> Sort
11
--             -> Parallel Seq Scan on orders o
12
--                  Filter: (amount > 1000)
13
--        -> Index Scan using users_pkey on users u

flowchart TB A[Gather Merge] --> B[Worker 1: Merge Join] A --> C[Worker 2: Merge Join] A --> D[Worker 3: Merge Join] B --> E[Parallel Scan orders 块 1-33] C --> F[Parallel Scan orders 块 34-66] D --> G[Parallel Scan orders 块 67-100] E --> H[Sort] F --> I[Sort] G --> J[Sort] H --> K[Merge Join with Index Scan users] I --> K J --> K

7.4 并行相关参数#

1
-- 最大并行 Worker 数
2
SHOW max_parallel_workers;                -- 默认 8
3

4
-- 每个 Gather 节点的最大 Worker 数
5
SHOW max_parallel_workers_per_gather;     -- 默认 2
6

7
-- 并行扫描的最小表大小 (8MB)
8
SHOW min_parallel_table_scan_size;        -- 默认 8MB
9

10
-- 是否启用并行 Append
11
SHOW enable_parallel_append;              -- 默认 on
12

13
-- 并行 Worker 的代价因子
14
SHOW parallel_tuple_cost;                 -- 默认 0.1
15
SHOW parallel_setup_cost;                 -- 默认 1000.0

八、执行计划解读#

8.1 EXPLAIN 输出分析#

1
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
2
SELECT u.name, count(*) as order_count
3
FROM users u
4
JOIN orders o ON u.id = o.user_id
5
WHERE u.status = 'active'
6
GROUP BY u.name
7
ORDER BY order_count DESC
8
LIMIT 10;

1
Limit  (cost=35.80..35.82 rows=10 width=24)
2
       (actual time=0.152..0.155 rows=10 loops=1)
3
  Buffers: shared hit=15
4
  -> Sort  (cost=35.80..35.85 rows=20 width=24)
5
          (actual time=0.151..0.153 rows=10 loops=1)
6
        Sort Key: count(*) DESC
7
        Sort Method: top-N heapsort  Memory: 25kB
8
        Buffers: shared hit=15
9
        -> HashAggregate  (cost=35.50..35.70 rows=20 width=24)
10
                (actual time=0.128..0.135 rows=20 loops=1)
11
              Group Key: u.name
12
              Batches: 1  Memory Usage: 24kB
13
              Buffers: shared hit=15
14
              -> Hash Join  (cost=10.25..32.50 rows=200 width=16)
15
                     (actual time=0.042..0.090 rows=200 loops=1)
16
                    Hash Cond: (o.user_id = u.id)
17
                    Buffers: shared hit=15
18
                    -> Seq Scan on orders o
19
                           (cost=0.00..16.00 rows=1000 width=8)
20
                           (actual time=0.005..0.025 rows=1000 loops=1)
21
                          Buffers: shared hit=10
22
                    -> Hash  (cost=10.12..10.12 rows=10 width=16)
23
                             (actual time=0.028..0.028 rows=10 loops=1)
24
                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
25
                          Buffers: shared hit=5
26
                          -> Seq Scan on users u
27
                                 (cost=0.00..10.12 rows=10 width=16)
28
                                 (actual time=0.005..0.023 rows=10 loops=1)
29
                                Filter: (status = 'active'::text)
30
                                Rows Removed by Filter: 90
31
                                Buffers: shared hit=5
32
Planning:
33
  Buffers: shared hit=12
34
Execution Time: 0.210 ms

8.2 EXPLAIN 关键字段解读#

1
┌─────────────────┬────────────────────────────────────────────────┐
2
│ 字段             │ 含义                                           │
3
├─────────────────┼────────────────────────────────────────────────┤
4
│ cost=X..Y       │ 启动代价..总代价 (估算)                        │
5
│ rows=N          │ 估算返回行数                                   │
6
│ width=W         │ 估算每行字节数                                 │
7
│ actual time=X..Y│ 实际启动时间..实际完成时间 (毫秒)              │
8
│ rows=N          │ 实际返回行数                                   │
9
│ loops=L         │ 执行循环次数                                   │
10
│ Buffers         │ 缓冲区访问统计 (shared hit/read/dirtied)       │
11
│ Sort Method     │ 排序算法和内存使用                             │
12
│ Hash Cond       │ Hash Join 的连接条件                           │
13
│ Filter          │ 过滤条件                                       │
14
│ Rows Removed    │ 被过滤掉的行数                                 │
15
└─────────────────┴────────────────────────────────────────────────┘

8.3 常见执行计划模式#

索引查找模式：

1
EXPLAIN SELECT * FROM users WHERE id = 42;
2
-- Index Scan using users_pkey on users
3
--   Index Cond: (id = 42)

多表连接模式：

1
EXPLAIN SELECT * FROM orders o
2
JOIN users u ON o.user_id = u.id
3
JOIN products p ON o.product_id = p.id;
4

5
-- Hash Join on (o.product_id = p.id)
6
--   -> Hash Join on (o.user_id = u.id)
7
--        -> Seq Scan on orders
8
--        -> Hash -> Seq Scan on users
9
--   -> Hash -> Seq Scan on products

子查询优化模式：

1
EXPLAIN SELECT * FROM orders
2
WHERE user_id IN (SELECT id FROM users WHERE status = 'active');
3

4
-- Hash Semi Join
5
--   Hash Cond: (orders.user_id = users.id)
6
--   -> Seq Scan on orders
7
--   -> Hash -> Seq Scan on users
8
--        Filter: (status = 'active')

九、优化器实战技巧#

9.1 统计信息调优#

1
-- 增大统计目标以提高估算精度
2
ALTER TABLE users ALTER COLUMN status SET STATISTICS 500;
3

4
-- 对特定列收集扩展统计信息
5
CREATE STATISTICS users_stats (ndistinct, dependencies, mcv)
6
ON status, city FROM users;
7
ANALYZE users;
8

9
-- 查看扩展统计信息
10
SELECT * FROM pg_statistic_ext
11
WHERE stxname = 'users_stats';

扩展统计信息的作用：

1
┌──────────────────────┬──────────────────────────────────────────┐
2
│ 类型                  │ 解决的问题                               │
3
├──────────────────────┼──────────────────────────────────────────┤
4
│ ndistinct             │ 多列组合的唯一值估算                     │
5
│ dependencies          │ 列之间的函数依赖关系                     │
6
│ mcv (最常见值)        │ 多列组合的频率分布                       │
7
└──────────────────────┴──────────────────────────────────────────┘
8

9
示例:
10
  WHERE city = 'Beijing' AND status = 'active'
11

12
  无扩展统计: 选择率 = P(city) × P(status) = 0.1 × 0.7 = 0.07
13
  有 dependencies: 发现 status 依赖 city
14
  实际选择率 = P(city='Beijing' AND status='active') = 0.09

9.2 强制/禁用特定扫描方式#

1
-- 调试时临时禁用某种扫描方式
2
SET enable_seqscan = off;      -- 禁用顺序扫描
3
SET enable_indexscan = off;    -- 禁用索引扫描
4
SET enable_bitmapscan = off;   -- 禁用位图扫描
5
SET enable_hashjoin = off;     -- 禁用 Hash Join
6
SET enable_mergejoin = off;    -- 禁用 Merge Join
7
SET enable_nestloop = off;     -- 禁用 Nested Loop
8

9
-- 查看优化器决策过程
10
SET enable_seqscan = off;
11
EXPLAIN SELECT * FROM users WHERE status = 'active';
12
-- 现在会优先选择索引扫描 (如果有的话)

9.3 pg_hint_plan 扩展#

1
-- 使用提示控制优化器
2
/*+ HashJoin(users orders) */
3
EXPLAIN SELECT * FROM users u
4
JOIN orders o ON u.id = o.user_id;
5

6
/*+ SeqScan(users) IndexScan(orders idx_user_id) */
7
EXPLAIN SELECT * FROM users u
8
JOIN orders o ON u.id = o.user_id;

总结#

PostgreSQL 查询处理完整流程#

flowchart TB A[SQL 文本] --> B[Parser 语法解析] B --> C[Analyzer 语义分析] C --> D[Rewriter 查询重写] D --> E[Planner] subgraph "Planner (计划生成)" E1[逻辑优化 子链接提升/谓词下推] --> E2[物理优化 代价计算/路径选择] E2 --> E3[扫描方式选择 Seq/Index/Bitmap] E3 --> E4[连接算法选择 NL/Merge/Hash] E4 --> E5[并行度决策] E5 --> E6[生成 Plan Tree] end E --> E1 E6 --> F[Executor 执行器] subgraph "Executor" F1[Seq Scan] --> F2[Index Scan] F3[Hash Join] --> F4[Sort] F5[Aggregate] --> F6[Limit] end F --> G[返回结果]

核心要点#

Parser：手写递归下降解析器，生成 Parse Tree
Analyzer：语义分析，结合系统目录生成 Query Tree
Rewriter：RULE 系统和视图展开
Planner（逻辑优化）：子链接提升、谓词下推、连接消除、DISTINCT 消除
Planner（物理优化）：基于代价模型和统计信息选择扫描方式与连接算法
并行查询：Gather 模型，多进程并行扫描和连接

常见问题#

Q1：PostgreSQL 和 MySQL 的优化器有什么区别？#

PostgreSQL 的优化器基于代价（CBO），统计信息更丰富（支持扩展统计信息如 ndistinct、dependencies、MCV）。MySQL 的优化器也是 CBO，但统计信息相对简单。PostgreSQL 支持 RULE 系统做查询重写，MySQL 没有等价机制。并行查询方面，PostgreSQL 使用多进程模型，MySQL 使用多线程。