PostgreSQL 查询性能分析-未完纪

应用在数据库中存取数据时，需要监控哪些查询表现良好，哪些表现不佳。随着数据不断添加和修改，应用也随需求变化而演进，因此这项工作应该持续、频繁地进行。

pg_stat_statements 是跟踪查询统计信息的 PostgreSQL 扩展，对于发现数据库结构或查询的优化机会非常有帮助。

启用扩展

使用前，需要先启用扩展。原文交互教程中的 PostgreSQL 窗口已经完成配置，无需重复执行。

在 Postgres 服务器的 postgresql.conf 中设置 shared_preload_libraries = 'pg_stat_statements'。
连接数据库，运行：

 CREATE EXTENSION pg_stat_statements;

扩展显示

pg_stat_statements 包含大量信息。如果使用 PostgreSQL 标准命令行客户端 psql，先开启结果的扩展显示：

\x on

查看全部信息

最简单的查询可以查看启用了扩展的各数据库的大量统计信息：

 SELECT * FROM pg_stat_statements;

按数据库查看

查找性能不佳的查询时，通常希望关注针对某一个数据库执行的查询。因此，可以连接 pg_database，以便筛选。

下面的查询只选取少量列，显示数据库名称，以及所有数据库中执行时间最高的前五条查询：

 SELECT
  d.datname, s.total_exec_time, s.calls, s.rows,
  s.query
FROM pg_stat_statements s JOIN pg_database d ON (s.dbid = d.oid)
ORDER BY total_exec_time DESC
LIMIT 5;

找出最耗时的查询

如果想找出占用数据库大量时间的查询，该怎么办？

total_exec_time 表示该语句累计执行时间，单位为毫秒，精度很高。下面将它四舍五入到两位小数。

查询返回累计执行时间、调用次数和行数，并计算每条查询的平均时间，以及原文称为“CPU 百分比”的累计执行时间占比：

 SELECT
  d.datname, round(s.total_exec_time::numeric, 2) AS total_exec_time, s.calls, s.rows,
  round(s.total_exec_time::numeric / calls, 2) AS avg_time,
  round((100 * s.total_exec_time / sum(s.total_exec_time::numeric) OVER ())::numeric, 2) AS percentage_cpu,
  substring(s.query, 1, 50) AS short_query
FROM pg_stat_statements s JOIN pg_database d ON (s.dbid = d.oid)
ORDER BY percentage_cpu DESC
LIMIT 5;

得到表现最差的查询列表后，可以分别通过 EXPLAIN ANALYZE 深入分析。它会显示 Postgres 实际使用的执行计划，帮助判断可以在哪些地方改进。

平均查询执行时间

所有数据库中全部查询的平均执行时间是多少？

 SELECT (sum(total_exec_time) / sum(calls))::numeric(6,3) AS avg_execution_time
FROM pg_stat_statements;

对 shared_buffers 写入最多的查询

下面找出对 shared_buffers 写入最多的前五条查询。shared_buffers 是 PostgreSQL 的共享内存区域。这些查询使大量共享内存块变脏，因此可能存在改进空间。

 SELECT query, shared_blks_dirtied
FROM pg_stat_statements
WHERE shared_blks_dirtied > 0
ORDER BY 2 desc
LIMIT 5;

可能需要索引的表

pg_stat_statements 可以告诉我们哪些查询执行次数多、耗时长。再结合 pg_stat_user_tables 和 pg_stat_user_indexes 等统计视图，就可以获得更多性能优化线索。

查询表现不佳，常常是因为缺少帮助数据库定位目标行的索引。下面的查询可以指出可能缺少索引的表：

 SELECT relname, seq_scan - idx_scan AS too_much_seq,
  CASE WHEN seq_scan - idx_scan > 0 THEN 'Missing Index?' ELSE 'OK' END,
  pg_relation_size(relid) AS rel_size, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE schemaname <> 'information_schema' AND schemaname NOT LIKE 'pg%'
ORDER BY too_much_seq DESC;

    relname   | too_much_seq |      case      | rel_size | seq_scan | idx_scan
-------------+--------------+----------------+----------+----------+----------
 dependents  |           41 | Missing Index? |   139264 |       41 |        0
 countries   |           -3 | OK             |     8192 |        1 |        4
 locations   |           -3 | OK             |     8192 |        1 |        4
 regions     |          -24 | OK             |     8192 |        1 |       25
 departments |         -927 | OK             |     8192 |       81 |     1008
 employees   |        -3469 | OK             |   106496 |      161 |     3630

dependents 表的顺序扫描次数比索引扫描多 41 次。根据最近执行的查询，它可能需要一个索引。

但必须理解，索引也有成本，会减慢写入，因为新增或修改数据时，数据库还需要更新索引。因此，不应当直接为表的全部列都添加索引。