Postgres中具有pg_stat_statements的查询优化

＆＃34;我想整天优化所有查询，因为这绝对值得您花时间和精力，＆＃34;希望从未有人说过这句话。因此，当涉及到查询优化时，您应该如何选择战斗？幸运的是，在PostgreSQL中，我们有一种方法可以在系统范围内查看数据库查询：

pg_stat_statements是自8.4版本以来在PostgreSQL中一直存在的扩展。经过数年的发展（特别是从9.2版开始），它已成为一种有用的工具。因此，我们认为现在和在Crunchy博客上突出显示它一样好。

从技术上讲，您可以将Postgres设置为记录对数据库的所有调用，但是如果您对可以为您提供更多概述的内容感兴趣，则pg_stat_statements可以使您轻松访问查询统计信息。它不会阻止失控的查询使您的系统陷入瘫痪，但这可能是对Postgres性能工具包的简单而重要的补充。您无需成为专家即可开始使用此扩展并从中获得价值。让我们快速旋转一下。

pg_stat_statements包含在contrib模块中，因此它与标准Postgres一起提供，但可能不会自动启用：

如果您使用的是诸如Crunchy Bridge之类的Postgres托管系统，则pg_stat_statements可能已经添加到postgresql.conf中。 （提示：运行SHOW shared_preload_libraries;进行检查。）在这种情况下，您可以跳到下一步。

重新登录到Postgres。在要访问pg_stat_statements的数据库中，运行：

就是这样，您可以开始使用它了。您确实需要在要访问扩展名的每个数据库中启用扩展名，但是pg_stat_statements实际上从一开始就跟踪同一台服务器中的所有数据库。

pg_stat_statements给我们一个包含聚合查询统计信息的视图。我使用的是运行PG 13的Crunchy Bridge实例，这是描述视图时的结果：

\ d pg_stat_statements视图＆＃34; public.pg_stat_statements＆＃34;专栏类型整理|可空|默认值---------------------- + ------------------ + -------- --- + ---------- + --------- userid | oid | | | dbid | oid | | | queryid | bigint | | |查询|文字| | |计划| bigint | | | total_plan_time |双精度| | | min_plan_time |双精度| | | max_plan_time |双精度| | | mean_plan_time |双精度| | | stddev_plan_time |双精度| | |电话| bigint | | | total_exec_time |双精度| | | min_exec_time |双精度| | | max_exec_time |双精度| | | mean_exec_time |双精度| | | stddev_exec_time |双精度| | |行| bigint | | | ... blk_read_time |双精度| | | blk_write_time |双精度| | | wal_records | bigint | | | wal_fpi | bigint | | | wal_bytes |数字| | |

我们将稍后重新介绍其中的一些属性，但现在让我们开始学习。我创建了一个新的Crunchy Bridge集群，并且由于已经添加了pg_stat_statements，因此我真正需要做的只是要做的就是运行CREATE EXTENSION。然后我们可以查询pg_stat_statements视图，就像这样：

SELECT（total_exec_time / 1000/60）作为total_min，mean_exec_time作为avg_ms，调用，从pg_stat_statements查询ORDER BY 1 DESC LIMIT 500；

total_exec_time和mean_exec_time以毫秒为单位，并且call是查询已运行的次数。 min_exec_time，max_exec_time和stddev_exec_time也可用。您还可以查看查询返回的累积行总数。您可以通过多种方法将pg_stat_statements中的信息切成薄片并将其切成小块。

您可能已经注意到，除了执行之外，还包括查询计划的统计信息。这些是PG 13的新增功能，可以提供更多有用的见解（例如，与执行相比，计划花费的时间明显更长）。

如果您想知道pg_stat_statements是否还记录针对视图本身发出的查询：是的，默认情况下会记录该查询。

您还可以进行手动重置，以清除到目前为止收集的统计信息：

在您最终进行了一些调整（例如添加索引）之后，可以这样做。或者，您可能已对数据库架构进行了一些更改。无论哪种方式，即使您进行了定期重置，您也仍然可以通过拍摄视图快照（例如保存查询输出）来跨时间进行比较。

