PostgreSQL 性能排查

PostgreSQL 性能问题常见根因不是“没有索引”这么简单，还包括统计信息不准、表膨胀、索引膨胀、VACUUM 不及时、长事务、连接过多、锁等待。

学习目标

学完本页，你要能做到：

目标	能力
看懂慢 SQL	会用 EXPLAIN (ANALYZE, BUFFERS) 判断慢在扫描、排序、Join、锁还是 IO
看懂估算错误	能解释 rows 估算和真实行数差距为什么会让优化器选错计划
看懂 VACUUM 问题	知道 dead tuple、表膨胀、索引膨胀和长事务的关系
会排查锁	能用 pg_stat_activity、pg_locks 找阻塞源
会商业优化	能处理资产列表、医疗采集任务抢占、报表分页、JSONB 查询、批量导入等常见问题
会面试回答	能把执行计划、统计信息、MVCC、VACUUM、锁等待、连接池串成一条排查链

PostgreSQL 慢在哪里

一次查询从客户端到结果返回，可以拆成这些阶段：

flowchart TD
    A["客户端发 SQL"] --> B["连接进程接收请求"]
    B --> C["解析 SQL"]
    C --> D["重写查询"]
    D --> E["优化器生成执行计划"]
    E --> F["执行器访问表和索引"]
    F --> G{"数据页是否在内存"}
    G -- "在 shared buffers" --> H["直接读取内存页"]
    G -- "不在内存" --> I["从磁盘读取数据页"]
    H --> J["排序 / Join / 聚合"]
    I --> J
    J --> K["返回结果给客户端"]

慢 SQL 的根因通常落在四类：

类别	典型表现	本质
访问太多数据	Seq Scan、Rows Removed by Filter 很大	没用上合适索引，或者业务确实查太多
计划选错	估算 rows 和实际行数差很多	统计信息过期、数据倾斜、相关列缺少扩展统计
中间计算重	Sort Method: external merge Disk、Hash 落盘	排序、聚合、Join 内存不够或 SQL 形态不合理
等待资源	SQL 单独跑很快，并发时卡住	锁等待、IO 饱和、连接过多、VACUUM 被长事务阻塞

所以排查顺序不能只问“有没有索引”，而要先确认慢在执行计划还是慢在等待。

排查流程

flowchart TD
    A["接口慢"] --> B["确认 SQL 和耗时"]
    B --> C["EXPLAIN ANALYZE"]
    C --> D{"扫描是否过多"}
    D -- "是" --> E["索引 / 统计信息 / SQL 改写"]
    D -- "否" --> F{"是否锁等待"}
    F -- "是" --> G["查 pg_locks / pg_stat_activity"]
    F -- "否" --> H{"是否膨胀或 VACUUM 问题"}
    H -- "是" --> I["查 dead tuples / vacuum"]
    H -- "否" --> J["查连接池 / IO / CPU"]

第一步：拿到真实 SQL 和真实参数

生产里很多慢 SQL 不是 SQL 模板慢，而是某组参数慢。例如同一个资产列表查询：

select id, asset_no, hospital_code, status, created_at
from asset_info
where tenant_id = $1
  and hospital_code = $2
  and status = $3
order by created_at desc
limit 20 offset 20000;

tenant_id=1 可能只有 1000 行，tenant_id=9 可能有 3000 万行。只看模板不看参数，会误判。

建议先做三件事：

动作	目的
从应用日志拿 SQL、参数、耗时、traceId	确认是哪条调用慢
从 pg_stat_statements 看总耗时和调用次数	判断是单次慢还是总量大
用相同参数在影子库或只读库 EXPLAIN ANALYZE	得到真实计划和真实行数

启用 pg_stat_statements 后常用查询：

select query,
       calls,
       round(total_exec_time::numeric, 2) as total_ms,
       round(mean_exec_time::numeric, 2) as mean_ms,
       rows
from pg_stat_statements
order by total_exec_time desc
limit 20;

如果一个 SQL 单次只 20ms，但每天调用 500 万次，它也是优化目标；如果一个 SQL 平均 5 秒但每天只跑一次，要看是否影响核心链路。

第二步：看 EXPLAIN ANALYZE

PostgreSQL 推荐这样看真实执行计划：

