PostgreSQL 运维实战系列，第三期：性能调优与查询优化深度实践

0. 前言：为什么 80% 的性能问题集中在查询层

前两期我们搭建了生产环境和高可用架构，但有了一个稳定运行的集群只是起点。数据库的最终价值在于以最低的延迟和成本返回数据——而决定这一点的核心是查询优化。

大多数“慢数据库”本质上不是硬件不够、配置不对，而是查询写得不好，索引建得不到位。Andrew Atkinson 在《High Performance PostgreSQL》中指出，很多性能问题都源于不够完善的数据库设计实践和索引策略。解决 80% 的性能问题，只需要掌握三件事：读懂执行计划、建对索引、避开反模式。这就是第三期的全部内容。

1. 读懂 EXPLAIN：DBA 的必修课

1.1 从执行计划看优化器的“思考”

PostgreSQL 执行一条 SQL，要经历词法分析、语法分析、查询重写、查询规划器和执行器五个阶段，其中最关键的环节是查询规划器——它基于代价模型从多个候选方案中选择“成本最低”的，这个成本是一个结合 CPU 运算和磁盘 I/O 的综合估值。

而评估的基础是 ANALYZE 命令（或者在 autovacuum 触发下自动完成）收集的统计信息，存储在 pg_statistic 系统表中，包括：唯一值数量（n_distinct）、高频值及其频率（MCVs）、直方图分布等，最终可以通过 pg_stats 视图查看列级别的细节。

EXPLAIN ANALYZE 就是让我们一窥优化器“思考过程”的重要工具。

它的输出由两部分构成：

规划期的代价估算：优化器给出的估算行数、启动成本和总成本（cost=0.28..8.29，前一个数是启动成本，后一个是总成本）；
执行期的实际数据：实际耗时（actual time 的单位是毫秒）、真实行数、循环次数，这才是验证优化器是否“猜对”的唯一依据。

当实际行数与估算行数差距巨大（比如实际返回 5000 行，估算只有 5 行）时，优化器很可能选择了次优计划。

1.2 读懂执行计划中的“危险信号”

用 EXPLAIN ANALYZE 诊断慢查询时，扫一眼输出就能精准定位问题的根源：

现象	执行计划中的表现	代价估算	最常见原因	解决动作
全表扫描	`Seq Scan` 出现在大户头上，同时 idx_scan 统计为 0	—	WHERE 条件没有匹配的索引，或优化器低估了小表	对 WHERE/JOIN 列建索引；检查数据量，小表全表扫描亦可能是最优解
不必要的大规模排序	显式 `Sort` 节点，且 `actual rows` 非常大（比如百万级）	—	缺少支持 ORDER BY 的索引；无索引排序将所有行装进内存	针对排序键单独加索引，或者用 `btree(column)` 索引代替 ORDER BY
严重行过滤	`Rows Removed by Filter: X` 里 X 远大于最终返回的行数	—	索引选择性差，大量数据被读入后才被过滤掉	建组合索引把过滤条件收紧；调整 WHERE 中条件的顺序
嵌套循环连接	`Nested Loop` 配 `Join Filter`，内表每行驱动一次	—	外表大 + 内表无索引，Nested Loop 演变成慢速查询	在内表的连接列上创建索引；或将 `enable_nestloop` 调至 `off`，让优化器转向 Hash Join 或 Merge Join
`Buffers: read` 过多	`Buffers: shared read=xxx`	—	内存容量不足，数据页反复从磁盘读取	提升 `shared_buffers`（专用服务器建议 25% 系统内存）
估算与实际严重偏离	`actual rows` 与 `rows` 估算值相差 10 倍以上	导致嵌套循环选错	统计信息陈旧，或数据分布不均列间强关联	执行 `ANALYZE table`；多列关联统计不足时用 `CREATE STATISTICS` 增加扩展统计

1.3 统计信息调优

默认统计采样大小由 default_statistics_target 控制，默认值为 100——在小表上用没问题，上了千万级别误差就很大了。实操方法：

全局提采样精度：将 default_statistics_target 从 100 提升至 200 或 500，增加 ANALYZE 的样本量，通常能带来明显改善。
个别列精准打击：业务中经常作为过滤条件的热列，不要走全局开关，用 ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 5000 专门调高。
扩展统计捕捉列间相关性：对于 city 与 state 这种强关联列，仅凭单列统计信息会让优化器低估选择率，可执行 CREATE STATISTICS stats_city_state ON city, state FROM zipcodes; 后再 ANALYZE zipcodes;，让优化器感知列间依赖关系。