创建表付款（由默认身份标识生成的ID整数主键，日期时间戳记非空默认时间CURRENT_TIMESTAMP，类型为文本非空检查（类型为（＆＃39; Event＆＃39 ;,＆＃39; Package＆＃39;））， status TEXT CHECK（（（Waive＆＃39;，＆＃39; Refund＆＃39;）中的状态）），client_id INTEGER NOT NULL REFERENCES client（id），event_id INTEGER REFERENCES event（id），金额NUMERIC（8， 2）NOT NULL，余额NUMERIC（8,2）NOT NULL）；在付款中插入（日期，类型，client_id，event_id，金额，余额）值（＆＃39; 2020-01-10 13:00 PST＆＃39; ，＆＃39; Event＆＃39;，1、4、20.00、0.00）；

-[记录12] --------------------------------------------- ------------------------ total_min | 0.00022264500000000002avg_ms | 13.3587个呼叫| 1查询|创建表付款（由默认身份标识生成的ID整数主键，日期时间戳记非空缺省时间CURRENT_TIMESTAMP，类型为文本非空检查（类型为（＆＃39; Event＆＃39 ;，＆＃39; Package＆＃39;））， status TEXT CHECK（（（Waive＆＃39;，＆＃39; Refund＆＃39;）中的状态）），client_id INTEGER NOT NULL REFERENCES client（id），event_id INTEGER REFERENCES event（id），金额NUMERIC（8， 2）NOT NULL，余额NUMERIC（8,2）NOT NULL）...- [RECORD 50] --------------------------- ------------------------------------------ total_min | 6.687333333333334e-05avg_ms | 4.0124电话| 1查询|插入付款（日期，类型，client_id，event_id，金额，余额）VALUES（$ 1，$ 2，$ 3，$ 4，$ 5，$ 6）

注意区别吗？ CREATE TABLE查询按原样保存，＆＃34;但是INSERT删除了文字值，并替换为参数符号（$ 1等）。 （您可能还会看到这被称为查询的“规范化形式”。）这允许在集合中评估语义上等效的查询。也就是说，如果我要使用相同的语法在付款表中运行另一个插入，我们确实希望将其视为相同的操作，但是不必担心我们会插入不同的值。

我的同事Greg Smith的提示：如果要使用块的读写时间（blk_read_time和blk_write_time）统计信息，则还需要打开postgresql.conf中的track_io_timing参数。如果您希望pg_stat_statements可以提供所有功能，则建议这样做。默认情况下它是禁用的，因为在某些系统上计时时间很慢。

要检查系统中的计时器有多快，请使用pg_test_timing实用程序。对于大多数硬件而言，收集所有这些时序数据的开销很低。

我尚未启用track_io_timing，所以我们现在集中在更简单的示例上。请留意我们的博客上将来深入探讨该主题。

我将pg_stat_statements描述为使您可以总体了解查询统计信息。哪些属性最重要以及要设置的阈值可能取决于数据库的使用方式和要求。但是，一旦您能够对某些特定查询进行归零，您可能想要做的下一件事就是使用EXPLAIN（ANALYZE）分别进行挖掘。然后，您也许可以通过创建新索引来继续优化这些查询。那只是一个例子，但我希望这有助于说明pg_stat_statements如何成为提高数据库性能的便捷工具。

要记住的一件事是查询文本保存在外部磁盘文件中，并且不占用共享内存。 （摘自官方文档）。 pg_stat_statements应该只在您的系统上留下相对较小的占用空间，尤其是与记录所有内容相比。也就是说，您还可以确保在pg_stat_statements.max上设置一个较低的阈值，或仅使用pg_stat_statements.track参数设置要跟踪的某些类型的语句。

pg_stat_statements使您免于解析所有日志的工作，以使您了解查询的最新情况。对于那些不熟悉该扩展程序的人，希望这会鼓励您开始研究。在这里的同时，我们还在Crunchy Learning Portal中开设了关于EXPLAIN（ANALYZE）的课程，对于那些你们中的一个也想旋转一下。享受！