explain (analyze, buffers, verbose)
select id, asset_no
from asset_info
where tenant_id = 1001
  and status = 'ONLINE'
order by created_at desc
limit 20;

关键字段不要背，要理解它们怎么来的：

字段	含义	怎么判断问题
cost	优化器估算成本，不是毫秒	只用于比较计划，不等于真实耗时
rows	优化器估算行数	和 actual rows 差很多，说明统计信息或数据分布有问题
actual time	实际开始/结束耗时	判断哪个节点最耗时
loops	节点执行次数	Nested Loop 内层 loops 很大要警惕
Buffers: shared hit	命中内存页	高说明主要从 shared buffers 读
Buffers: shared read	从磁盘读页	高说明 IO 压力或缓存未命中
Rows Removed by Filter	扫描后过滤掉的行	很大说明扫描了大量无效数据
Sort Method	排序方式	external merge Disk 说明排序落盘

示例：慢计划可能长这样：

Limit  (cost=125000.00..125000.05 rows=20 width=48)
  (actual time=1860.215..1860.224 rows=20 loops=1)
  Buffers: shared hit=1024 read=180000
  ->  Sort
        Sort Key: created_at DESC
        Sort Method: external merge Disk: 256MB
        ->  Seq Scan on asset_info
              Filter: ((tenant_id = 1001) and (status = 'ONLINE'))
              Rows Removed by Filter: 9800000

这说明：

1. 扫描了大量无效行，Rows Removed by Filter 很高。
2. 读了很多磁盘页，shared read 很高。
3. 结果还要排序，且排序落盘。
4. 这不是“机器差”，而是索引和查询形态有问题。

更合适的索引可能是：

create index idx_asset_tenant_status_created
on asset_info (tenant_id, status, created_at desc);

优化后的计划目标是：

Index Scan using idx_asset_tenant_status_created
只扫描满足 tenant_id/status 的前若干行
按 created_at desc 天然有序
不再全表扫描和大排序

第三步：理解统计信息为什么会让计划选错

PostgreSQL 优化器不是“实际跑一遍再决定计划”，而是根据统计信息估算。统计信息来自 ANALYZE。

flowchart TD
    A["表中真实数据分布"] --> B["ANALYZE 抽样"]
    B --> C["pg_statistic 保存统计信息"]
    C --> D["优化器估算过滤后行数"]
    D --> E["选择 Seq Scan / Index Scan / Join 算法"]

如果统计信息过期或无法表达真实数据分布，优化器就可能选错。

常见例子：

场景	为什么估错
数据倾斜	status='ONLINE' 占 99%，status='DELETED' 占 1%，平均估算不准
多列相关	province 和 city 强相关，单列统计无法表达组合关系
新导入大量数据	表变了，但统计信息没更新
临时表/中间表	没有及时 ANALYZE

基础处理：

analyze asset_info;

多列相关时可以考虑扩展统计：

create statistics st_asset_tenant_status
on tenant_id, status
from asset_info;

analyze asset_info;

这不是“玄学调参”。优化器要回答的是“过滤后大概剩多少行”。估算错了，Join 顺序、索引选择、排序策略都会错。

常用视图

视图	用途
pg_stat_activity	当前连接、SQL、等待状态
pg_locks	锁信息
pg_stat_user_tables	表扫描、dead tuple、vacuum 信息
pg_stat_user_indexes	索引使用情况

查看长事务：

select pid, usename, state, now() - xact_start as xact_age, query
from pg_stat_activity
where xact_start is not null
order by xact_age desc;

查看表 dead tuple：

select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
from pg_stat_user_tables
order by n_dead_tup desc;

查阻塞关系：

select blocked.pid as blocked_pid,
       blocked.query as blocked_query,
       blocking.pid as blocking_pid,
       blocking.query as blocking_query,
       now() - blocked.query_start as blocked_duration
from pg_stat_activity blocked
join pg_locks blocked_locks
  on blocked.pid = blocked_locks.pid