1.4 使用 hint 干预计划：什么时候该用，怎么用

大多数情况下，我们依赖优化器自行选择。但一些复杂查询、数据严重倾斜场景下，优化器会持续选择次优计划——这是 hint 介入的时机。PostgreSQL 不自带 hint，但可以通过 pg_hint_plan 扩展来实现：

-- 安装扩展后，在 SQL 前加注释格式 hint
/*+ HashJoin(a b) Leading((a b)) */
SELECT * FROM a JOIN b ON a.id = b.a_id;

对 hint 保持审慎：计划锁定是双刃剑。一旦表结构、数据分布或版本变化，锁定住的计划可能变得比原来的更差。因此 hint 最安全的用法是临时止血 + 限期移除。同时在全局监控中留意，不要让 hint 变成永久的“技术债”。

2. 索引策略：选对在哪儿建索引，胜过盲目建十个

2.1 组合索引的顺序原则

最经典的索引反模式：为单个字段建一堆单列索引，让优化器在一堆“镜子碎片”中迷失。更高效的做法是：用组合索引统一覆盖多个过滤和排序条件。组合索引的顺序依赖查询模式。一个电商的例子：

SELECT * FROM orders 
WHERE status = 'shipped' 
  AND created_at >= now() - interval '30 days' 
ORDER BY created_at DESC;

如果只有两个分离的单列索引 idx_orders_status 和 idx_orders_created_at，最终执行计划仍然逃不掉额外的排序。正确的做法是直接建组合索引且匹配排序：

CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

此索引在 B-Tree 顺序中已经把排序做完了——过滤 status 时拿到按 created_at 顺序排列的数据，无需再 Sort。查询全天的 status='shipped' 订单时，第一个字段 status 也能很好地利用索引的首列。

2.2 索引类型选择速查表

PG 支持 B-Tree、Hash、GiST、GIN、BRIN 等多种索引，选对类型比盲目建索引重要得多。这是生产中最容易用错的领域之一，配一个参考表帮你快速判断：

场景	推荐索引	关键限制	实战应用
等值/范围/排序	B-Tree（默认）	组合索引遵守最左前缀顺序	WHERE create_time BETWEEN a AND b，配合 ORDER BY
等值查询（无范围、排序）	Hash	仅支持 `=` 操作，不支持排序	海量日志中的 status_code 查询适合 Hash
全文检索	GIN	对 tsvector 类型构建	搜索新闻、仓库文件描述等文本
数组/JSONB 成员查询	GIN	倒排索引高效处理多值字段	多标签系统中的 tags @> ARRAY[‘postgres’]
地理位置/几何范围查询	GiST	邻近搜索、相交范围检索	门店 5 km 范围 + 几何图形碰撞
超大表但列值顺序与物理排列相关（如时序、自增 ID）	BRIN	按块范围存储元信息，比 B-Tree 节省数十倍空间	IoT 设备数据、每天千万行的监控指标存储
带条件子集的查询（如只查未删除的行）	Partial（表达式索引+条件）	仅索引满足条件子集	大表中 is_deleted = false + status = ‘active’ 组合
让索引“覆盖”查询，避免回表	Covering（INCLUDE）	索引内包含额外非键列	常查某几列的场景：`CREATE INDEX ... INCLUDE (name, age)`

有了这个索引矩阵，建索引时便能准确出击。

2.3 为什么索引会“失效”

有时候建了索引却不生效，往往不是 PG 的错，而是写法踩了陷阱。以下几个场景是对照排查的起点：

索引失效原因	错误示例	正确写法
字段上套了函数	`WHERE date(created_at) = '2026-01-01'`	`WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'`
隐式类型转换（类型不匹配）	`WHERE phone_number = 13800001111`（字段是 varchar）	`WHERE phone_number = '13800001111'`
组合索引违反最左前缀	索引 `(first_name, last_name)`，查询只用 `last_name`	将经常独立查询的字段前置

2.4 索引维护与清理：不要让索引成为负担

过度索引的危害常常是隐性的：每个索引都会拖累 INSERT/UPDATE/DELETE 的性能。在更新频繁的热表上，所有索引的维护开销会叠加，某些场景下索引总大小甚至超过表本身。定期清理策略：

