PostgreSQL 教程: 使用 pg_stat_statements 分析查询

十月 20, 2023

摘要pg_stat_statements扩展提供了大量查询统计信息,这有助于查找需要优化的慢查询。在本教程中,我们将看到一些示例查询,查看来自pg_stat_statements的一些信息。

准备工作

为了运行本教程中讨论的视图或查询,我们应该已经成功配置了pg_stat_statements,将其创建为扩展。请注意,pg_stat_statements收集统计信息的每个查询,都可以通过queryid唯一标识。queryid是根据查询文本生成的哈希值,并且在我们查询pg_stat_statements视图时是可见的。

操作步骤

在以下命令中,我们使用一个函数将查询截断为仅 40 个字符,以获得更好的易读性。在查看统计信息时,我们可以使用\x进行扩展显示,并避免截断查询:

1. 查找写入临时块次数最多的查询:

select queryid, left(query,40), calls,
    temp_blks_read, temp_blks_written
  from pg_stat_statements
  order by temp_blks_written desc;

2. 查找读取临时块次数最多的查询:

select queryid, left(query,40), calls,
    temp_blks_read, temp_blks_written
  from pg_stat_statements
  order by temp_blks_read desc;

3. 查找因访问临时表从磁盘读取次数最多的查询:

select queryid, left(query,40), calls, local_blks_read,
    local_blks_read/calls as avg_read_per_call
  from pg_stat_statements
  order by local_blks_read desc;

4. 查找执行时间最长的查询:

select queryid, left(query,40), mean_time, max_time
  from pg_stat_statements
  where calls > 10
  order by mean_time desc, max_time desc;

5. 查找执行次数最多的查询:

select queryid, left(query,40), calls, mean_time
  from pg_stat_statements
  order by calls desc;

怎么做到的…

以下是pg_stat_statements视图中可用的列的列表。从这个视图,我们可以查询到很多对分析查询有用的信息:

rockdb=# \d pg_stat_statements
                    View "public.pg_stat_statements"
       Column        |       Type       | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
 userid              | oid              |           |          |
 dbid                | oid              |           |          |
 queryid             | bigint           |           |          |
 query               | text             |           |          |
 calls               | bigint           |           |          |
 total_time          | double precision |           |          |
 min_time            | double precision |           |          |
 max_time            | double precision |           |          |
 mean_time           | double precision |           |          |
 stddev_time         | double precision |           |          |
 rows                | bigint           |           |          |
 shared_blks_hit     | bigint           |           |          |
 shared_blks_read    | bigint           |           |          |
 shared_blks_dirtied | bigint           |           |          |
 shared_blks_written | bigint           |           |          |
 local_blks_hit      | bigint           |           |          |
 local_blks_read     | bigint           |           |          |
 local_blks_dirtied  | bigint           |           |          |
 local_blks_written  | bigint           |           |          |
 temp_blks_read      | bigint           |           |          |
 temp_blks_written   | bigint           |           |          |
 blk_read_time       | double precision |           |          |
 blk_write_time      | double precision |           |          |

本教程中讨论的一些查询是有特定用途的。然而,可以通过多种组合来构造更多这样的查询,以过滤经历某个阶段的查询,类似于获取生成更多临时块、从临时块读取、从磁盘读取但不从内存读取的查询列表、最长执行时间和调用(执行)次数最多的查询,如步骤 1 到 5 所示。在任何时间点,都可以使用以下函数调用,重置收集的所有统计信息:

rockdb=# select pg_stat_statements_reset();
 pg_stat_statements_reset
--------------------------

(1 row)