PostgreSQLNotes

第 12 章：查询优化

zjc 于 2026-01-12 发布

这是《PostgreSQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 查询优化是围绕“减少扫描、减少排序、减少中间结果、控制内存”的循环过程。

12.1 优化流程

收集慢 SQL
  -> EXPLAIN ANALYZE
  -> 检查扫描节点
  -> 检查过滤条件
  -> 检查索引匹配
  -> 检查连接顺序
  -> 检查排序聚合内存
  -> 压测并发
  -> 固化规范

12.2 慢 SQL 来源

日志：

SHOW log_min_duration_statement;

可使用 pg_stat_statements：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    queryid,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    shared_blks_read,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

12.3 索引匹配

常见失效：

WHERE created_at::date = current_date

改写：

WHERE created_at >= current_date
  AND created_at < current_date + interval '1 day'

类型一致：

bigint 列 = '123' 参数可转换
text 列 = int 参数可能不走索引

12.4 减少返回数据

避免：

SELECT * FROM orders;

分页：

SELECT id, order_no
FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;

深分页可用键集分页替代 OFFSET。

12.5 JOIN 优化

SELECT o.id, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= now() - interval '1 day';

要点：

  1. 连接键类型一致；
  2. 小表驱动或哈希连接由优化器选择；
  3. 内层循环必须有索引；
  4. 先过滤再连接；
  5. 避免函数包装连接键；
  6. 控制连接列重复值。

12.6 聚合与排序

HashAggregate 内存超限会改用磁盘：

SET work_mem = '64MB';

调整只针对会话或特定查询，不要全局盲目调大导致并发内存不足。

优化：

  1. 先过滤；
  2. 减少分组维度；
  3. 物化汇总表；
  4. 覆盖索引；
  5. 使用近似；
  6. 分批处理。

12.7 并行查询

查看：

EXPLAIN ANALYZE
SELECT count(*) FROM large_table;

相关参数：

SHOW max_parallel_workers_per_gather;
SHOW max_parallel_workers;
SHOW min_parallel_table_scan_size;

并行不是万能：小表、索引点查、高并发短查询可能收益低。

12.8 CTE 与子查询

PostgreSQL 12+ 默认可以下推非物化 CTE。若显式 WITH MATERIALIZED，外层条件可能无法下推：

WITH t AS MATERIALIZED (
    SELECT * FROM orders WHERE status = 'PAID'
)
SELECT * FROM t WHERE user_id = 1001;

需要复用中间结果时物化有价值，否则用执行计划验证。

12.9 参数化计划

使用绑定参数可减少解析和计划次数：

PREPARE get_orders(bigint) AS
SELECT * FROM orders WHERE user_id = $1;

EXECUTE get_orders(1001);

极端数据分布下，通用计划可能不适合所有参数。PostgreSQL 有 plan cache 策略，应用层也可评估指定查询不走通用计划。

12.10 常见优化清单

现象 优化
Seq Scan 大表 检查索引和过滤条件
Index Scan 大量回表 覆盖索引或 Bitmap
Rows Removed 高 索引列不匹配
Sort Disk work_mem、索引排序
Hash 耗时 连接键分布、过滤
估算偏差 ANALYZE、扩展统计
临时块高 减少中间结果

本章小结

查询优化要从真实执行数据出发。pg_stat_statements 找成本最高的 SQL，EXPLAIN ANALYZE 定位节点，统计信息和索引决定大部分性能。并发场景必须一起压测。

思考题

  1. total_exec_time 和 mean_exec_time 分别适合什么排序？
  2. 函数包装列为什么影响索引？
  3. work_mem 如何安全调整？
  4. 并行查询何时收益有限？
  5. CTE 物化有什么利弊？