在 Azure Database for PostgreSQL - 单一服务器上优化查询统计信息集合Optimize query statistics collection on an Azure Database for PostgreSQL - Single Server

本文介绍如何在 Azure Database for PostgreSQL 服务器上优化查询统计信息集合。This article describes how to optimize query statistics collection on an Azure Database for PostgreSQL server.

使用 pg_stats_statementsUse pg_stats_statements

Pg_stat_statements 是 PostgreSQL 扩展,默认情况下在 Azure Database for PostgreSQL 中启用 。Pg_stat_statements is a PostgreSQL extension that's enabled by default in Azure Database for PostgreSQL. 该扩展提供了一种方法来跟踪服务器执行的所有 SQL 语句的执行统计信息。The extension provides a means to track execution statistics for all SQL statements executed by a server. 此模块会挂接到每个查询执行,并且性能成本较高。This module hooks into every query execution and comes with a non-trivial performance cost. 启用 pg_stat_statements 会强制将查询文本写入到磁盘上的文件。Enabling pg_stat_statements forces query text writes to files on disk.

如果拥有含长查询文本的唯一查询,或者未主动监视 pg_stat_statements,请禁用 pg_stat_statements,以提供最佳性能 。If you have unique queries with long query text or you don't actively monitor pg_stat_statements , disable pg_stat_statements for best performance. 为此,请将设置更改为 pg_stat_statements.track = NONETo do so, change the setting to pg_stat_statements.track = NONE.

禁用 pg_stat_statements 时,部分工作负荷的性能可实现高达 50% 的提升 。Some customer workloads have seen up to a 50 percent performance improvement when pg_stat_statements is disabled. 禁用 pg_stat_statements 的代价是无法对性能问题进行故障排除。The tradeoff you make when you disable pg_stat_statements is the inability to troubleshoot performance issues.

若要设置 pg_stat_statements.track = NONE,请执行以下操作:To set pg_stat_statements.track = NONE:

使用查询存储Use the Query Store

Azure Database for PostgreSQL 中的查询存储功能提供了用于跟踪查询统计信息的更高效的方法。The Query Store feature in Azure Database for PostgreSQL provides a more effective method to track query statistics. 建议使用此功能作为使用 pg_stats_statements 的替代方法 。We recommend this feature as an alternative to using pg_stats_statements .

后续步骤Next steps

请考虑在 Azure 门户中或通过 Azure CLI 来设置 pg_stat_statements.track = NONEConsider setting pg_stat_statements.track = NONE in the Azure portal or by using the Azure CLI.

有关详细信息,请参阅:For more information, see: