PostgreSQL 18 从新手到大师：实战指南 - 3.5 查询计划分析

PostgreSQL查询计划解析指南本文系统介绍了PostgreSQL查询计划的核心概念和使用方法。主要内容包括：查询计划的重要性及其对性能的关键影响查询优化器的工作原理和成本模型 EXPLAIN命令的多种用法及输出格式解析查询计划树形结构的组成和常见节点类型通过示例详细演示查询计划的分析步骤文章重点讲解了如何通过查询计划识别性能瓶颈，并提供了实用的EXPLAIN命令选项组合，帮助开发

火马编程

906人浏览 · 2026-01-07 13:31:57

火马编程 · 2026-01-07 13:31:57 发布

一、查询计划概述

查询计划是PostgreSQL查询优化器为执行SQL查询生成的执行方案。它描述了PostgreSQL将如何执行查询，包括使用哪些索引、如何连接表、如何排序数据等。

1.1 查询计划的重要性

查询计划对查询性能有着至关重要的影响：

决定查询速度：好的查询计划可以让查询在毫秒级完成，而差的查询计划可能需要几分钟甚至几小时
影响系统资源使用：好的查询计划可以减少CPU、内存和I/O的使用
帮助识别性能瓶颈：通过分析查询计划，可以识别查询中的性能瓶颈
指导查询优化：查询计划可以指导开发人员优化查询语句和表设计

1.2 查询优化器的作用

查询优化器（Query Optimizer）是PostgreSQL的核心组件之一，负责生成最优的查询计划。它的主要职责包括：

解析查询：将SQL查询解析为语法树
语义分析：检查查询的语义正确性
生成候选计划：生成多个可能的查询计划
评估计划成本：使用统计信息评估每个计划的成本
选择最优计划：选择成本最低的查询计划

1.3 查询计划的成本模型

PostgreSQL使用基于成本的优化方法，成本模型考虑以下因素：

I/O成本：读取数据页的成本
CPU成本：处理数据的成本
内存使用：查询所需的内存量
网络成本：数据传输的成本

成本模型使用相对成本单位，默认情况下：

顺序读取一个数据页的成本为1.0
随机读取一个数据页的成本为4.0
处理一个数据行的成本为0.01

二、查看查询计划

PostgreSQL提供了多种方式查看查询计划，最常用的是EXPLAIN命令。

2.1 EXPLAIN命令的基本用法

-- 基本EXPLAIN命令，只显示计划和估计成本
EXPLAIN SELECT * FROM employees WHERE department = 'IT';

-- EXPLAIN ANALYZE，显示实际执行时间和行数
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'IT';

-- EXPLAIN (ANALYZE, BUFFERS)，显示缓冲区使用情况
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM employees WHERE department = 'IT';

-- EXPLAIN (ANALYZE, VERBOSE)，显示详细的计划信息
EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM employees WHERE department = 'IT';

-- EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)，以JSON格式输出
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) SELECT * FROM employees WHERE department = 'IT';

2.2 EXPLAIN命令的输出格式

EXPLAIN命令支持以下输出格式：

文本格式：默认格式，适合直接查看
JSON格式：适合程序处理
XML格式：适合程序处理
YAML格式：适合程序处理

2.3 常用的EXPLAIN选项

选项	描述
`ANALYZE`	执行查询并显示实际执行时间和行数
`BUFFERS`	显示缓冲区使用情况
`COSTS`	显示估计成本
`VERBOSE`	显示详细信息，包括列名和表别名
`FORMAT`	指定输出格式（TEXT、JSON、XML、YAML）
`SETTINGS`	显示查询使用的配置参数
`SUMMARY`	显示查询的摘要信息，包括总执行时间

三、查询计划的组成部分

3.1 查询计划的基本结构

查询计划是一个树形结构，每个节点代表一个操作。最底层的节点是扫描操作，上层节点是连接、聚合、排序等操作。

Gather  (cost=1000.00..1120.00 rows=1000 width=100)
  Workers Planned: 2
  ->  Parallel Seq Scan on employees  (cost=0.00..120.00 rows=500 width=100)
        Filter: (department = 'IT'::text)

