当 PostgreSQL 查询占用 CPU 很高时，找出是谁在吃 CPU

找出「是谁」在吃 CPU

查看当前正在跑的高 CPU 查询

SELECT
pid,
usename,
datname,
state,
now() - query_start AS running_time,
wait_event_type,
wait_event,
query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY running_time DESC;

重点关注： running_time 很长的 query 是否是复杂 SELECT 是否反复出现同一条 SQL

看耗 CPU 的 SQL

如果开启了 pg_stat_statements

查总 CPU 消耗最高：

SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

查单次最慢：

SELECT
query,
max_exec_time,
calls
FROM pg_stat_statements
ORDER BY max_exec_time DESC
LIMIT 10;

查调用次数最多

SELECT
query,
calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

检查连接数

SELECT count(*) FROM pg_stat_activity;

看当前正在跑的 SQL

SELECT
pid,
now() - query_start AS runtime,
state,
query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY runtime DESC;

系统确认 CPU 高 ≠ I/O 高

SELECT
datname,
blks_read,
blks_hit
FROM pg_stat_database;

blks_hit 高 → CPU 计算型 blks_read 高 → I/O 型问题

是否有 autovacuum 问题

SELECT relname, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

死元组多： 查询变慢 CPU 增高

启用 pg_stat_statements 扩展

docker-compose

services:
  db_postgres:
    image: postgres:18
    command:
      - "postgres"
      - "-c"
      - "shared_preload_libraries=pg_stat_statements"
      - "-c"
      - "pg_stat_statements.max=10000"
      - "-c"
      - "pg_stat_statements.track=all"

或者修改 /var/lib/postgresql/data/postgresql.conf 加入：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

在数据库里创建扩展

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

验证是否成功:

SELECT count(*) FROM pg_stat_statements;

如果有数字结果 → 成功

-- =====================================
-- 📊 PostgreSQL Top 10 SQL 分析模板
-- 时间单位：秒 (s)
-- =====================================

SELECT
query,
calls AS execution_count,
total_exec_time / 1000.0 AS total_time_sec,      -- 总耗时（秒）
mean_exec_time / 1000.0 AS avg_time_sec,         -- 平均耗时（秒）
max_exec_time / 1000.0 AS max_time_sec,          -- 单次最大耗时（秒）
rows AS total_rows_returned
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

字段解释
字段	含义
query	SQL 文本
execution_count	SQL 被调用次数
total_time_sec	SQL 总耗时，秒为单位
avg_time_sec	SQL 平均执行时间，秒为单位
max_time_sec	SQL 单次最大耗时，秒为单位
total_rows_returned	SQL 总返回行数

优化建议

建立索引可有效应对大部分的耗时查询

通过 PostgreSQL 内置的系统视图或 \d 命令查看已经创建的索引

\d 表名

检查 work_mem

如果聚合 + 排序占用 CPU 高，可以调大： SET work_mem = '64MB';

定期 ANALYZE

ANALYZE 表名;