找到从未使用的索引：查询 pg_stat_user_indexes，关注 idx_scan 字段，如果在一到两周内索引从未被使用（idx_scan=0），可以谨慎评估后删除。
定位索引膨胀：pg_stat_user_indexes 没有直接显示膨胀率，可借助 pgstattuple 扩展或查询 pg_class.reltuples 对比索引大小与实际使用情况。
用 REINDEX 清理碎片：

-- 在线重建索引（持有 SHARE UPDATE EXCLUSIVE 锁，允许并发读写）
REINDEX INDEX CONCURRENTLY idx_name;

-- 重建整个表所有索引
REINDEX TABLE CONCURRENTLY table_name;

3. SQL 反模式：新手最常踩的 7 个性能陷阱

大部分性能问题源自 SQL 写法。以下 7 类陷阱在生产环境中反复出现，值得我们逐一识别和规避。

陷阱 1：忽略索引导致全表扫描

经典场景：WHERE 条件没有匹配的索引，PostgreSQL 被迫走 Seq Scan。用户量巨大的表极慢。解法：为筛选列建立索引，且注意查询条件与索引匹配。

陷阱 2：函数包裹索引列

经典场景：WHERE SUBSTR(phone, 1, 3) = '139' 或 WHERE UPPER(name) = 'JOHN' 使索引无效。解法：为常用函数运算建立表达式索引：CREATE INDEX idx_phone_prefix ON users (SUBSTR(phone, 1, 3))。

陷阱 3：隐式类型转换

经典场景：字符串字段 phone 用数字比较 WHERE phone = 13800001111，PG 自动转换类型，绕过了索引。解法：让比较两边的数据类型一致。这是最常见但容易被忽略的问题。

陷阱 4：不必要的 SELECT *

经典场景：取少量字段却带回了整行数据，增加网络 I/O 和内存分配。同时禁用覆盖索引的可能性——如果所需字段都在索引里（INCLUDE 子句），可免去回表，大幅降低 I/O。应养成按需列出的习惯。

陷阱 5：笛卡尔积与 JOIN 顺序错误

经典场景：多个表连接时忘记加连接条件，或连接条件放错位置导致 Nested Loop 爆炸。解法：使用显式 JOIN 语法，把过滤条件前置，让优化器优先减少数据集。EXPLAIN 是发现 JOIN 顺序和类型的唯一有效手段。

陷阱 6：CTE（WITH 子句）的“优化围栏”

经典场景：许多人假设 CTE 内部会自动与外部合并优化，但默认情况下 CTE 是一个优化围栏——它被物化，不继承外围 WHERE 条件下推，导致子查询先吐出大量数据再行过滤。解法：

-- PG 12+ 用 MATERIALIZED / NOT MATERIALIZED 控制
WITH cte AS NOT MATERIALIZED (SELECT huge_table, column1, column2 FROM large_table)
SELECT * FROM cte WHERE column1 = 'active';  -- 优化器可下推条件

陷阱 7：分页深处的 OFFSET 计数

经典场景：OFFSET 1000000 LIMIT 20 意味着 PG 需要扫描前 100 万行再丢弃，即使有索引。解法：使用 Keyset Pagination（游标分页）：

-- 第一次请求
SELECT * FROM orders ORDER BY id LIMIT 20;
-- 下一次带上最后一条的 id
SELECT * FROM orders WHERE id > 12345 ORDER BY id LIMIT 20;

对基于时间的分页，可结合上次返回的 last_created_at 更高效推进。

4. 监控工具链：pg_stat_statements + pgBadger

查询监控有两条腿：实时靠扩展 pg_stat_statements，事后靠日志解析器 pgBadger。两者组合形成完善的可观测体系。

4.1 pg_stat_statements：实时 SQL 性能大脑

# postgresql.conf 配置
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

-- 创建扩展并查看 Top 慢查询
CREATE EXTENSION pg_stat_statements;

SELECT queryid, query, calls AS exec_count, 
       total_exec_time, mean_exec_time, 
       rows_fetched / calls AS avg_rows
FROM pg_stat_statements 
ORDER BY total_exec_time DESC LIMIT 10;

关键解读：

mean_exec_time（平均耗时） 是反应该查询单次性能的直接指针；
shared_blks_hit 与磁盘 I/O 成正比——缓存命中率低时同时检查 work_mem 和索引效率；
rows_fetched 如果显示每轮扫描行数远超最终返回的行数，表明过滤效率低下。

一个需要定期重置的陷阱：pg_stat_statements 是累积统计（存活于共享内存），如果不定期 SELECT pg_stat_statements_reset()，总数可能会变得极其臃肿，监测结果反而不再反映当前状态。

