强曰为道
与天地相似，故不违。知周乎万物，而道济天下，故不过。旁行而不流，乐天知命，故不忧.
文档目录

PostgreSQL 完全指南 / 08 - 查询优化

第 08 章 · 查询优化

查询优化是 DBA 和开发者的必备技能。本章介绍 PostgreSQL 的查询计划器、EXPLAIN ANALYZE 的使用方法以及常见优化技巧。

8.1 查询处理流程

SQL 文本
  │
  ▼
┌──────────┐
│ Parser   │ ── 语法分析，生成语法树
└────┬─────┘
     ▼
┌──────────┐
│ Analyzer │ ── 语义分析，解析表名/列名
└────┬─────┘
     ▼
┌──────────┐
│ Rewriter │ ── 应用规则系统（视图展开等）
└────┬─────┘
     ▼
┌──────────┐
│ Planner/ │ ── 生成最优执行计划
│Optimizer │    成本模型：随机页读取 + CPU 处理
└────┬─────┘
     ▼
┌──────────┐
│ Executor │ ── 按计划执行，返回结果
└──────────┘

8.2 EXPLAIN ANALYZE

基本用法

-- EXPLAIN：只看计划，不实际执行
EXPLAIN SELECT * FROM employees WHERE department = '工程部';

-- EXPLAIN ANALYZE：实际执行并返回真实统计
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = '工程部';

-- 常用选项组合
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM employees WHERE department = '工程部';

-- JSON 格式输出（适合工具解析）
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM employees WHERE department = '工程部';

输出解读

Seq Scan on employees  (cost=0.00..1250.00 rows=100 width=56) (actual time=0.015..8.500 rows=95 loops=1)
  Filter: (department = '工程部')
  Rows Removed by Filter: 905
  Buffers: shared hit=450 read=50
Planning Time: 0.150 ms
Execution Time: 8.700 ms
字段含义
Seq Scan扫描方式（顺序扫描）
cost=0.00..1250.00估算成本（启动成本..总成本）
rows=100估算返回行数
width=56估算每行平均字节数
actual time=0.015..8.500实际耗时（首行..最后一行，毫秒）
rows=95实际返回行数
loops=1该节点执行次数
Buffers: shared hit=450 read=50缓冲区命中 450 页，磁盘读取 50 页
Rows Removed by Filter过滤掉的行数

成本模型参数

-- 查看成本参数
SHOW seq_page_cost;          -- 顺序读一页的成本（默认 1.0）
SHOW random_page_cost;       -- 随机读一页的成本（默认 4.0）
SHOW cpu_tuple_cost;         -- 处理一行的 CPU 成本（默认 0.01）
SHOW cpu_index_tuple_cost;   -- 处理索引行的 CPU 成本（默认 0.005）
SHOW cpu_operator_cost;      -- 操作符的 CPU 成本（默认 0.0025）

-- SSD 环境调低随机读成本
SET random_page_cost = 1.1;

8.3 常见扫描方式

扫描方式说明适用场景
Seq Scan顺序扫描全表小表、无合适索引、大部分行
Index Scan索引扫描 + 回表有索引、返回少量行
Index Only Scan仅索引扫描（不回表）覆盖索引、Visibility Map 命中
Bitmap Index Scan位图索引扫描中等数量行、多条件组合
Bitmap Heap Scan位图堆扫描配合 Bitmap Index Scan
-- 触发 Seq Scan（无索引）
EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 0;

-- 触发 Index Scan（有索引）
CREATE INDEX idx_emp_dept ON employees (department);
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = '工程部';

-- 触发 Index Only Scan
EXPLAIN ANALYZE SELECT id FROM employees WHERE id < 100;

-- 触发 Bitmap Index Scan（多条件组合）
EXPLAIN ANALYZE SELECT * FROM employees
WHERE department = '工程部' AND salary > 15000;

8.4 常见 Join 策略

Join 类型说明适用场景
Nested Loop嵌套循环小表驱动大表、有索引
Hash Join哈希连接等值连接、无索引、中等表
Merge Join归并连接已排序的表、大表连接
-- 强制使用不同的 Join 策略来观察
SET enable_nestloop = off;
SET enable_hashjoin = on;
SET enable_mergejoin = off;
EXPLAIN ANALYZE SELECT e.name, d.budget
FROM employees e JOIN departments d ON e.department = d.name;

-- 恢复默认
RESET enable_nestloop;
RESET enable_hashjoin;
RESET enable_mergejoin;

8.5 统计信息

查询优化器依赖统计信息来估算成本。

-- 查看列的统计信息
SELECT
    attname AS column_name,
    n_distinct,           -- 不同值数量（-1 表示唯一）
    most_common_vals,     -- 最常见值
    most_common_freqs,    -- 最常见值的频率
    histogram_bounds,     -- 直方图边界
    null_frac             -- NULL 值比例
FROM pg_stats
WHERE tablename = 'employees';

-- 手动更新统计信息
ANALYZE employees;

-- 更精确的统计（更多采样）
ANALYZE employees (department, salary);

-- 设置统计精度
ALTER TABLE employees ALTER COLUMN department SET STATISTICS 1000;
ANALYZE employees;

8.6 并行查询

PostgreSQL 支持并行查询以利用多核 CPU。

