# 查找最耗费资源的SQL（Top SQL）
#### 什么是Top SQL？
Top SQL是指在特定时间段内消耗系统资源（CPU、IO、内存）最多或执行频率最高的SQL语句。快速且准确地定位Top SQL是数据库性能优化的关键阶段。
#### 前提条件
Top SQL分析通常依赖pg_stat_statements插件，该插件用于收集SQL语句的执行统计信息。
安装pg_stat_statements插件，请参见[使用pg_stat_statements插件](https://support.huaweicloud.com/usermanual-rds-pg/rds_09_0066.html)。
#### Top SQL的最佳排查时机
在排查性能问题时，"什么时候查"决定了能够获取的数据是否有效。
表1Top 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**，说明有锁冲突。
  
 
#### 重置统计信息
pg_stat_statements记录的数据具有累积性。您可以运行以下命令定期重置计数器，以便重新采集统计信息。
```
SELECT pg_stat_statements_reset();
```