PostgreSQL的系统视图pg

PostgreSQL的系统视图pg_stats

pg_stats 是 PostgreSQL 提供的一种系统视图，用于展示当前数据库中的统计信息。这些统计信息由数据库内部的自动统计过程通过 ANALYZE 命令收集，它们帮助查询规划器做出更好的执行决策，从而优化查询性能。

`pg_stats` 视图的结构

以下是 pg_stats 视图的主要列及其含义：

schemaname：表所在的模式名称。
tablename：表的名称。
attname：列的名称。
inherited：统计信息是否包含从继承表中继承的数据。
null_frac：列中空值的比例。
avg_width：列中其它值的平均宽度（以字节为单位）。
n_distinct：列中不同值的估计数量。
most_common_vals：最常见的值列表。
most_common_freqs：最常见值的频率列表。
histogram_bounds：直方图的边界值列表。
correlation：列值顺序与物理存储顺序的相关性。
most_common_elems: 最常见元素的数组（如果列为数组类型）。
most_common_elem_freqs: 最常见元素的频率数组（如果列为数组类型）。
elem_count_histogram: 数组大小的直方图（如果列为数组类型）。

查询 `pg_stats` 视图

可以使用以下 SQL 语句查询 pg_stats 视图，以获取某表或某列的统计信息：

查询特定表的统计信息

SELECT
    schemaname,
    tablename,
    attname,
    null_frac,
    avg_width,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    correlation
FROM
    pg_stats
WHERE
    tablename = 't1';

white=# SELECT
white-#     schemaname,
white-#     tablename,
white-#     attname,
white-#     null_frac,
white-#     avg_width,
white-#     n_distinct,
white-#     most_common_vals,
white-#     most_common_freqs,
white-#     correlation
white-# FROM
white-#     pg_stats
white-# WHERE
white-#     tablename = 't1';
 schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | correlation 
------------+-----------+---------+-----------+-----------+------------+------------------+-------------------+-------------
 yewu1      | t1        | id      |         0 |         4 |         -1 |                  |                   |           1
 yewu1      | t1        | name    |         0 |        10 |         -1 |                  |                   |  0.81865406
(2 rows)

white=#

分析与调优

通过 pg_stats 视图提供的信息，可以进行以下分析和调优：

查询规划：
- 查询规划器利用统计信息来选择最优的查询执行计划。了解一列的 most_common_vals 和 most_common_freqs 可以帮助你理解查询执行中可能遇到的性能问题。
索引优化：
- correlation 值高的列说明它们和存储顺序有很高的相关性，这些列如果用于查询很常见，则可以考虑基于此列创建索引以加快查询速度。
表设计：
- null_frac 和 avg_width 可以帮助优化表设计，减少 NULL 值的使用，选择合适的数据类型以节省存储空间。
调优统计信息：
- 如果查询计划器未能选择最佳的查询计划，可以使用 ANALYZE 命令手动更新统计信息，从而提高查询效率：
```
ANALYZE t1;
```