4.2 pgBadger：日志分析利器

pgBadger 会将 PostgreSQL 日志（CSV 格式）转化为一份图文并茂的 HTML 分析报告，给出慢查询分布、错误类型统计、并发峰值、I/O 负载等维度。

基础生成命令：

pgbadger /var/log/postgresql/postgresql.log.* -o /var/www/html/report.html

生产中的调度策略：

建议配置 cron 每日跑 pgBadger，自动生成趋势报告，用于对比昨日与今天的慢查询情况。
结合两者做深度回溯时，常常是先用 pg_stat_statements 定位高消耗查询，再通过日志时间段确认是否与系统维护、流量波峰相关。

4.3 OS 级别的监控配合

查询层面之外，操作系统级的监控亦不可或缺：top/htop 看资源争抢，iostat 观测磁盘 I/O 压力——一个查询突然变慢，有时不是数据库的问题，而是旁路进程吃满 CPU 或磁盘。另外 pg_stat_activity 能捕捉被锁阻塞的会话，这通常和慢查询形成共生关系。多维度交叉定位才能排查到底。

5. 压力测试与容量规划

5.1 pgbench 压测方法论

pgbench 是 PG 自带的基准测试工具，用它做两种测试：基准速度测试（性能基线） 和 负载差异对比（配置变更前后） 。标准流程：

# 创建测试数据库
createdb pgbench_test

# 用 50 倍因子（约 5000 万行）初始化数据集
pgbench -i -s 50 pgbench_test

# 执行压力测试（100 并发客户端，4 线程，跑 300 秒）
pgbench -c 100 -j 4 -T 300 pgbench_test

输出关注的指标是 tps（每秒事务数） ，包括含连接建立与不包含连接建立的 tps 值——前者反映整体吞吐，后者反映纯查询性能，适合做版本对比。

5.2 编写自定义 workload 脚本

默认的 TPC-B workload 无法揭示真实业务瓶颈。通过脚本读文件的方式，可以将特定业务的查询模式（如混合只读/写入 85%/15%）精准模拟出来。这种方法尤其适合配置变更前后的效果验证，以及上线前的容量预估。例如：

-- custom_workload.sql
BEGIN;
-- 模拟 85% 读负载
SELECT balance FROM accounts WHERE id = :aid;
-- 模拟 15% 写负载
set r random(1, 100)
if :r <= 15
    UPDATE accounts SET balance = balance + 10 WHERE id = :aid;
endif
END;

此处 :aid 由 pgbench 自动填充，无需手写具体值。

运行该自定义脚本只需指定文件：

pgbench -c 50 -T 300 -f custom_workload.sql pgbench_test

5.3 容量规划公式

从 pg_stat_statements 或 pgBadger 中提取实际负载指标，从容量规划的角度反推资源需求：

规划维度	估算公式	说明
峰值 QPS	`∑(calls_per_second) × 业务增长系数`	历史数据反推增长趋势，预留 2–3 年的扩展余量
CPU 需求	`(Σ total_exec_time / 采样周期) × 并行因子`	依据历史 CPU 使用率与数据库内时间综合估算
内存需求	`shared_buffers + (work_mem × 平均并发数 × 每查询操作数 × 安全系数)`	work_mem 的风险容易被低估
I/O 吞吐	`WAL 生成速率(MB/s) + checkpoint I/O + 业务读写峰值`	用 pgbench 峰值测试作为最高水位参考

6. 本期的实战检查清单

每日：EXPLAIN ANALYZE 复查 3–5 个最慢的查询，识别 Seq Scan 和实际行数偏离估算值的节点
每周：执行 REINDEX INDEX CONCURRENTLY 缓解索引膨胀；运行 pgBadger 生成 HTML 报告，对比上周与本周趋势
每月：做一次完整的 pgbench 基准测试，将 tps 值与之前三个月基线对比；清理 idx_scan 为零的索引（在业务窗口确认）
重大变更前：通过自定义 pgbench 脚本模拟切换后的新负载，探测潜在的瓶颈

写在最后的一点反思

查询优化不是“调几行参数、加几个索引”就万事大吉的——表统计信息需要适时的 ANALYZE 来保持新鲜，autovacuum 需要合理配置来控制表膨胀，而业务模式的变化又会带来新的访问路径，让原本健康的执行计划瞬间失灵。

