PostgreSQL 性能优化

2025-12-20 约 3743 字预计阅读 8 分钟

慢查询通常先暴露在计划上，而不是在 postgresql.conf 里。按 PostgreSQL 的路径：先跑 EXPLAIN (ANALYZE, BUFFERS)，看清 Seq Scan 还是 Index Scan，再决定索引、写法和配置。

缺索引、函数包住列、统计信息过期、autovacuum 跟不上、代价参数按机械盘默认——都会让同一条 SQL 走全表。先读计划，再动旋钮。

选 / 不选

选	不选
`EXPLAIN (ANALYZE, BUFFERS)` 当第一刀	先把 `shared_buffers` 改成网上抄来的 25%
等值列放复合索引左边；覆盖列用 `INCLUDE`	只在范围列上建单列索引，再抱怨复合索引「没用」
大表单独下调 `autovacuum_vacuum_scale_factor`	关掉 autovacuum「省 I/O」
`pg_stat_statements` 按累计耗时排序	凭一次慢查询截图加 covering index
btree 处理等值和范围；GIN / GiST 留给包含、全文、几何	给 `email = $1` 建 GIN

先看计划

EXPLAIN 只估代价，不跑查询。EXPLAIN ANALYZE 会真正执行，并给出每层节点的实际行数和耗时。当前文档里 ANALYZE 会隐含打开 BUFFERS；写成显式更不容易漏：

        
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE status = 'open'
  AND created_at >= DATE '2026-01-01';

表名换成实际对象。ANALYZE 有副作用：UPDATE / DELETE 会改数据。改表的语句包在 BEGIN … ROLLBACK 里看计划。

读计划时只盯三件事，不要对照网文里的毫秒数：

扫描类型：Seq Scan、Index Scan、Bitmap Index Scan + Bitmap Heap Scan、Index Only Scan。
rows 估计和实际是否差一个数量级。差一个数量级，优先 ANALYZE 表，而不是加索引。
Buffers：shared hit 对 shared read。大量 read 说明热数据没进缓存，或扫描面太大。

Seq Scan 不是原罪。小表、高选择度（条件能命中很大比例行）、或索引随机读在规划器看来更贵时，顺序扫整表更便宜。官方代价单位是相对的：默认 seq_page_cost = 1.0，random_page_cost = 4.0。看到 Seq Scan，先问「估计行数是否离谱」，再问「索引条件是不是根本没写进 Index Cond」。

Filter 和 Index Cond 不是一回事。条件出现在 Filter，表示扫完（或按别的键取出行）再过滤；出现在 Index Cond，才是索引真正收窄了扫描范围。

索引：类型、列顺序、INCLUDE、部分索引

默认 CREATE INDEX 是 btree，覆盖 =、<、>、BETWEEN、IN 以及能排序的范围。不要给普通等值列换别的类型。

btree：等值、范围、排序。email = $1、created_at >= $1 走这里。
GIN：倒排。数组包含、jsonb 包含、全文检索。写入比 btree 重，等值查找普通标量不该用它。
GiST：几何、范围类型重叠、最近邻 ORDER BY loc <-> point。不是「比 btree 更强的通用索引」。

复合 btree 最有效的情况：左边是等值，右边才是范围。(status, created_at) 适合 WHERE status = 'open' AND created_at >= $1。只写 WHERE created_at >= $1 时，左边没有等值约束，扫描面可能接近整棵索引。PostgreSQL 18 起 btree 有 skip scan：前导列区分度很低时，有可能对后列条件做多次定位。前导列区分度高，skip scan 帮不上，规划器仍可能改走 Seq Scan。列顺序按查询写，不按「感觉重要」。

覆盖索引用 INCLUDE，不要把载荷列塞进搜索键。官方例子是 Index-Only Scans：

        
CREATE INDEX orders_status_created_cover
  ON orders (status, created_at)
  INCLUDE (user_id, total_cents);