3.2 查询计划中的关键信息

节点类型：操作的类型，如Seq Scan、Index Scan、Hash Join等
成本信息：
- cost=startup_cost..total_cost：估计的启动成本和总成本
- rows=estimated_rows：估计返回的行数
- width=estimated_width：估计每行的宽度（字节）
实际执行信息（使用ANALYZE时）：
- actual time=startup_time..total_time：实际启动时间和总执行时间
- rows=actual_rows：实际返回的行数
- loops=number_of_loops：操作执行的次数
过滤条件：Filter: (condition)，应用于扫描的过滤条件
索引信息：使用的索引名称和索引条件
连接信息：连接类型、连接条件和连接顺序

3.3 常见的查询计划节点

3.3.1 扫描节点

Seq Scan：顺序扫描整个表
Index Scan：索引扫描，先扫描索引，再回表获取数据
Index Only Scan：仅索引扫描，不需要回表获取数据
Bitmap Heap Scan：位图堆扫描，结合位图索引使用
Bitmap Index Scan：位图索引扫描，用于生成位图
Tid Scan：TID扫描，根据行标识符直接访问行

3.3.2 连接节点

Nested Loop Join：嵌套循环连接
Hash Join：哈希连接
Merge Join：合并连接

3.3.3 聚合和排序节点

GroupAggregate：分组聚合
HashAggregate：哈希聚合
Sort：排序操作
Unique：去重操作

3.3.4 其他节点

Limit：限制返回行数
Offset：跳过指定行数
Subquery Scan：子查询扫描
CTE Scan：公共表表达式扫描
Materialize：物化操作，将结果存储在内存中

四、解读查询计划

4.1 示例查询计划分析

EXPLAIN ANALYZE 
SELECT e.name, d.department_name, COUNT(*) as num_employees
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 5000
GROUP BY e.name, d.department_name
ORDER BY num_employees DESC
LIMIT 10;

查询计划：