-- 并行查询相关参数
SHOW max_parallel_workers_per_gather;  -- 每个 Gather 节点的最大并行工作者（默认 2）
SHOW max_parallel_workers;             -- 并行工作者总数上限（默认 8）
SHOW parallel_tuple_cost;              -- 并行处理一行的成本
SHOW parallel_setup_cost;              -- 启动并行工作者的成本
SHOW min_parallel_table_scan_size;     -- 触发并行扫描的最小表大小（默认 8MB）
SHOW min_parallel_index_scan_size;     -- 触发并行索引扫描的最小大小（默认 512kB）

-- 查看是否使用了并行
EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table;

-- 禁用并行（调试时）
SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table;

-- 增加并行度
SET max_parallel_workers_per_gather = 4;

并行查询计划示例：

Finalize Aggregate  (cost=1000.00..1000.01 rows=1 width=8)
  ->  Gather  (cost=1000.00..1000.01 rows=3 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=0.00..1000.00 rows=1 width=8)
              ->  Parallel Seq Scan on large_table  (cost=0.00..800.00 rows=100000 width=0)

8.7 常见查询优化技巧

避免 SELECT *

-- ❌ 不好
SELECT * FROM employees WHERE department = '工程部';

-- ✅ 好：只选需要的列
SELECT id, name, salary FROM employees WHERE department = '工程部';

-- ✅ 更好：使用覆盖索引
CREATE INDEX idx_emp_covering ON employees (department) INCLUDE (name, salary);
SELECT name, salary FROM employees WHERE department = '工程部';  -- Index Only Scan

避免隐式类型转换

-- ❌ 类型不匹配，无法使用索引
SELECT * FROM orders WHERE order_no = 12345;  -- order_no 是 TEXT

-- ✅ 类型匹配
SELECT * FROM orders WHERE order_no = '12345';

-- ❌ 函数包裹列，无法使用索引
SELECT * FROM employees WHERE LOWER(email) = 'alice@example.com';

-- ✅ 创建表达式索引
CREATE INDEX idx_emp_lower_email ON employees (LOWER(email));
SELECT * FROM employees WHERE LOWER(email) = 'alice@example.com';

LIMIT 优化

-- ❌ 深分页性能差
SELECT * FROM employees ORDER BY id LIMIT 20 OFFSET 100000;
-- 会扫描前 100020 行

-- ✅ Keyset 分页（Seek Method）
SELECT * FROM employees
WHERE id > 100000    -- 上一页最后一条的 id
ORDER BY id
LIMIT 20;

IN vs EXISTS vs JOIN

-- 当子查询结果集大时，EXISTS 通常更快
-- ❌ IN：需要物化子查询
SELECT * FROM employees e
WHERE e.department IN (SELECT d.name FROM departments d WHERE d.budget > 200000);

-- ✅ EXISTS：短路求值
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.name = e.department AND d.budget > 200000);

-- ✅ JOIN：优化器自动选择
SELECT e.* FROM employees e
JOIN departments d ON e.department = d.name
WHERE d.budget > 200000;

避免 OR 导致的全表扫描

-- ❌ OR 可能导致全表扫描
SELECT * FROM employees WHERE department = '工程部' OR department = '产品部';

-- ✅ 使用 IN
SELECT * FROM employees WHERE department IN ('工程部', '产品部');

-- ✅ 使用 UNION ALL（当 OR 无法使用索引时）
SELECT * FROM employees WHERE department = '工程部'
UNION ALL
SELECT * FROM employees WHERE department = '产品部';

8.8 查询优化器提示（Hints）

PostgreSQL 没有内置的 Hint 语法，但可以通过以下方式影响优化器：

-- 设置会话级参数
SET enable_seqscan = off;         -- 禁用顺序扫描
SET enable_indexscan = on;        -- 启用索引扫描
SET enable_hashjoin = off;        -- 禁用哈希连接
SET enable_mergejoin = on;        -- 启用归并连接

-- 调整代价参数
SET random_page_cost = 1.1;       -- SSD 环境
SET effective_cache_size = '4GB'; -- 估算可用缓存

-- CTE 物化控制
WITH cte AS MATERIALIZED (...) SELECT ...;
WITH cte AS NOT MATERIALIZED (...) SELECT ...;

-- 使用 pg_hint_plan 扩展（第三方）
-- CREATE EXTENSION pg_hint_plan;
-- /*+ IndexScan(employees idx_emp_dept) */
-- SELECT * FROM employees WHERE department = '工程部';

8.9 慢查询排查流程

1. 找到慢查询
   ├── pg_stat_statements（最常用）
   ├── log_min_duration_statement（日志记录）
   └── pg_stat_activity（实时查看）

2. EXPLAIN ANALYZE
   ├── 检查估算行数 vs 实际行数（差距大说明统计不准）
   ├── 检查是否有 Seq Scan（全表扫描大表）
   ├── 检查是否有 Nested Loop 且内表无索引
   └── 检查 Buffers（hit vs read 比例）

3. 分析原因
   ├── 统计信息过时 → ANALYZE
   ├── 缺少索引 → 创建索引
   ├── 索引未被使用 → 检查查询条件/类型转换
   ├── 参数不合理 → 调整 work_mem 等
   └── 数据分布不均 → 优化查询逻辑

4. 验证优化
   └── 再次 EXPLAIN ANALYZE 对比

业务场景

场景排查方法
查询突然变慢EXPLAIN ANALYZE → 检查计划变化
CPU 飙高pg_stat_statements 找高频查询
大表查询慢检查索引 + 统计信息 + 并行查询
深分页慢改用 Keyset 分页
连接慢检查连接池配置 + pg_stat_activity

扩展阅读