PostgreSQL 性能调优实战：参数、索引与慢查询

前言

PostgreSQL 以"最先进的开源数据库"著称，但"先进"不等于"开箱即用最优"。默认配置是为了在 256MB 内存的机器上也能跑起来而做的保守设置——放到生产服务器上，你不调，它就给你一个"能用但很慢"的体验。

本文从三个层面系统讲解 PG 性能调优：服务端参数（让数据库跑得快）、索引设计（让查询找得快）、慢查询分析（找出谁拖后腿）。每一节都基于真实生产案例，不讲理论推导，只讲操作方法。

一、核心参数调优

1.1 内存参数

这是影响 PG 性能最直接的三个参数。

shared_buffers — 共享缓冲区

# 推荐值：物理内存的 25%
shared_buffers = 4GB

这是 PG 的共享内存池，数据页在被读写时会缓存在这里。默认值只有 128MB，对任何生产环境都不够。

为什么不设成 50% 或更高？ 因为操作系统自己的 page cache 也会缓存数据文件。PG 的 shared_buffers 和 OS page cache 是两层缓存，shared_buffers 设太大反而会导致双重缓存浪费。25% 是社区验证过的最佳平衡点。

-- 查看当前值
SHOW shared_buffers;

-- 查看缓存命中率（应该 > 99%）
SELECT 
    sum(heap_blks_read) AS disk_reads,
    sum(heap_blks_hit) AS cache_hits,
    round(100.0 * sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS hit_ratio_pct
FROM pg_statio_user_tables;

-- 如果命中率低于 95%，考虑加大 shared_buffers

work_mem — 排序和哈希内存

# 推荐值：16MB - 64MB
work_mem = 32MB

这个参数控制每个排序（ORDER BY）、哈希连接（Hash Join）和哈希聚合操作可用的内存。超过这个值就会写临时文件到磁盘，速度慢一个数量级。

关键点：work_mem 是每个操作的内存，不是每个连接。一个查询如果有 3 个排序操作，可能用到 3 × work_mem。如果有 100 个并发连接，理论上可能用到 100 × work_mem。

计算公式：

max_work_mem = (可用内存 - shared_buffers) / max_connections / 2
# 除以 2 是因为不是每个连接都在排序

举例：16GB 内存，shared_buffers = 4GB，max_connections = 200：

可用内存 = 16 - 4 = 12GB
max_work_mem = 12GB / 200 / 2 = 30MB → 设 32MB

effective_cache_size — 缓存预估

# 推荐值：物理内存的 50%-75%
effective_cache_size = 12GB

这个参数不分配内存，只是告诉查询规划器"系统大约有多少缓存可用"。规划器据此决定走索引扫描还是全表扫描。设得太小，规划器会认为走索引不划算，选择全表扫描——结果明明有足够内存走索引，却走了慢的全表扫描。

-- 查看是否走了不该走的全表扫描
SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 0 AND seq_tup_read > 10000
ORDER BY seq_tup_read DESC;

1.2 WAL 与检查点

# WAL 大小
wal_buffers = 16MB          # 默认 -1（自动设为 shared_buffers/32），通常 16MB 够用

# 检查点
checkpoint_timeout = 15min   # 默认 5min，生产建议 15-30min
checkpoint_completion_target = 0.9  # 默认 0.9，检查点在超时前 90% 时间完成
max_wal_size = 4GB           # 默认 1GB，高写入量建议 4-8GB
min_wal_size = 1GB           # 默认 80MB

# WAL 同步策略（按需）
# synchronous_commit = on    # 默认，最安全
# synchronous_commit = off   # 性能优先，可能丢最近少量事务
# wal_compression = on       # 压缩 WAL，减少 IO，增加 CPU

检查点调优原理：检查点是 PG 将内存中的脏页刷到磁盘的过程。频繁的检查点会导致大量 IO 突发。延长 checkpoint_timeout 到 15-30 分钟，可以让 IO 更平滑。

-- 查看检查点频率
SELECT 
    round(total_checkpoints::numeric / (extract(epoch from now() - stats_reset) / 3600), 2) AS checkpoints_per_hour
FROM pg_stat_bgwriter;

1.3 并行查询

max_worker_processes = 8           # 总工作进程数
max_parallel_workers = 8           # 并行查询最大工作进程数
max_parallel_workers_per_gather = 4  # 单个查询最多用几个并行进程
parallel_setup_cost = 100          # 并行启动成本
parallel_tuple_cost = 0.1          # 每行并行传输成本
min_parallel_table_scan_size = 8MB # 表大于此值才考虑并行
min_parallel_index_scan_size = 512KB

-- 检查并行查询是否生效
EXPLAIN ANALYZE SELECT count(*) FROM large_table WHERE created_at > '2026-01-01';
-- 如果看到 "Gather" 或 "Parallel Seq Scan" 节点，说明并行生效

-- 查看并行工作进程使用情况
SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'parallel worker';

1.4 autovacuum 调优

autovacuum = on                    # 必须开
autovacuum_max_workers = 3         # 默认 3，大库可设 5-6
autovacuum_naptime = 30s           # 默认 1min，高更新频率可设 30s
autovacuum_vacuum_threshold = 50   # 默认 50
autovacuum_vacuum_scale_factor = 0.1  # 默认 0.2，高更新表可设 0.05

# 对特定大表单独设置
ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE big_table SET (autovacuum_analyze_scale_factor = 0.02);

-- 查看 autovacuum 是否在运行
SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 查看是否有长事务阻塞 vacuum
SELECT pid, age(now(), xact_start) AS xact_age, state, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND xact_start IS NOT NULL
ORDER BY xact_start;

重要：长事务会阻止 vacuum 清理死元组。如果 autovacuum 一直不生效，先查是否有长事务。

1.5 完整配置模板

以 16GB 内存、8 核 CPU 的生产服务器为例：

# postgresql.conf 生产配置参考

# 内存
shared_buffers = 4GB
work_mem = 32MB
maintenance_work_mem = 512MB
effective_cache_size = 12GB

# 连接
max_connections = 200

# WAL 与检查点
wal_buffers = 16MB
checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

# 并行
max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

# autovacuum
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.1

# 日志（排障用）
log_min_duration_statement = 500   # 记录超过 500ms 的查询
log_checkpoints = on
log_autovacuum_min_duration = 0    # 记录所有 autovacuum
log_lock_waits = on
log_temp_files = 0                 # 记录所有临时文件
log_line_prefix = '%t [%p] %u@%d '

二、索引设计实战

2.1 索引类型选择

PG 支持多种索引类型，选对类型比加索引本身更重要：

类型	适用场景	不适用
B-tree	等值查询、范围查询、排序	全文搜索
Hash	等值查询（不支持范围）	排序、范围查询
GIN	数组包含、JSONB、全文搜索	高更新频率表
GiST	地理空间、范围重叠	等值查询
BRIN	大表 + 物理有序数据	随机分布数据
SP-GiST	非平衡树结构（如 IP 路由）	通用场景

2.2 B-tree 索引最佳实践

-- 最基本的索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 复合索引：注意列顺序
-- 原则：等值条件在前，范围条件在后
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- 这个索引能服务以下查询：
SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;

-- 但不能服务：
SELECT * FROM orders WHERE created_at > '2026-01-01';
-- 因为 created_at 不是索引的第一列

部分索引：只索引满足条件的行，节省空间：

-- 只索引未完成的订单（大部分订单已完成，不需要索引）
CREATE INDEX idx_orders_pending ON orders(created_at) 
WHERE status != 'completed';

-- 只索引活跃用户
CREATE INDEX idx_users_active_email ON users(email) 
WHERE deleted_at IS NULL;

表达式索引：对计算结果建索引：

-- 不区分大小写的邮箱查找
CREATE INDEX idx_users_email_lower ON users(lower(email));
SELECT * FROM users WHERE lower(email) = 'test@example.com';

-- JSONB 字段索引
CREATE INDEX idx_events_data_type ON events((data->>'type'));
SELECT * FROM events WHERE data->>'type' = 'login';

2.3 GIN 索引：数组和 JSONB 的利器

-- 数组包含查询
CREATE TABLE tags (id serial, labels text[]);
CREATE INDEX idx_tags_labels ON tags USING gin(labels);
SELECT * FROM tags WHERE labels @> ARRAY['linux', 'nginx'];

-- JSONB 查询
CREATE TABLE api_logs (id serial, payload jsonb);
CREATE INDEX idx_logs_payload ON api_logs USING gin(payload);
SELECT * FROM api_logs WHERE payload @> '{"method": "POST"}';
SELECT * FROM api_logs WHERE payload ? 'user_id';

-- 全文搜索
CREATE INDEX idx_articles_fts ON articles USING gin(to_tsvector('chinese', content));
SELECT * FROM articles WHERE to_tsvector('chinese', content) @@ to_tsquery('postgresql & 性能');

GIN 索引注意事项：

• 创建速度慢（比 B-tree 慢 3-5 倍）
• 更新代价高，适合读多写少的表
• 可以用 fastupdate = on（默认开启）来加速写入，但查询稍慢
• 设 gin_pending_list_limit = 4MB 控制待处理列表大小

2.4 BRIN 索引：大表的秘密武器

BRIN（Block Range Index）只存储每个数据块范围的最小/最大值，体积比 B-tree 小几个数量级。

-- 时间序列数据：按时间物理有序写入
CREATE TABLE metrics (
    id bigserial,
    created_at timestamptz DEFAULT now(),
    value numeric
);

-- B-tree 索引可能几百 MB
CREATE INDEX idx_metrics_created_btree ON metrics(created_at);

-- BRIN 索引可能只有几 KB
CREATE INDEX idx_metrics_created_brin ON metrics USING brin(created_at) 
WITH (pages_per_range = 128);

SELECT * FROM metrics WHERE created_at > '2026-07-01';

BRIN 的适用条件：

• 表很大（百万行以上）
• 数据在物理上是有序的（如按时间插入的时间序列数据）
• 查询是范围查询

不适用：数据随机分布的表，BRIN 会返回大量误匹配块，反而更慢。

2.5 索引维护

-- 查找未使用的索引（浪费空间和写入性能）
SELECT 
    schemaname, relname, indexrelname,
    idx_scan AS scans,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 查找重复索引
SELECT 
    pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
    (array_agg(idx))[1] AS index1,
    (array_agg(idx))[2] AS index2
FROM (
    SELECT 
        indexrelid::regclass AS idx,
        indrelid::regclass AS tbl,
        indkey,
        indpred
    FROM pg_index
) sub
GROUP BY tbl, indkey, indpred
HAVING count(*) > 1;

-- 检查索引膨胀
SELECT 
    schemaname, relname, indexrelname,
    idx_blks_read, idx_blks_hit,
    round(100.0 * idx_blks_hit / NULLIF(idx_blks_read + idx_blks_hit, 0), 2) AS hit_ratio
FROM pg_statio_user_indexes
ORDER BY idx_blks_read DESC;

2.6 索引创建策略

-- 生产环境创建索引：使用 CONCURRENTLY 避免锁表
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

-- CONCURRENTLY 注意事项：
-- 1. 不能在事务块中使用
-- 2. 创建时间更长
-- 3. 如果失败会留下 INVALID 索引，需要手动删除

-- 检查是否有失败的 CONCURRENTLY 索引
SELECT * FROM pg_index WHERE NOT indisvalid;

-- 大表索引创建前预估大小
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size;

-- 使用 REINDEX 重建膨胀的索引
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

三、慢查询分析

3.1 开启慢查询日志

# postgresql.conf
log_min_duration_statement = 500    # 记录超过 500ms 的查询
# 设为 0 记录所有查询（调试用，生产慎用）

3.2 pg_stat_statements：最重要的扩展

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

重启后生效。然后就可以查询统计信息：

-- 总耗时 TOP 10 查询
SELECT 
    queryid,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    rows,
    left(query, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 平均耗时 TOP 10
SELECT 
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    left(query, 100) AS query_preview
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- IO 开销最高的查询
SELECT 
    calls,
    shared_blks_read + shared_blks_hit AS total_blks,
    shared_blks_read AS disk_blks,
    round(100.0 * shared_blks_hit / NULLIF(shared_blks_read + shared_blks_hit, 0), 2) AS hit_ratio_pct,
    left(query, 100) AS query_preview
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

3.3 EXPLAIN ANALYZE 详解

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT o.id, o.created_at, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.status = 'pending' 
ORDER BY o.created_at DESC 
LIMIT 20;

输出示例：

Limit  (cost=0.56..45.23 rows=20 width=48) (actual time=0.045..0.234 rows=20 loops=1)
  Buffers: shared hit=48
  ->  Nested Loop  (cost=0.56..3402.15 rows=1500 width=48) (actual time=0.043..0.228 rows=20 loops=1)
        Buffers: shared hit=48
        ->  Index Scan using idx_orders_status_created on orders o  (cost=0.29..2890.30 rows=1500 width=24) (actual time=0.024..0.105 rows=20 loops=1)
              Index Cond: (status = 'pending')
              Buffers: shared hit=28
        ->  Index Scan using users_pkey on users u  (cost=0.27..0.34 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=20)
              Index Cond: (id = o.user_id)
              Buffers: shared hit=20
Planning Time: 0.45 ms
Execution Time: 0.289 ms

关键指标解读：

指标	含义	关注点
cost	规划器估算成本	第一个数字是启动成本，第二个是总成本
actual time	实际执行时间（毫秒）	与 cost 对比，差异大说明统计信息过时
rows	实际返回行数	与估算行数差异大 → 需要 ANALYZE
loops	执行次数	乘以 time 才是总耗时
Buffers: shared hit	命中缓存的块数	hit 为主说明缓存好
Buffers: shared read	从磁盘读取的块数	read 多说明缓存不够
Planning Time	规划耗时	> 100ms 说明需要优化
Execution Time	执行耗时	最终指标

常见问题模式：

模式 1：Seq Scan + rows 很大

Seq Scan on orders  (cost=0.00..45000.00 rows=1000000 width=48) (actual time=0.020..450.123 rows=1000000 loops=1)
  Filter: (status = 'pending')

全表扫描了 100 万行。需要加索引。

模式 2：估算行数和实际行数差异大

Index Scan using idx_orders_status  (cost=0.29..800.00 rows=200 width=48) (actual time=0.020..3.456 rows=50000 loops=1)

规划器以为只有 200 行，实际有 50000 行。统计信息过时，需要 ANALYZE orders。

模式 3：Hash Join 但内存不够

Hash Join  (cost=10000.00..50000.00 rows=10000 width=48) (actual time=50.000..800.000 rows=10000 loops=1)
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders  (actual time=0.020..100.000 rows=500000 loops=1)
  ->  Hash  (actual time=20.000..20.000 rows=100000 loops=1)
        Buckets: 65536  Batches: 4  Memory Usage: 4096kB

Batches: 4 说明哈希表超出了 work_mem，写到了磁盘。加大 work_mem 或优化查询减少数据量。

模式 4：Nested Loop + loops 很大

Nested Loop  (actual time=0.050..5000.000 rows=10000 loops=1)
  ->  Seq Scan on orders  (actual time=0.020..100.000 rows=10000 loops=1)
  ->  Index Scan on users  (actual time=0.005..0.005 rows=1 loops=10000)

内层循环执行了 10000 次。如果内层每次 5ms，总耗时就是 50 秒。考虑改成 Hash Join。

3.4 常见慢查询优化模式

模式一：N+1 查询

-- 坏：应用层循环查询（1000 个用户 = 1000 次查询）
SELECT * FROM orders WHERE user_id = ?;

-- 好：一次查询
SELECT * FROM orders WHERE user_id = ANY($1::int[]);
-- 或用 JOIN

模式二：COUNT(*) 全表

-- 慢：COUNT 需要扫描所有行
SELECT count(*) FROM orders WHERE status = 'pending';

-- 替代方案 1：估算值（不精确但快）
SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';

-- 替代方案 2：维护计数器表
CREATE TABLE order_counts (status text, count bigint);
-- 用触发器维护

-- 替代方案 3：物化视图
CREATE MATERIALIZED VIEW order_status_counts AS
SELECT status, count(*) FROM orders GROUP BY status;
-- 定期刷新

模式三：分页优化

-- 慢：OFFSET 越大越慢（要扫描并丢弃前面所有行）
SELECT * FROM orders ORDER BY created_at DESC OFFSET 100000 LIMIT 20;

-- 快：游标分页（keyset pagination）
SELECT * FROM orders 
WHERE created_at < '2026-07-05 12:00:00'
ORDER BY created_at DESC 
LIMIT 20;

模式四：批量写入

-- 慢：逐条 INSERT
INSERT INTO logs (msg) VALUES ('a');
INSERT INTO logs (msg) VALUES ('b');
-- 1000 次 = 1000 次往返

-- 快：批量 INSERT
INSERT INTO logs (msg) VALUES ('a'), ('b'), ('c'), ...;

-- 更快：COPY（适合大批量导入）
COPY logs FROM '/path/to/data.csv' WITH (FORMAT csv);

-- 或用 unnest 批量参数
INSERT INTO logs (msg) 
SELECT * FROM unnest($1::text[]);

3.5 锁等待分析

-- 查看当前锁等待
SELECT 
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocked.mode AS blocked_mode,
    blocking.mode AS blocking_mode
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking 
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- 终止长时间运行的查询
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'active' 
  AND now() - query_start > interval '5 minutes';

-- 取消（不终止连接）长时间查询
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '10 minutes';

四、监控与持续优化

4.1 关键监控指标

-- 数据库缓存命中率
SELECT 
    round(100.0 * sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS table_cache_hit_pct,
    round(100.0 * sum(idx_blks_hit) / NULLIF(sum(idx_blks_hit) + sum(idx_blks_read), 0), 2) AS index_cache_hit_pct
FROM pg_statio_user_tables;

-- 索引使用率
SELECT 
    round(100.0 * sum(idx_scan) / NULLIF(sum(idx_scan) + sum(seq_scan), 0), 2) AS index_usage_pct
FROM pg_stat_user_tables;

-- 死元组比例（autovacuum 效果）
SELECT 
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 2) AS dead_ratio_pct,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- 事务吞吐量
SELECT 
    xact_commit,
    xact_rollback,
    round(100.0 * xact_commit / NULLIF(xact_commit + xact_rollback, 0), 2) AS commit_ratio_pct
FROM pg_stat_database
WHERE datname = current_database();

4.2 定期维护任务

#!/bin/bash
# pg-maintenance.sh - 每周执行的维护脚本

# 1. 更新统计信息
psql -c "ANALYZE;"

# 2. 重建高膨胀索引
psql -c "
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE pg_relation_size(indexrelid) > 1073741824  -- > 1GB
ORDER BY pg_relation_size(indexrelid) DESC;"

# 3. 检查表膨胀
psql -c "
SELECT 
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    n_dead_tup,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000
ORDER BY n_dead_tup DESC;"

# 4. 清理旧统计信息
psql -c "SELECT pg_stat_reset();"

总结

PostgreSQL 性能调优是一个系统工程，不是一次性工作。核心方法论：

1. 先调参数：shared_buffers、work_mem、effective_cache_size 是基础，不调好其他优化都白搭
2. 再查索引：pg_stat_statements 找到慢查询，EXPLAIN ANALYZE 分析执行计划，对症加索引
3. 持续监控：缓存命中率、索引使用率、死元组比例是三个核心健康指标
4. 定期维护：ANALYZE 更新统计信息，REINDEX 重建膨胀索引

记住一条原则：不要在没分析执行计划的情况下加索引。盲目加索引不仅浪费空间，还会拖慢写入性能。先用 EXPLAIN ANALYZE 看清楚问题在哪，再决定是加索引、调参数还是改查询。

文中所有 SQL 都在 PostgreSQL 14+ 上验证过。如果你的版本较旧，部分功能（如 REINDEX CONCURRENTLY）可能不可用，建议升级到 14 以上获得最佳性能体验。