INCLUDE 列不参与搜索，也不参与 UNIQUE 约束。只有查询需要的列都在索引里，且可见性映射大多为 all-visible，才会变成 Index Only Scan。表改得很勤，heap 还是要回表，INCLUDE 只是把索引撑大。宽列不要 INCLUDE。GIN 不做 index-only scan。

部分索引把谓词写进索引定义，适合「绝大多数行是历史、查询只碰一小撮」：

        
CREATE INDEX orders_open_created_idx
  ON orders (created_at)
  WHERE status = 'open';

查询的 WHERE 必须能推出索引谓词，规划器才会用。WHERE status = 'open' AND created_at >= $1 能用；丢掉 status 条件就不能用。

索引在，计划不用

这是最常见的失败，不是「没建索引」。看见的样子：\d 里索引赫然在列，EXPLAIN 仍是 Seq Scan，或 pg_stat_user_indexes.idx_scan 长期为 0。

        
-- 失败形态：列上套了函数，btree 无法按原列匹配
CREATE INDEX users_email_idx ON users (email);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email
FROM users
WHERE lower(email) = '[email protected]';

计划里通常是 Seq Scan，条件落在 Filter，不是 Index Cond。怎么确认：同一条 EXPLAIN 看节点类型；再查使用计数：

        
        
        
    
SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 只说明自统计重置以来没用过，刚建的索引也会是 0。排除新索引之后，仍为 0 的大索引才值得问为什么。

其它同样导致「有索引不用」的写法：

类型对不上：bigint 列写成 WHERE id = '42' 有时还能隐式转换；WHERE id::text = $1 或在列上 CAST，索引对不上。
前导通配：email LIKE '%example.com'，btree 无法从左边锚定。LIKE 'ada%' 才可能走 btree。
选择度太高：条件能命中表的一大部分，随机回表比顺序读更贵。这是规划器在省 I/O，不是索引坏了。
统计过期：rows 估计和实际差很多。对表跑 ANALYZE，必要时 ALTER TABLE … ALTER COLUMN … SET STATISTICS。
复合索引用反了：索引是 (status, created_at)，查询只有 created_at，且 status 区分度高。

对应修法也窄：表达式查询建表达式索引 ON users (lower(email))；类型在常量侧转换，不要在列侧；范围查询把等值列放到索引左边。不要用 SET enable_seqscan = off 当生产配置，那只是验证「强制走索引会不会更慢」的调试开关。

膨胀和 autovacuum

UPDATE / DELETE 不会立刻把空间还给操作系统。死元组占着堆和索引，直到 VACUUM 回收。常规 VACUUM 把空间留给后续写入；VACUUM FULL 重建整表，拿 ACCESS EXCLUSIVE 锁，autovacuum 永远不会发出 FULL。

默认阈值大致是：死元组数超过 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples。scale_factor 默认 0.2。百万行的表要等大约 20% 行死后才真空，大表会先膨胀再清理。真正常动的旋钮：

按表下调 autovacuum_vacuum_scale_factor（例如热点表 0.02），不要全局抄一个「激进值」。
autovacuum_vacuum_cost_delay / autovacuum_vacuum_cost_limit：delay 太大，真空永远追不上写入；为了「别影响线上」把 vacuum 饿死，膨胀会反过来拖慢查询。
autovacuum_max_workers：多张大表同时达标时，默认 worker 会互相堵住。加 worker 要同步看 autovacuum_work_mem，内存按 worker 数乘。
log_autovacuum_min_duration：先看见真空跑了多久、跳过了哪些页，再谈调参。

不选：autovacuum = off。关掉之后还要自己保证每张表定期真空，否则会撞上事务 ID 回卷保护。静态大表即使很少更新，也仍需要冻结。

膨胀先看目录视图，不必先装扩展：

        
        
        
    
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze,
  seq_scan,
  idx_scan
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