join pg_locks blocking_locks
  on blocking_locks.locktype = blocked_locks.locktype
 and blocking_locks.database is not distinct from blocked_locks.database
 and blocking_locks.relation is not distinct from blocked_locks.relation
 and blocking_locks.page is not distinct from blocked_locks.page
 and blocking_locks.tuple is not distinct from blocked_locks.tuple
 and blocking_locks.transactionid is not distinct from blocked_locks.transactionid
 and blocking_locks.classid is not distinct from blocked_locks.classid
 and blocking_locks.objid is not distinct from blocked_locks.objid
 and blocking_locks.objsubid is not distinct from blocked_locks.objsubid
 and blocking_locks.pid <> blocked_locks.pid
join pg_stat_activity blocking
  on blocking.pid = blocking_locks.pid
where not blocked_locks.granted
  and blocking_locks.granted;

这段 SQL 的作用是：找出“谁被阻塞”和“谁正在阻塞别人”。生产排查锁等待时，它比盲目重启服务可靠得多。

慢 SQL 常见原因

原因	表现
Seq Scan 过多	没有合适索引或返回数据太多
估算错误	估算 rows 和 actual rows 差很多
表膨胀	扫描页数变多
锁等待	SQL 本身不慢，并发时卡住
连接过多	进程和内存压力大
临时文件	排序、Hash、聚合内存不够落盘

商业场景一：资产列表深分页很慢

错误写法：

select id, asset_no, created_at
from asset_info
where tenant_id = 1001
order by created_at desc
limit 20 offset 200000;

offset 200000 的含义不是“直接跳到第 200001 行”。数据库通常要先找到前 200020 行，再丢掉前 200000 行。页数越深，浪费越多。

更适合核心列表的游标翻页：

select id, asset_no, created_at
from asset_info
where tenant_id = 1001
  and (created_at, id) < (timestamp '2026-07-06 10:00:00', 9000001)
order by created_at desc, id desc
limit 20;

配套索引：

create index idx_asset_tenant_created_id
on asset_info (tenant_id, created_at desc, id desc);

为什么加 id？因为 created_at 可能重复，只用时间翻页会漏数据或重复数据。created_at + id 形成稳定排序锚点。

商业场景二：医疗采集任务抢占卡住

任务表常见设计：

create table collect_task (
  id bigserial primary key,
  hospital_code varchar(32) not null,
  status varchar(20) not null,
  priority int not null default 0,
  created_at timestamp not null default now()
);

多个采集执行器抢任务，如果直接：

select id
from collect_task
where status = 'WAITING'
order by priority desc, created_at
limit 10;

然后再 update，多个执行器可能抢到同一批任务，或者互相锁等待。PostgreSQL 常用 for update skip locked：

with picked as (
  select id
  from collect_task
  where status = 'WAITING'
  order by priority desc, created_at
  limit 10
  for update skip locked
)
update collect_task t
set status = 'RUNNING'
from picked
where t.id = picked.id
returning t.id;

原理：

flowchart TD
    A["执行器 A 锁住前 10 个任务"] --> B["执行器 B 扫描任务"]
    B --> C{"遇到已锁任务"}
    C -- "普通 for update" --> D["等待锁释放"]
    C -- "skip locked" --> E["跳过已锁任务"]
    E --> F["继续领取后面的任务"]

配套索引：

create index idx_collect_waiting_pick
on collect_task (status, priority desc, created_at, id);

这个场景体现了：性能问题不只是 SQL 快不快，还包括并发下锁等待是否可控。

商业场景三：JSONB 查询慢

如果资产扩展属性放在 jsonb：

create table asset_ext (
  id bigserial primary key,
  attrs jsonb not null
);

查询：

select id
from asset_ext
where attrs @> '{"deviceType":"CT"}';

可以使用 GIN 索引：

create index idx_asset_ext_attrs_gin
on asset_ext using gin(attrs);

但不要误以为 JSONB 可以替代所有关系建模：

字段类型	建议
高频过滤、Join、排序字段	拆成普通列并建 B-tree 索引
灵活扩展、低频检索字段	可以放 JSONB
强约束字段	用普通列、外键、唯一约束

如果把 tenant_id、status、created_at 都塞进 JSONB，后续列表、统计、权限过滤会非常难优化。

VACUUM、表膨胀和慢查询

PostgreSQL 的 MVCC 更新不是原地覆盖旧行，而是生成新 tuple，旧 tuple 等待后续 VACUUM 清理。

