PostgreSQL 新增log_min_duration_sample参数用来控制日志记录的慢SQL百分比，发行说明如下。

发行说明

Allow a sample of statements to be logged (Adrien Nayrat)
A log_statement_sample_rate ratio of statements taking over log_min_duration_sample duration will be logged.

PostgreSQL 12 已提供了log_statement_sample_rate 参数控制日志记录的慢SQL百分比，之前写了篇博客可参考: PostgreSQL 12: 新增 log_statement_sample_rate 参数控制数据库日志中慢SQL百分比。

但 PostgreSQL 13版本这个参数的定义与12版本有差异，使得对慢查询的抽样记录策略有变化，先来看看手册中这几个参数的说明。

Log_statement_sample_rate

PostgreSQL 12版本参数定义，如下:

1
2

log_statement_sample_rate (real)
Determines the fraction of statements that exceed log_min_duration_statement to be logged. The default is 1.0, meaning log all such statements. Setting this to zero disables logging by duration, same as setting log_min_duration_statement to -1. log_statement_sample_rate is helpful when the traffic is too high to log all queries.

PostgreSQL 13版本参数定义，如下:

1
2

log_statement_sample_rate (floating point)
Determines the fraction of statements with duration exceeding log_min_duration_sample that will be logged. Sampling is stochastic, for example 0.5 means there is statistically one chance in two that any given statement will be logged. The default is 1.0, meaning to log all sampled statements. Setting this to zero disables sampled statement-duration logging, the same as setting log_min_duration_sample to -1. Only superusers can change this setting.

从以上看出:

PostgreSQL 12: 执行时间超出 log_min_duration_statement 的慢查询将被抽样到日志中。
PostgreSQL 13: 执行时间超出 log_min_duration_sample 的慢查询将被抽样到日志中，此参数的说明详见以下。

Log_min_duration_sample

PostgreSQL 13 版本新增 log_min_duration_sample参数，手册说明如下:

log_min_duration_sample (integer)
Allows sampling the duration of completed statements that ran for at least the specified amount of time. This produces the same kind of log entries as log_min_duration_statement, but only for a subset of the executed statements, with sample rate controlled by log_statement_sample_rate. For example, if you set it to 100ms then all SQL statements that run 100ms or longer will be considered for sampling. Enabling this parameter can be helpful when the traffic is too high to log all queries. If this value is specified without units, it is taken as milliseconds. Setting this to zero samples all statement durations. -1 (the default) disables sampling statement durations. Only superusers can change this setting.

This setting has lower priority than log_min_duration_statement, meaning that statements with durations exceeding log_min_duration_statement are not subject to sampling and are always logged.

Other notes for log_min_duration_statement apply also to this setting.

这个参数用来控制慢查询的执行时间阀值，超出这个参数设置值的慢查询将根据 log_statement_sample_rate 抽样到日志中。

13版本与12版本慢查询抽样策略差异

PostgreSQL 12日志中慢查询抽样策略，如下:

PostgreSQL 13日志中慢查询抽样策略，如下:

通过上图说明应该很容易理解12版本与13版本在抽样策略的差异。

13 版本中这两个参数需配合使用，如下:

1 2	log_min_duration_sample - minimum required statement duration log_statement_sample_rate - sample rate (0.0 - 1.0)

举例说明

场景一: PostgreSQL 12版本

1 2	log_min_duration_statement = 100ms log_statement_sample_rate = 0.2

超出100ms的慢查询将被抽样记录到日志中，抽样率为20%。

场景二: PostgreSQL 13版本

1 2	log_min_duration_sample = 100ms log_statement_sample_rate = 0.2

超出100ms的慢查询将被抽样记录到日志中，抽样率为20%，场景二和场景一的抽样策略是相同的。

场景三: PostgreSQL 13版本

1
2
3

log_min_duration_statement = 500ms
log_min_duration_sample = 100
log_statement_sample_rate = 0.2

大于100ms且小于500ms的慢查询将被抽样记录到日志中，抽样率为20%，大于或等于500ms的慢查询全部记录。

总结

PostgreSQL 13版本log_min_duration_sample参数的定义与 12 版本有差异，使得对慢查询的抽样记录策略有变化，本文详细介绍了这两个参数的差异，在数据库运维过程中需注意。