所以第三期最想传递的理念是：把查询优化建成闭环——EXPLAIN ANALYZE 发现问题 → 转向索引或 SQL 重构 → 用 pgbench 验证效果 → 用监控持续跟踪。在追求性能的路上，最宝贵的不是某个绝妙的技巧，而是在你摸透 PostgreSQL 执行计划的数据思维。

参考资料

Cardinality Estimation in PostgreSQL 17 New Statistics Model and Tuning Tips – Netdata [10†L3-L38]

Why Your SQL Queries Are Slower Than a Sloth – dev.to [7†L3-L30]

PostgreSQL 十大性能问题及解决方案 – ManageEngine [6†L5-L27]

PostgreSQL新手SQL总翻车？这7个性能陷阱你踩过没？ – CSDN [8†L4-L7]

PostgreSQL性能监控：pg_stat_statements与pgBadger日志分析 – CSDN [15†L7-L27]

How to Use pgBadger to Monitor PostgreSQL Queries – Cybrosys [16†L22-L45]

How to Benchmark PostgreSQL Query Performance Using pgbench – Cybrosys [17†L12-L44]

PostgreSQL 18: 多列 B 树索引支持跳跃扫描 – rockdata.net [0†L24-L26]

Overview of PostgreSQL Planner Configuration Parameters – Cybrosys [12†L3-L53]

PostgreSQL 运维实战系列，第三期：性能调优与查询优化深度实践

0. 前言：为什么 80% 的性能问题集中在查询层

1. 读懂 EXPLAIN：DBA 的必修课

1.1 从执行计划看优化器的“思考”

1.2 读懂执行计划中的“危险信号”

1.3 统计信息调优

1.4 使用 hint 干预计划：什么时候该用，怎么用

2. 索引策略：选对在哪儿建索引，胜过盲目建十个

2.1 组合索引的顺序原则

2.2 索引类型选择速查表

2.3 为什么索引会“失效”

2.4 索引维护与清理：不要让索引成为负担

3. SQL 反模式：新手最常踩的 7 个性能陷阱

陷阱 1：忽略索引导致全表扫描

陷阱 2：函数包裹索引列

陷阱 3：隐式类型转换

陷阱 4：不必要的 SELECT *

陷阱 5：笛卡尔积与 JOIN 顺序错误

陷阱 6：CTE（WITH 子句）的“优化围栏”

陷阱 7：分页深处的 OFFSET 计数

4. 监控工具链：pg_stat_statements + pgBadger

4.1 pg_stat_statements：实时 SQL 性能大脑

4.2 pgBadger：日志分析利器

4.3 OS 级别的监控配合

5. 压力测试与容量规划

5.1 pgbench 压测方法论

5.2 编写自定义 workload 脚本

5.3 容量规划公式

6. 本期的实战检查清单

写在最后的一点反思

Leave a comment Cancel

HiddenMerit Daily · Issue 59

HiddenMerit Daily · Issue 58

HiddenMerit Daily · Issue 56

HiddenMerit Daily · Issue 57

PostgreSQL 运维实战系列，第三期：性能调优与查询优化深度实践

0. 前言：为什么 80% 的性能问题集中在查询层

1. 读懂 EXPLAIN：DBA 的必修课

1.1 从执行计划看优化器的“思考”

1.2 读懂执行计划中的“危险信号”

1.3 统计信息调优

1.4 使用 hint 干预计划：什么时候该用，怎么用

2. 索引策略：选对在哪儿建索引，胜过盲目建十个

2.1 组合索引的顺序原则

2.2 索引类型选择速查表

2.3 为什么索引会“失效”

2.4 索引维护与清理：不要让索引成为负担

3. SQL 反模式：新手最常踩的 7 个性能陷阱

陷阱 1：忽略索引导致全表扫描

陷阱 2：函数包裹索引列

陷阱 3：隐式类型转换

陷阱 4：不必要的 SELECT *

陷阱 5：笛卡尔积与 JOIN 顺序错误

陷阱 6：CTE（WITH 子句）的“优化围栏”

陷阱 7：分页深处的 OFFSET 计数

4. 监控工具链：pg_stat_statements + pgBadger

4.1 pg_stat_statements：实时 SQL 性能大脑

4.2 pgBadger：日志分析利器

4.3 OS 级别的监控配合

5. 压力测试与容量规划

5.1 pgbench 压测方法论

5.2 编写自定义 workload 脚本

5.3 容量规划公式

6. 本期的实战检查清单

写在最后的一点反思

Leave a comment Cancel

Recommendation

PostgreSQL 运维实战系列，第三期：性能调优与查询优化深度实践