n_dead_tup 相对 n_live_tup 长期很高、last_autovacuum 很旧，先查这个表的存储参数，而不是集群级把 shared_buffers 加大。

三个会误导的内存 / 代价参数

网文常给一套固定数字：shared_buffers = 25% RAM、work_mem = 64MB、random_page_cost = 1.1。官方文档给的是起点和相对关系，不是可抄的生产值。三者都依赖这台机器有多少 RAM、数据是否多半在缓存、磁盘是本地 SSD、网络盘还是机械盘。

shared_buffers。资源消耗写得很明确：专用库、内存 ≥ 1GB 时，25% 是合理起点；超过 40% 往往不如把页留给操作系统缓存。它是服务器自己的页面缓存，启动时才能改。太小：热页反复读盘。太大：挤占 OS cache 和 work_mem，checkpoint 写风暴要靠加大 max_wal_size 摊平。笔记本上和 128GB 专用库上，同一个百分比含义不同。

work_mem。默认 4MB。限制的是单个排序或哈希操作，不是每个连接一个额度。一条带多个 sort/hash 的 SQL 会乘几份；哈希操作还会再乘 hash_mem_multiplier（默认 2.0）。并发会话再乘一层。64MB 在 max_connections = 200 且复杂查询扎堆时，可以直接把机器打到换页。看见 EXPLAIN 里 Sort Method: external merge Disk，或 pg_stat_statements.temp_blks_written 很高，再考虑提高——优先对那条会话 SET work_mem，不要先改全局。

random_page_cost。规划器代价默认 4.0，相对 seq_page_cost = 1.0。调低会让规划器更偏爱索引扫描；调高则相反。SSD 上随机读和顺序读差距缩小，往 1.x 试是常见做法；数据几乎全在 RAM 时，两者可以接近。机械盘、缓存命中差、或网络盘延迟结构不同，不要照抄 SSD 的数。官方也写了：没有公认的「理想值」算法，按少数几次实验改代价常量风险很大。改完用同一条 EXPLAIN 看计划有没有从 Seq 变成 Index，而不是背 1.1。

effective_cache_size 只是规划器对「操作系统还能缓存多少」的假设，默认 4GB，不分配内存。把它设成 75% RAM 不会真的留出 75% RAM。

配置解决的是资源饥饿。它修不好「扫一千万行只为返回十二行」的 SQL。

累计耗时和锁等待

单次 EXPLAIN 看见的是这一条。线上要找的是累计把时间吃掉的语句。pg_stat_statements 需要写进 shared_preload_libraries 并重启，库内 CREATE EXTENSION pg_stat_statements。compute_query_id 保持 auto 或 on。

        
        
        
    
SELECT
  left(query, 80) AS query,
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 1) AS mean_ms,
  rows,
  shared_blks_read,
  temp_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

按 total_exec_time 排，不要只按 mean_exec_time。一条均时不高但每秒几千次的 SQL，累计会超过偶发的大查询。temp_blks_written 对上上一节的 work_mem。shared_blks_read 高而 calls 也高，先看是不是反复 Seq Scan。

锁等待看 pg_stat_activity 的 wait_event_type / wait_event，再用 pg_blocking_pids 对齐阻塞方：

        
        
        
    
SELECT
  blocked.pid AS blocked_pid,
  left(blocked.query, 80) AS blocked_query,
  blocking.pid AS blocking_pid,
  left(blocking.query, 80) AS blocking_query,
  blocked.wait_event_type,
  blocked.wait_event
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

长时间 Lock / LWLock，先查未提交的事务和显式 LOCK TABLE，而不是加索引。autovacuum 防回卷那次不会被普通冲突锁打断；常规真空会被冲突锁取消。会话挂着空闲事务，真空和查询会一起停。

计划对了再动配置。配置动了，用同一条 EXPLAIN (ANALYZE, BUFFERS) 核对，不要换一套新的网文数字。