兄弟，PostgreSQL 9.5持续占80%-90%CPU确实头疼，你已经排查了长查询和索引，那咱们从其他几个方向深挖试试：

一、先从系统层面定位进程细节 #

• 用htop或者top -H看PostgreSQL的线程/进程：PostgreSQL是多进程模型，每个连接对应一个postgres进程，你可以先确认是单个进程独占CPU，还是多个进程分散消耗。配合ps aux | grep postgres能列出所有postgres进程，找到占用最高的那个PID。
• 用perf做函数级采样分析：这是Linux下最直接的性能分析工具，执行perf top -p <你的高CPUpostgres进程PID>，能实时看到这个进程内部哪些函数在吃CPU——比如如果是ExecScan这类函数占比高，可能是全表扫描多；如果是hashjoin相关，可能是哈希连接消耗大，一目了然。

二、深挖PostgreSQL内部统计视图 #

• 启用并查询pg_stat_statements：这个扩展是排查SQL CPU消耗的神器，先确认postgresql.conf里shared_preload_libraries包含pg_stat_statements，重启后创建扩展CREATE EXTENSION pg_stat_statements;。然后执行：

  SELECT queryid, query, total_time, calls, mean_time, cpu_time 
  FROM pg_stat_statements 
  ORDER BY cpu_time DESC LIMIT 20;
  

  重点看cpu_time和calls，有些SQL单次执行快，但调用频率极高，累计CPU就会拉满，你之前看pg_stat_activity可能没抓到（因为单次执行完就结束了）。
• 检查全表扫描情况：即使建了索引，也可能因为统计信息过时、索引不匹配导致PostgreSQL选全表扫描。查pg_stat_user_tables：

  SELECT relname, seq_scan, idx_scan, n_live_tup 
  FROM pg_stat_user_tables 
  ORDER BY seq_scan DESC LIMIT 10;
  

  如果某个表seq_scan远大于idx_scan，先执行ANALYZE <表名>更新统计信息，再看查询计划有没有变化。
• 排查锁竞争：虽然锁一般导致等待，但大量进程抢锁会引发频繁上下文切换，也会耗CPU。查pg_locks：

  SELECT locktype, relation::regclass, mode, granted, pid 
  FROM pg_locks 
  WHERE NOT granted;
  

  看有没有大量等待锁的进程，尤其是排他锁、共享锁的竞争。

三、检查PostgreSQL配置参数合理性 #

• 调整work_mem：如果work_mem设置太小，复杂查询的排序、哈希连接会频繁用磁盘临时文件，虽然主要是IO，但大量这类操作会让CPU跟着升高。可以先查pg_stat_activity里有没有waiting状态的进程，或者看pg_stat_bgwriter的temp_files、temp_bytes指标，如果数值很大，说明临时文件用得多，适当调大work_mem（比如从默认的4M调到16M或32M，别调太大避免内存不足）。
• 核对shared_buffers：这个参数是PostgreSQL的共享缓冲区，太小会导致频繁磁盘IO，CPU也会因为处理缓存置换和IO等待变高。9.5版本建议设置为系统内存的25%-40%，比如16G内存的机器设4G-6G，同时要留够内存给系统和其他服务。
• 控制max_connections：连接数过多会导致进程上下文切换频繁，CPU被消耗在切换上。用SELECT count(*) FROM pg_stat_activity;看当前连接数，如果接近max_connections，考虑用连接池（比如pgBouncer）减少实际进程数。

四、排查特殊场景 #

• 检查自动VACUUM：9.5的autovacuum如果处理大表或者频繁更新的表，会占用不少CPU。查pg_stat_user_tables的last_autovacuum、vacuum_count，或者用ps aux | grep autovacuum看有没有正在运行的autovacuum进程。如果是autovacuum导致的，可以临时调整autovacuum_vacuum_threshold等参数降低频率，或者手动VACUUM大表后再观察。
• 检查自定义函数/存储过程：如果有PL/pgSQL或者其他语言写的复杂函数，被频繁调用的话也会吃CPU。查pg_stat_user_functions：

  SELECT funcname, calls, total_time, cpu_time 
  FROM pg_stat_user_functions 
  ORDER BY cpu_time DESC LIMIT 10;
  

  看哪些函数累计CPU时间高，优化函数逻辑或者减少调用次数。
• 排查第三方扩展：比如PostGIS这类扩展，处理空间数据时可能消耗大量CPU，检查有没有扩展相关的进程在高负载运行。

五、日志分析 #

• 开启慢查询日志：临时修改postgresql.conf，设置log_min_duration_statement = 0（记录所有SQL）、log_statement = 'all'，重启生效后观察日志，能抓到所有执行的SQL，包括那些单次快但高频的查询。排查完记得改回原设置，避免日志爆炸。
• 检查错误日志：看看PostgreSQL日志里有没有索引损坏、统计信息缺失、配置错误这类警告，这些也可能间接导致CPU升高。

内容的提问来源于stack exchange，提问作者user565