PostgreSQL的系统视图pg_stats

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 视图提供的信息，可以进行以下分析和调优：

1. 查询规划：

   • 查询规划器利用统计信息来选择最优的查询执行计划。了解一列的 most_common_vals 和 most_common_freqs 可以帮助你理解查询执行中可能遇到的性能问题。

2. 索引优化：

   • correlation 值高的列说明它们和存储顺序有很高的相关性，这些列如果用于查询很常见，则可以考虑基于此列创建索引以加快查询速度。

3. 表设计：

   • null_frac 和 avg_width 可以帮助优化表设计，减少 NULL 值的使用，选择合适的数据类型以节省存储空间。

4. 调优统计信息：

   • 如果查询计划器未能选择最佳的查询计划，可以使用 ANALYZE 命令手动更新统计信息，从而提高查询效率：

   ANALYZE t1;
   

小结

通过 pg_stats 视图，PostgreSQL 提供了表和列的详细统计信息。这些统计信息对于数据库查询优化、索引设计和性能调优非常有帮助。了解并合理利用这些统计信息，可以帮助你更好地管理和优化 PostgreSQL 数据库的性能。

通过定期检查 pg_stats 视图并根据需要调整表设计和索引，可以显著提高数据库的查询性能和整体运行效率。