2023年7月3日发(作者：)

PostgreSQL如何查找TOPSQL⼀、安装pg_stat_statements如果您使⽤的是云数据库，跳过安装，到create extension 部分。pg_stat_statements是PostgreSQL的核⼼插件之⼀。可以在编译PostgreSQL时安装，也可以单独安装。编译时安装make world

make install-world

单独安装cd src/contrib/pg_stat_statements/

make; make install

⼆、加载pg_stat_statements模块vi $PGDATA/

shared_preload_libraries='pg_stat_statements'

如果要跟踪IO消耗的时间，还需要打开如下参数track_io_timing = on

设置单条SQL的最长长度，超过被截断显⽰（可选）track_activity_query_size = 2048

三、配置pg_stat_statements采样参数vi $PGDATA/

pg_stat_ = 10000 # 在pg_stat_statements中最多保留多少条统计信息，通过LRU算法，覆盖⽼的记录。

pg_stat_ = all # all - (所有SQL包括函数内嵌套的SQL), top - 直接执⾏的SQL(函数内的sql不被跟踪), none - (不跟踪)

pg_stat__utility = off # 是否跟踪⾮DML语句 (例如DDL，DCL)， on表⽰跟踪, off表⽰不跟踪

pg_stat_ = on # 重启后是否保留统计信息

重启数据库pg_ctl restart -m fast

四、创建pg_stat_statements extension在需要查询TOP SQL的数据库中，创建extensioncreate extension pg_stat_statements;

如果您使⽤的是阿⾥云RDS PPAS数据库，请使⽤管理函数创建这个插件。 举例： 1 创建插件 dblink select rds_manage_extension('create','dblink'); 2 删除插件 dblink select rds_manage_extension('drop','dblink');五、分析TOP SQLpg_stat_statements输出内容介绍查询pg_stat_statements视图，可以得到统计信息。SQL语句中的⼀些过滤条件在pg_stat_statements中会被替换成变量，减少重复显⽰的问题。pg_stat_statements视图包含了⼀些重要的信息，例如：SQL的调⽤次数，总耗时，最快/慢执⾏时间，平均执⾏时间，执⾏时间的⽅差（看出抖动），总共扫描或返回或处理了多少⾏shared buffer的使⽤情况，命中，未命中，产⽣脏块，驱逐脏块local buffer的使⽤情况，命中，未命中，产⽣脏块，驱逐脏块temp buffer的使⽤情况，读了多少脏块，驱逐脏块数据块的读写时间NameuseriddbidqueryidquerycallsTypeoidoidbiginttextbigintdoubleprecisiondoubleprecisiondoubleprecisiondoubleprecisiondoubleprecisionbigintbigintbigintReferencespg__---DescriptionOID of user who executed the statementOID of database in which the statement was executedInternal hash code, computed from the statement's parse treeText of a representative statementNumber of times executedtotal_time-Total time spent in the statement, in millisecondsmin_time-Minimum time spent in the statement, in millisecondsmax_time-Maximum time spent in the statement, in millisecondsmean_time-Mean time spent in the statement, in millisecondsstddev_time-Population standard deviation of time spent in the statement, in millisecondsrowsshared_blks_hitshared_blks_read---Total number of rows retrieved or affected by the statementTotal number of shared block cache hits by the statementTotal number of shared blocks read by the statementshared_blks_dirtiedshared_blks_writtenlocal_blks_hitlocal_blks_readlocal_blks_dirtiedbigintbigintbigintbigintbigint-----Total number of shared blocks dirtied by the statementTotal number of shared blocks written by the statementTotal number of local block cache hits by the statementTotal number of local blocks read by the statementTotal number of local blocks dirtied by the statementlocal_blks_writtenNametemp_blks_readtemp_blks_writtenbigintTypebigintbigintdoubleprecisiondoubleprecision-References--Total number of local blocks written by the statementDescriptionTotal number of temp blocks read by the statementTotal number of temp blocks written by the statementTotal time the statement spent reading blocks, in milliseconds (if track_io_timingis enabled, otherwise zero)Total time the statement spent writing blocks, in milliseconds (if track_io_timingis enabled, otherwise zero)blk_read_time-blk_write_time-

最耗IO SQL平均最耗IO SQL TOP 5select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;

总最耗IO SQL TOP 5select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;

最耗时 SQL平均最耗时 SQL TOP 5select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;

总最耗时 SQL TOP 5select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5;

响应时间抖动最严重 SQLselect userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;

最耗共享内存 SQLselect userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;

最耗临时空间 SQLselect userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;

六、重置统计信息pg_stat_statements是累积的统计，如果要查看某个时间段的统计，需要打快照，建议参考⽤户也可以调⽤如下SQL定期清理历史的统计信息select pg_stat_statements_reset();

参考