【PostgreSQL】PostgreSQL扩展：pg_stat_statements 优化SQL

【PostgreSQL】PostgreSQL扩展：pg_stat_statements 优化SQL

扩展能够扩展，更改和推进Postgres的行为。怎么样？通过挂钩到低级的Postgres API挂钩。可以水平扩展Postgres的开源Citus数据库本身是作为PostgreSQL扩展实现的，这使Citus可以与Postgres版本保持最新，而不会像其他Postgres fork那样落后。尽管我想更深入地研究最有用的Postgres扩展：pg_stat_statements，但我之前已经写过各种扩展类型。

你看，我刚从FOSDEM回来。FOSDEM是在布鲁塞尔举行的年度免费开源软件会议，在活动中，我在PostgreSQL开发室中发表了有关Postgres扩展的演讲。到今天结束时，Postgres开发室中进行的一半以上的讨论都提到了pg_stat_statements：

如果您使用Postgres，但尚未使用pg_stat_statements，则必须将其添加到工具箱中。而且，即使您很熟悉，也可能值得重温。

pg_stat_statements入门

Pg_stat_statements是所谓的contrib扩展名，可以在PostgreSQL发行版的contrib目录中找到。这意味着它已经随Postgres一起提供了，您无需从源代码构建它或安装软件包。如果尚未启用数据库，则可能必须启用它。这很简单：

CREATE EXTENSION pg_stat_statements;

如果您在主要的云提供商上运行，则很有可能他们已经为您安装并启用了它。

一旦安装了pg_stat_statements，它就会开始悄悄地在后台运行。Pg_stat_statements记录针对您的数据库运行的查询，从中删除一些变量，然后保存有关该查询的数据，例如花费了多长时间以及基础读/写发生了什么。

注意：它不会保存每个查询，而是对其进行参数化，然后保存汇总结果

让我们来看几个示例。假设我们执行以下查询：

SELECT order_details.qty, order_details.item_id, order_details.item_price FROM order_details, customers WHERE customers.id = order_details.customer_id AND customers.email = 'craig@citusdata.com'

它将查询转换为：

SELECT order_details.qty, order_details.item_id, order_details.item_price FROM order_details, customers WHERE customers.id = order_details.customer_id AND customers.email = '?'

如果这是我在应用程序中经常执行的查询，以获取诸如零售订单历史记录之类的订单详细信息，那么它不会节省我为每个用户运行该订单的频率，而是节省了汇总视图。

看数据

从这里我们可以查询pg_stat_statements的原始数据，我们将看到类似以下内容：

SELECT * FROM pg_stat_statements; userid | 16384 dbid | 16388 query | select * from users where email = ?; calls | 2 total_time | 0.000268 rows | 2 shared_blks_hit | 16 shared_blks_read | 0 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 ...

使用pg_stat_statements提取见解

现在，这里有大量有价值的信息，作为高级用户，有时它们都可以证明是有价值的。但是，即使没有开始理解数据库的内部结构，您仍然可以通过以某些方式查询pg_stat_statements来获得一些真正强大的见解。通过查看total_time和每个查询被调用一次的次数，我们可以非常快速地查看哪些查询经常运行以及它们平均消耗了多少：

SELECT (total_time / 1000 / 60) as total, (total_time/calls) as avg, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100;

您可以通过多种不同的方式对此进行过滤和排序，您可能只想关注运行1000多次的查询。或平均超过100毫秒的查询。上面的查询向我们显示了数据库消耗的总时间（以分钟为单位）以及平均时间（以毫秒为单位）。通过上面的查询，我会得到类似以下内容的信息：

total | avg | query --------+--------+------------------------- 295.76 | 10.13 | SELECT id FROM users... 219.13 | 80.24 | SELECT * FROM ... (2 rows)

根据经验，我知道快速获取记录时，PostgreSQL应该能够在1ms内返回。鉴于此，我可以开始优化工作。在上面的内容中，我看到将第一个查询降低到1ms会有所改善，但是优化第二个查询将对整个系统的性能产生更大的提升。

特别说明：如果要构建多租户应用，则可能不希望pg_stat_statements参数化tenant_id。为了解决这个问题，我们构建了cit

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。