flowchart TD
    A["update 一行数据"] --> B["生成新 tuple"]
    B --> C["旧 tuple 变成 dead tuple"]
    C --> D{"是否有长事务还可能看见旧版本"}
    D -- "有" --> E["VACUUM 不能清理"]
    D -- "没有" --> F["VACUUM 标记空间可复用"]
    E --> G["dead tuple 堆积"]
    G --> H["表和索引膨胀"]
    H --> I["扫描更多页面，查询变慢"]

这就是 PostgreSQL 和 MySQL 一个很大的运维差异：PostgreSQL 要特别关注 VACUUM、长事务和膨胀。

查看自动 vacuum 压力：

select relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_pct,
       last_autovacuum,
       autovacuum_count
from pg_stat_user_tables
order by n_dead_tup desc
limit 20;

处理建议：

问题	处理
长事务阻塞清理	找出长事务，推动应用提交或终止异常会话
高频更新表 dead tuple 高	调整 autovacuum 参数，降低触发阈值
表已经严重膨胀	评估 VACUUM FULL、pg_repack 或重建表
索引膨胀	REINDEX CONCURRENTLY 或 pg_repack

注意：VACUUM FULL 会重写表并需要强锁，生产使用要非常谨慎。很多在线系统更倾向用 pg_repack 这类在线重整方案。

连接过多为什么会慢

PostgreSQL 是多进程模型，一个连接通常对应一个后端进程。连接太多不是“并发能力更强”，反而会导致：

1. 进程上下文切换增加。
2. 每个连接占用内存。
3. 数据库同时处理太多活跃 SQL，CPU/IO 被打满。
4. 应用连接池过大，让数据库雪上加霜。

排查：

select state, count(*)
from pg_stat_activity
group by state
order by count(*) desc;

商业项目常见策略：

场景	建议
Java 应用	HikariCP 连接池不要盲目配很大，要按数据库能力和接口耗时估算
微服务数量很多	考虑 PgBouncer 做连接池复用
报表和核心业务混跑	报表走只读库或单独资源池
大量 idle in transaction	必须修应用，空闲事务会阻塞 VACUUM

优化动作

1. 用 EXPLAIN ANALYZE 看真实计划。
2. 用 ANALYZE 更新统计信息。
3. 给高频过滤和排序条件建合适索引。
4. 处理长事务，保证 VACUUM 能清理旧版本。
5. 大表使用分区或归档。
6. 使用连接池控制连接数。

生产排查清单

现象	优先检查	常见处理
某接口突然慢	锁等待、执行计划变化、数据库资源	查 pg_stat_activity、pg_locks、近期发布
SQL 一直慢	执行计划、索引、统计信息	建/改索引，ANALYZE，改写 SQL
白天慢晚上快	并发、锁、IO、连接数	限流、连接池、拆报表、错峰任务
更新多的表越来越慢	dead tuple、膨胀、autovacuum	调 autovacuum，清长事务，重整表
排序聚合慢	work_mem、临时文件、SQL 形态	优化索引、减少结果集、谨慎调整内存
从库查询旧数据	复制延迟	读写一致性策略，不强一致就不要读从库

面试追问：PostgreSQL 为什么 VACUUM 很重要

标准回答不能只说“清垃圾”。更完整的回答是：

PostgreSQL 的 MVCC 更新会生成新 tuple，旧 tuple 在没有事务可见后才可以被清理。VACUUM 负责标记旧 tuple 空间可复用，并维护可见性信息。如果长事务一直存在，VACUUM 不能清理它可能还需要看到的旧版本，dead tuple 会堆积，表和索引膨胀，查询需要扫描更多页面，性能下降。因此 PostgreSQL 性能治理必须关注长事务、autovacuum、dead tuple、膨胀和索引重建。

面试标准回答

PostgreSQL 性能排查要先拿到慢 SQL，用 EXPLAIN ANALYZE 看真实执行计划，关注 Seq Scan、Index Scan、估算行数和实际行数差异、排序和临时文件。然后检查是否锁等待、是否有长事务、dead tuple 是否很多、VACUUM 是否正常。PostgreSQL 更新会产生旧版本，如果 VACUUM 跟不上会导致表和索引膨胀。连接过多也会带来进程和内存压力，生产常使用 PgBouncer 控制连接。