检查 PostgreSQL 中的统计目标 [英] Check statistics targets in PostgreSQL

问题描述

我已经搜索过，但找不到任何简单直接的答案.如何检查 ANALYZE 使用的当前统计目标?

I have searched but been unable to find any simple, straight forward answer to this. How do I check the current statistics targets used by ANALYZE?

推荐答案

统计目标的设置按列存储在目录表中 pg_attribute.你可以这样设置:

The setting for the statistics target is stored per column in the catalog table pg_attribute. You can set it like this:

ALTER TABLE myschama.mytable ALTER mycolumn SET STATISTICS 127;

然后像这样检查:

SELECT attstattarget
FROM   pg_attribute
WHERE  attrelid = 'myschama.mytable'::regclass
AND    attname = 'mycolumn';

或者你只是在 pgAdmin 的对象浏览器中查看创建脚本，如果该值与 default_statistics_target.

Or you just look at the creation script in the object browser of pgAdmin, where it is appended if the value is distinct from the default in default_statistics_target.

我引用了 attstattarget 的手册:

attstattarget 控制累积统计信息的详细程度通过 ANALYZE 为该列.零值表示没有统计应该收集.负值表示使用系统默认值统计目标.正值的确切含义是数据类型依赖.对于标量数据类型，attstattarget 既是最常见值"的目标数量收集，目标要创建的直方图箱数.

attstattarget controls the level of detail of statistics accumulated for this column by ANALYZE. A zero value indicates that no statistics should be collected. A negative value says to use the system default statistics target. The exact meaning of positive values is data type-dependent. For scalar data types, attstattarget is both the target number of "most common values" to collect, and the target number of histogram bins to create.

大胆强调我的.

普通索引列的统计信息与列统计信息相同，并且在统计表中没有单独的条目.但是 Postgres 为索引表达式收集单独的统计信息.这些可以以类似的方式进行调整:

Statistics for plain index columns are identical to column statistics and have no separate entries in statistics tables. But Postgres gathers separate statistics for index expressions. Those can be tweaked in a similar fashion:

ALTER INDEX myschema.myidx ALTER COLUMN 1 SET STATISTICS 128;

在没有实际列名的情况下，使用序号来寻址索引列，对应于pg_attribute.attnum:

In absence of actual column names, ordinal numbers are used to address index columns, which correspond to pg_attribute.attnum:

SELECT attstattarget
FROM   pg_attribute
WHERE  attrelid = 'myschama.myidx'::regclass
AND    attnum = 1;

该设置仅在下次手动或通过 autovacuum 运行 ANALYZE 时实际影响列统计信息.

The setting only actually affects column statistics the next time ANALYZE is being run manually or by autovacuum.

这篇关于检查 PostgreSQL 中的统计目标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！