Limit  (cost=200.00..250.00 rows=10 width=52) (actual time=10.00..15.00 rows=10 loops=1)
  ->  Sort  (cost=200.00..225.00 rows=1000 width=52) (actual time=10.00..12.00 rows=10 loops=1)
        Sort Key: (count(*)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  HashAggregate  (cost=100.00..150.00 rows=1000 width=52) (actual time=5.00..8.00 rows=500 loops=1)
              Group Key: e.name, d.department_name
              Batches: 1  Memory Usage: 4096kB
              ->  Hash Join  (cost=50.00..80.00 rows=2000 width=36) (actual time=2.00..4.00 rows=2000 loops=1)
                    Hash Cond: (e.department_id = d.id)
                    ->  Seq Scan on employees e  (cost=0.00..30.00 rows=2000 width=28) (actual time=0.00..1.00 rows=2000 loops=1)
                          Filter: (salary > 5000)
                          Rows Removed by Filter: 3000
                    ->  Hash  (cost=30.00..30.00 rows=1000 width=16) (actual time=1.00..1.00 rows=1000 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 64kB
                          ->  Seq Scan on departments d  (cost=0.00..30.00 rows=1000 width=16) (actual time=0.00..0.50 rows=1000 loops=1)
Planning Time: 0.50 ms
Execution Time: 15.50 ms

4.2 解读步骤

从顶层到底层解读：
- 顶层是Limit节点，限制返回10行
- 下一层是Sort节点，按num_employees降序排序
- 再下一层是HashAggregate节点，进行分组聚合
- 然后是Hash Join节点，连接employees和departments表
- 最底层是两个Seq Scan节点，分别扫描employees和departments表
分析成本和实际执行时间：
- 估计总成本：250.00
- 实际总执行时间：15.50 ms
- 估计行数：1000，实际行数：10
分析扫描方式：
- 两个表都使用了顺序扫描（Seq Scan）
- employees表扫描了5000行，过滤掉了3000行，实际返回2000行
分析连接方式：
- 使用了Hash Join，适合两个表都比较大的情况
- 先扫描departments表并构建哈希表，然后扫描employees表并进行哈希连接
分析聚合方式：
- 使用了HashAggregate，适合分组聚合
- 内存使用：4096kB
分析排序方式：
- 使用了top-N heapsort，适合只需要前N行的情况
- 内存使用：25kB

4.3 识别性能瓶颈

从上面的查询计划中，可以识别出以下性能瓶颈：

顺序扫描：两个表都使用了顺序扫描，如果表很大，可能会很慢
过滤掉大量行：employees表过滤掉了3000行，只返回2000行
哈希连接的内存使用：虽然当前内存使用合理，但如果表很大，可能会溢出到磁盘

五、查询计划优化技巧

5.1 优化扫描方式

添加合适的索引：

-- 为department和salary列添加索引
CREATE INDEX idx_employees_department_salary ON employees (department, salary);

使用覆盖索引：

-- 创建覆盖索引，包含查询所需的所有列
CREATE INDEX idx_employees_department_salary_name ON employees (department, salary) INCLUDE (name);

调整表的存储参数：

-- 设置合适的填充因子
ALTER TABLE employees SET (fillfactor = 80);

5.2 优化连接方式

选择合适的连接顺序：
- 小表驱动大表
- 确保连接列上有索引
使用合适的连接类型：
- 对于小表，使用Nested Loop Join
- 对于大表，使用Hash Join或Merge Join

调整连接相关参数：

-- 调整哈希连接的内存使用
SET work_mem = '64MB';

5.3 优化聚合和排序

优化聚合查询：
- 在分组列上添加索引
- 考虑使用部分聚合
优化排序操作：
- 在排序列上添加索引
- 避免不必要的排序
- 调整排序内存参数：
```
SET work_mem = '64MB';
```

使用物化视图：

-- 创建物化视图
CREATE MATERIALIZED VIEW department_employee_count AS
SELECT department, COUNT(*) as num_employees
FROM employees
GROUP BY department;

-- 查询物化视图
SELECT * FROM department_employee_count;

5.4 优化子查询和CTE

避免 correlated subquery：

-- 不好的做法：correlated subquery
SELECT * FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

-- 好的做法：使用JOIN
SELECT e.* FROM employees e
JOIN (SELECT department, AVG(salary) as avg_salary FROM employees GROUP BY department) d
ON e.department = d.department AND e.salary > d.avg_salary;

使用CTE替代子查询：

-- 使用CTE
WITH dept_avg AS (
  SELECT department, AVG(salary) as avg_salary
  FROM employees
  GROUP BY department
)
SELECT e.* FROM employees e
JOIN dept_avg d ON e.department = d.department AND e.salary > d.avg_salary;

考虑物化CTE：

-- 物化CTE
WITH dept_avg AS MATERIALIZED (
  SELECT department, AVG(salary) as avg_salary
  FROM employees
  GROUP BY department
)
SELECT e.* FROM employees e
JOIN dept_avg d ON e.department = d.department AND e.salary > d.avg_salary;

六、使用pg_stat_statements分析慢查询

pg_stat_statements是PostgreSQL的一个扩展，用于收集和统计SQL查询的执行信息。

6.1 安装和配置pg_stat_statements

-- 安装扩展
CREATE EXTENSION pg_stat_statements;

-- 配置参数
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.max = 10000;
ALTER SYSTEM SET pg_stat_statements.track_utility = on;

-- 重启PostgreSQL使配置生效
-- sudo systemctl restart postgresql-18

6.2 使用pg_stat_statements查询慢查询

-- 查看执行时间最长的10个查询
SELECT 
    queryid,
    query,
    calls,
    total_time,
    mean_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM 
    pg_stat_statements
ORDER BY 
    total_time DESC
LIMIT 10;

-- 查看调用次数最多的10个查询
SELECT 
    queryid,
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM 
    pg_stat_statements
ORDER BY 
    calls DESC
LIMIT 10;

-- 查看平均执行时间最长的10个查询
SELECT 
    queryid,
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM 
    pg_stat_statements
ORDER BY 
    mean_time DESC
LIMIT 10;

-- 重置统计信息
SELECT pg_stat_statements_reset();

6.3 分析pg_stat_statements结果

从pg_stat_statements的结果中，可以分析出以下信息：

执行时间最长的查询：需要重点优化
调用次数最多的查询：即使单次执行时间短，总执行时间可能很长
平均执行时间最长的查询：可能存在性能问题
命中率：shared_blks_hit / (shared_blks_hit + shared_blks_read)，命中率低表示需要更多的I/O

七、查询计划优化实践

7.1 实践案例：优化慢查询

问题：以下查询执行缓慢：

SELECT 
    e.name, 
    d.department_name, 
    SUM(s.amount) as total_sales
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.id
JOIN 
    sales s ON e.id = s.employee_id
WHERE 
    s.sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY 
    e.name, d.department_name
ORDER BY 
    total_sales DESC;

分析步骤：

查看查询计划：
```
EXPLAIN ANALYZE SELECT ...;
```
识别瓶颈：
- 发现sales表使用了顺序扫描
- 连接操作消耗了大量时间
- 排序操作消耗了大量内存
优化措施：
- 为sales表的employee_id和sale_date列添加索引
- 为连接列添加索引
- 调整work_mem参数

实施优化：

-- 为sales表添加索引
CREATE INDEX idx_sales_employee_id_sale_date ON sales (employee_id, sale_date);

-- 调整work_mem参数
SET work_mem = '128MB';

验证优化效果：
- 再次执行EXPLAIN ANALYZE
- 比较优化前后的执行时间和成本
- 确认性能提升

7.2 常见查询优化模式

索引覆盖：使用覆盖索引避免回表
索引下推：将过滤条件下推到索引扫描阶段
哈希聚合：使用哈希聚合替代排序聚合
物化视图：预计算常用的聚合查询
分区表：将大表划分为小表，提高查询速度
并行查询：使用并行查询提高查询速度

八、查询计划的高级特性

8.1 并行查询计划

并行查询计划包含Gather或Gather Merge节点，表示使用了并行查询：

Gather  (cost=1000.00..1120.00 rows=1000 width=100)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on employees  (cost=0.00..120.00 rows=500 width=100)
        Filter: (department = 'IT'::text)

8.2 分区表查询计划

分区表查询计划包含Append节点，表示扫描了多个分区：

Append  (cost=0.00..200.00 rows=1000 width=100)
  ->  Seq Scan on sales_2023_01  (cost=0.00..50.00 rows=250 width=100)
        Filter: (sale_date BETWEEN '2023-01-01' AND '2023-12-31')
  ->  Seq Scan on sales_2023_02  (cost=0.00..50.00 rows=250 width=100)
        Filter: (sale_date BETWEEN '2023-01-01' AND '2023-12-31')
  ->  Seq Scan on sales_2023_03  (cost=0.00..50.00 rows=250 width=100)
        Filter: (sale_date BETWEEN '2023-01-01' AND '2023-12-31')
  ->  Seq Scan on sales_2023_04  (cost=0.00..50.00 rows=250 width=100)
        Filter: (sale_date BETWEEN '2023-01-01' AND '2023-12-31')

8.3 递归查询计划

递归查询计划包含Recursive Union节点，表示使用了递归查询：

Recursive Union  (cost=0.00..1000.00 rows=1000 width=100)
  ->  Seq Scan on employees  (cost=0.00..50.00 rows=10 width=100)
        Filter: (manager_id IS NULL)
  ->  Hash Join  (cost=0.55..90.00 rows=99 width=100)
        Hash Cond: (e.manager_id = recursive_cte.id)
        ->  Seq Scan on employees e  (cost=0.00..50.00 rows=1000 width=100)
        ->  Hash  (cost=0.22..0.22 rows=20 width=4)
              ->  WorkTable Scan on recursive_cte  (cost=0.00..0.22 rows=20 width=4)

九、PostgreSQL vs SQL Server 2019+ vs MySQL 8.0+：查询计划分析对比

在本节中，我们将PostgreSQL 18的查询计划分析与SQL Server 2019+和MySQL 8.0+进行对比，分析它们在查询优化器、查询计划生成和分析工具方面的差异。

9.1 查询优化器对比

特性	PostgreSQL 18	SQL Server 2019+	MySQL 8.0+
优化器类型	基于成本的优化器（CBO）	基于成本的优化器（CBO）	基于成本的优化器（CBO）
统计信息收集	自动收集，可手动触发	自动收集，可手动触发	自动收集，可手动触发
统计信息类型	直方图、高频值、相关性统计	直方图、采样统计、列统计	直方图、索引统计、表统计
并行查询支持	支持，动态并行度调整	支持，自适应并行度	有限支持，最高8个线程
查询计划缓存	支持，计划缓存机制	支持，执行计划缓存	支持，查询缓存（默认关闭）
优化器扩展性	支持扩展，可添加自定义优化规则	有限扩展能力	有限扩展能力

9.2 查询计划生成对比

特性	PostgreSQL 18	SQL Server 2019+	MySQL 8.0+
计划生成方法	动态规划，遗传算法	动态规划，启发式算法	基于成本的贪心算法
计划类型	串行计划、并行计划	串行计划、并行计划、批处理计划	串行计划、有限并行计划
连接算法	Nested Loop、Hash Join、Merge Join	Nested Loop、Hash Join、Merge Join	Nested Loop、Hash Join、Merge Join
扫描算法	Seq Scan、Index Scan、Index Only Scan、Bitmap Scan	多种扫描类型，包括聚集索引扫描、非聚集索引扫描等	Seq Scan、Index Scan、Index Only Scan
聚合算法	GroupAggregate、HashAggregate	多种聚合算法	Group By、Hash Aggregate
排序算法	快速排序、外部排序、top-N heapsort	多种排序算法，包括合并排序等	快速排序、外部排序

9.3 查询计划查看工具对比

特性	PostgreSQL 18	SQL Server 2019+	MySQL 8.0+
命令行工具	EXPLAIN、EXPLAIN ANALYZE	SHOWPLAN、STATISTICS IO/TIME、Actual Execution Plan	EXPLAIN、EXPLAIN ANALYZE
图形化工具	pgAdmin	SQL Server Management Studio (SSMS)	MySQL Workbench
在线工具	explain.depesz.com	无官方在线工具	无官方在线工具
计划格式	TEXT、JSON、XML、YAML	文本、XML、图形化	TEXT、JSON
详细程度	支持不同详细级别	支持不同详细级别	支持基本和详细计划
实际执行信息	支持ANALYZE选项	支持Actual Execution Plan	支持ANALYZE选项

9.4 查询计划分析对比

特性	PostgreSQL 18	SQL Server 2019+	MySQL 8.0+
计划可读性	良好，层次分明	优秀，图形化展示清晰	良好，易于理解
成本估算准确性	良好，基于统计信息	优秀，企业级优化	良好，适合中小规模数据
实际执行与估算对比	支持，ANALYZE选项	支持，Actual Execution Plan	支持，ANALYZE选项
性能瓶颈识别	支持，通过成本和时间分析	优秀，图形化高亮瓶颈	支持，通过成本和时间分析
优化建议	有限，需手动分析	优秀，内置优化建议	有限，需手动分析
大规模数据支持	优秀，适合TB级数据	优秀，适合PB级数据	良好，适合GB级数据

9.5 查询优化工具对比

特性	PostgreSQL 18	SQL Server 2019+	MySQL 8.0+
慢查询分析	pg_stat_statements	Query Store、Extended Events	Slow Query Log、Performance Schema
自动计划捕获	auto_explain	Query Store	Slow Query Log
计划比较	需手动比较	Query Store支持计划比较	需手动比较
索引建议	有限，需手动分析	优秀，Missing Index建议	有限，EXPLAIN输出提示
性能监控	pg_stat_activity、pg_stat_progress_*	Dynamic Management Views (DMVs)	Performance Schema、Sys Schema
可视化工具	pgAdmin、pgBadger	SSMS、Azure Data Studio	MySQL Workbench、Percona Monitoring and Management

十、查询计划分析工具

10.1 pgAdmin

pgAdmin提供了可视化的查询计划分析工具，可以：

以图形方式显示查询计划
显示每个节点的成本和执行时间
提供优化建议

10.2 explain.depesz.com

explain.depesz.com是一个在线工具，可以：

格式化查询计划
高亮显示性能瓶颈
提供优化建议
比较不同查询计划

10.3 pgBadger

pgBadger是一个日志分析工具，可以：

分析PostgreSQL日志
生成查询性能报告
识别慢查询
提供优化建议

10.4 auto_explain

auto_explain是PostgreSQL的一个扩展，可以：

自动记录慢查询的查询计划
配置记录条件（执行时间、行数等）
帮助识别生产环境中的慢查询

-- 安装扩展
CREATE EXTENSION auto_explain;

-- 配置auto_explain
ALTER SYSTEM SET auto_explain.log_min_duration = '100ms';
ALTER SYSTEM SET auto_explain.log_analyze = on;
ALTER SYSTEM SET auto_explain.log_buffers = on;
ALTER SYSTEM SET auto_explain.log_format = 'json';
ALTER SYSTEM SET auto_explain.log_nested_statements = on;
ALTER SYSTEM SET auto_explain.log_timing = on;
ALTER SYSTEM SET auto_explain.sample_rate = 1.0;

-- 重启PostgreSQL使配置生效
-- sudo systemctl restart postgresql-18

十一、总结

查询计划分析是PostgreSQL性能优化的重要组成部分。通过分析查询计划，可以识别性能瓶颈，采取相应的优化措施，提高查询性能。

通过本章的学习，你应该已经掌握了：

查询计划的基本概念和组成部分
如何使用EXPLAIN命令查看和分析查询计划
如何解读查询计划中的关键信息
如何识别查询计划中的性能瓶颈
查询计划优化的基本技巧
如何使用pg_stat_statements和其他工具分析慢查询
查询优化器的配置参数

在实际应用中，查询计划分析是一个迭代过程，需要不断尝试和调整，才能找到最优的查询计划。通过掌握查询计划分析的技巧，可以显著提高PostgreSQL数据库的查询性能。

十二、思考与练习

什么是查询计划？查询计划的重要性是什么？
如何查看查询计划？EXPLAIN命令的常用选项有哪些？
查询计划的基本结构是什么？包含哪些关键信息？
常见的查询计划节点有哪些？各适用于什么场景？
如何解读查询计划？如何识别性能瓶颈？
查询计划优化的基本技巧有哪些？
如何使用pg_stat_statements分析慢查询？
如何配置查询优化器的参数？
常见的查询优化模式有哪些？
如何使用查询计划分析工具？

2048 AI社区

有“AI”的1024 = 2048，欢迎大家加入2048 AI社区

更多推荐

医疗模型Hyperopt调参稳住AUC

2048 AI社区

Java 后端如何高效对接 Python 微调大模型？四种数据交互方案全解析（含实战代码）

Python 微调大模型可通过 Flask/FastAPI 暴露 REST 接口，Java 使用 Spring 的或调用。Python 示例（FastAPI）：https://www.zhihu.com/zvideo/1993874870761722160/https://www.zhihu.com/zvideo/1993874870648451336/https://www.zhihu.com/

2048 AI社区

AI的平衡艺术：2026年如何在技术狂奔中守护人性价值

2026年AI发展进入精智落地新阶段，战略核心从"盲目追新"转向"平衡艺术"。本文深入探讨技术与人性、创新与治理、自动化与体验三大平衡维度，通过真实场景分析展示如何构建可持续的AI价值体系。文章为企业和技术开发者提供兼具前瞻性与实操性的实施框架，助力在AI浪潮中把握发展节奏。关键词AI战略、技术伦理、人机协同、AI治理、用户体验、可持续创新