执行SQL语句时,如果出现慢SQL或SQL占用系统内存的情况,需进行具体查询分析。

查询慢SQL

查询慢SQL语句分为以下两种情况:
  • 查看历史慢SQL语句:
    通过观察pg_stat_statements视图,执行时间较长的SQL语句。
    SELECT total_time / calls AS  avg, query 
    FROM pg_stat_statements ORDER BY avg DESC LIMIT 10;
  • 查看当前慢SQL语句。
    查询执行时间超过1秒的SQL:
    select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '1 s' order by query_start ;

使用EXPLAIN查询SQL计划

通过使用EXPLAIN查询SQL计划,可分析SQL的快慢或资源占用情况。

使用EXPLAIN执行以下语句,可获得SQL的查询计划。具体语法示例如下:
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    BUFFERS [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }
上述示例对应的命令参数选项含义如下:
参数 说明
ANALYZE 会实际执行SQL,并获得相应的查询计划,默认为FALSE。如果优化一些修改数据的SQL 需要真实的执行但是不能影响现有的数据,可以放在一个事务中,分析完成后可以直接回滚。
VERBOSE 会显示查询计划的附加信息,默认为FALSE。附加信息包括查询计划中每个节点输出的列(Output),表的SCHEMA信息,函数的SCHEMA 信息,表达式中列所属表的别名,被触发的触发器名称等。
COSTS 会显示每个计划节点的预估启动代价(找到第一个符合条件的结果的代价)和总代价,以及预估行数和每行宽度,默认为TRUE。
BUFFERS 会显示关于缓存的使用信息,默认为FALSE。该参数只能与ANALYZE 参数一起使用。缓冲区信息包括共享块(常规表或者索引块)、本地块(临时表或者索引块)和临时块(排序或者哈希等涉及到的短期存在的数据块)的命中块数,更新块数,挤出块数。
FORMAT 指定输出格式,默认为TEXT。各个格式输出的内容都是相同的,其中XML | JSON | YAML更有利于您通过程序解析SQL语句的查询计划。

视图分析

可以通过观察视图pg_stat_statementspolar_stat_sql进行分析SQL执行需占用的资源情况,这两个视图需创建相关插件,并打开开关。
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION polar_stat_sql;

postgres=# alter system set polar_stat_sql.enable_stat=on;
ALTER SYSTEM
postgres=# alter system set polar_stat_sql.enable_qps_monitor=on;
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
  • pg_stat_statements视图说明请参见pg_stat_statements
  • polar_stat_sql视图说明。

    sql级别监控试图信息包含4个部分内容:

    • 执行计划节点统计信息(扫描计划节点、join计划节点、聚集计划节点、排序计划节点、hash计划节点)。
    • 收集sql级别的CPU运行时间(分为系统态和用户态)、IO(读写字节数)、memory(请求页数,交换页数)、ipc(ipc message接受量和请求量)、CPU上下文切换(分为自愿切换和非自愿)。
    • SQL的各个执行阶段耗时统计,包括parse时间、analyze时间、rewrite时间、执行计划生成时间、执行时间。
    • latch和锁信息统计。
    polar_stat_sql视图是记录的累计数据,在执行sql之前需进行清空。
    postgres=# select polar_stat_sql_reset();
     polar_stat_sql_reset 
    ----------------------
     
    (1 row)