查找最耗费资源的SQL（Top SQL）

什么是Top SQL？

Top SQL是指在特定时间段内消耗系统资源（CPU、IO、内存）最多或执行频率最高的SQL语句。快速且准确地定位Top SQL是数据库性能优化的关键阶段。

前提条件

Top SQL分析通常依赖pg_stat_statements插件，该插件用于收集SQL语句的执行统计信息。

安装pg_stat_statements插件，请参见使用pg_stat_statements插件。

Top SQL的最佳排查时机

在排查性能问题时，“什么时候查”决定了能够获取的数据是否有效。

表1 Top SQL的最佳排查时机
排查时机	适用场景	推荐工具/方法
业务高峰期/故障发生时	在数据库卡顿、CPU飙升、业务响应慢场景，需立刻定位当前“谁在占用资源”。	pg_stat_activity（查正在运行的进程）
日常巡检/低负载期	在分析长期累积的性能瓶颈，优化全量SQL逻辑的场景，适合分析“哪些SQL历史累计耗时最长”。	pg_stat_statements（查历史统计视图）
定期快照对比	用于对比两个时间点之间的差异，定位新增劣化SQL。	定时导出pg_stat_statements数据对比

Top SQL定位查询模板

以下为针对不同资源维度的查询模板。这些查询基于pg_stat_statements历史视图，已针对PostgreSQL 14及以上版本调整了列名兼容性。

找出最消耗总时间的SQL（CPU飙升）

该类SQL通常是性能优化的首选对象，因为占用时间越长，优化后的收益越大。

-- PostgreSQL 13及更低版本 
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; 
-- PostgreSQL 14及更高版本 
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;

找出最消耗IO的SQL（磁盘瓶颈）

该类SQL涉及大量的数据块读写，通常意味着缺少索引或存在全表扫描。

-- PostgreSQL 13及更低版本 
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time + blk_write_time) DESC LIMIT 5; 
-- PostgreSQL 14及更高版本 
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (shared_blk_read_time + local_blk_read_time + temp_blk_read_time + shared_blk_write_time + local_blk_write_time + temp_blk_write_time) DESC LIMIT 5;

找出单次执行最慢的SQL（长尾查询）

有些SQL虽然总耗时不高（total_exec_time 小），但平均每次执行都很慢，可能会导致前端接口超时。

-- PostgreSQL 13及更低版本
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 5; 
-- PostgreSQL 14及更高版本 
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;

找出执行频率最高的SQL（高频微操）
有些SQL单次执行仅需0.1ms，但每秒执行数千次，累积消耗大量CPU和资源。
```
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY calls DESC LIMIT 5;
```
结合实时状态排查（pg_stat_activity）
当系统当前响应极慢时，pg_stat_statements可能数据滞后。此时应直接查看正在执行的SQL。
```
SELECT pid, usename, datname, state, wait_event_type, wait_event, query, age(clock_timestamp(), query_start) AS duration FROM pg_stat_activity WHERE state != 'idle' AND query NOT LIKE '%pg_stat_activity%' ORDER BY query_start ASC; 
```
wait_event：若显示IO:DataFileRead，说明正在读磁盘（缺索引）；若显示Lock:transactionid，说明有锁冲突。