pg_statistic 表
- 概述
- pg_statistic 表
- SQL 语句解读
- pg_statistic 表属性解读
- pg_stats 视图和 pg_statistic 表的关系
声明：本文的部分内容参考了他人的文章。在编写过程中，我们尊重他人的知识产权和学术成果，力求遵循合理使用原则，并在适用的情况下注明引用来源。
本文主要参考了《PostgresSQL数据库内核分析》一书，OpenGauss1.1.0 的开源代码和《OpenGauss数据库源码解析》一书以及OpenGauss社区学习文档
概述
之前我们在文章【 OpenGauss源码学习 —— 列存储（analyze）(一)】中使用 SQL 语句：SELECT * FROM pg_statistic WHERE starelid = 'sales'::regclass;观察到了数据库表所收集到的统计信息。其中，pg_statistic 是 PostgreSQL 中的系统表，用于存储关于表列的统计信息。这些统计信息包括列中的值分布、唯一值的数量、空值的数量等等。本文我们则来详细的学习一下 pg_statistic 表。
pg_statistic 表
pg_statistic 表的主要作用是为查询优化器提供关于表列数据分布的信息，以便它可以生成更好的执行计划。具体来说，pg_statistic 表中的每一行都对应于一个表列的统计信息。这些统计信息对于 PostgreSQL 查询优化器来说非常重要，因为它可以帮助优化器决定如何访问表数据以获得最佳性能。例如，通过了解列中不同值的数量和数据分布，优化器可以选择合适的索引、连接顺序和连接方法，以及其他执行计划细节，从而提高查询的效率。
接下来，我们依旧按照【 OpenGauss源码学习 —— 列存储（analyze）(一)】中的案例进行分析。案例如下：
1. 创建列存储表，执行以下 SQL 语句：
CREATE TABLE sales (
sale_id SERIAL PRIMARY KEY,
product_id INT,
sale_date DATE,
quantity INT,
amount DECIMAL
) WITH (ORIENTATION = COLUMN);
postgres=# select * from sales;
sale_id | product_id | sale_date | quantity | amount
---------+------------+-----------+----------+--------
(0 rows)
postgres=# \d+ sales
Table "public.sales"
Column | Type | Modifiers | Storage | Stats target | Description
------------+--------------------------------+---------------------------------------------------------+---------+--------------+-------------
sale_id | integer | not null default nextval('sales_sale_id_seq'::regclass) | plain | |
product_id | integer | | plain | |
sale_date | timestamp(0) without time zone | | plain | |
quantity | integer | | plain | |
amount | numeric | | main | |
Has OIDs: no
Options: orientation=column, compression=low
2. 插入一些示例数据到列存储表中：
INSERT INTO sales (product_id, sale_date, quantity, amount)
VALUES
(101, '2023-08-01', 10, 100.00),
(102, '2023-08-02', 5, 50.00),
(101, '2023-08-03', 8, 80.00);
postgres=# select * from sales;
sale_id | product_id | sale_date | quantity | amount
---------+------------+---------------------+----------+--------
1 | 101 | 2023-08-01 00:00:00 | 10 | 100.00
2 | 102 | 2023-08-02 00:00:00 | 5 | 50.00
3 | 101 | 2023-08-03 00:00:00 | 8 | 80.00
(3 rows)
3. 执行 ANALYZE 进行统计信息收集：
postgres=# ANALYZE sales;
ANALYZE
4. 查看统计信息和优化计划：
查看表的统计信息，如不同列的值分布等：
postgres=# SELECT * FROM pg_statistic WHERE starelid = 'sales'::regclass::oid;
starelid | starelkind | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 |
staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1
| stavalues2 | stavalues3 | stavalues4 | stavalues5 | stadndistinct | staextinfo
----------+------------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+
--------+--------+--------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------
-----+------------+------------+------------+------------+---------------+------------
40980 | c | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 |
0 | 0 | 0 | | {1} | | | | {1,2,3}
| | | | | 0 |
40980 | c | 2 | f | 0 | 4 | -.666667 | 1 | 3 | 0 | 0 | 0 | 96 | 97 |
0 | 0 | 0 | {.666667} | {.5} | | | | {101}
| | | | | 0 |
40980 | c | 3 | f | 0 | 8 | -1 | 2 | 3 | 0 | 0 | 0 | 2062 | 2062 |
0 | 0 | 0 | | {1} | | | | {"2023-08-01 00:00:00","2023-08-02 00:00:00","2023-08-03 00:00:
00"} | | | | | 0 |
40980 | c | 4 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 |
0 | 0 | 0 | | {-.5} | | | | {5,8,10}
| | | | | 0 |
40980 | c | 5 | f | 0 | 11 | -1 | 2 | 3 | 0 | 0 | 0 | 1754 | 1754 |
0 | 0 | 0 | | {-.5} | | | | {50.00,80.00,100.00}
| | | | | 0 |
(5 rows)
查看查询的优化计划：
postgres=# EXPLAIN SELECT * FROM sales WHERE product_id = 101;
QUERY PLAN
---------------------------------------------------------------
Row Adapter (cost=3.01..3.01 rows=2 width=31)
-> CStore Scan on sales (cost=0.00..3.01 rows=2 width=31)
Filter: (product_id = 101)
(3 rows)
SQL 语句解读
首先，我们先来解读一下这句 SQL 语句：SELECT * FROM pg_statistic WHERE starelid = 'sales'::regclass;：
该语句的作用是从 PostgreSQL 系统目录中的 pg_statistic 表中检索统计信息。查询的条件是 starelid 字段等于 ‘sales’ 表的 OID（对象标识符）。具体解释如下：
- SELECT *: 查询语句的开头，表示要检索 pg_statistic 表中的所有列和所有行。
- FROM pg_statistic: 指定要查询的目标表是 pg_statistic，这是 PostgreSQL 中存储统计信息的系统表。
WHERE starelid = 'sales'::regclass::oid: 这是查询的过滤条件。它限制了结果集，只包括符合条件的行。具体来说：
- starelid 是 pg_statistic 表中的一个字段，它存储了统计信息所属的表的 OID（对象标识符）。OID 是 PostgreSQL 中的一种唯一标识符，用于识别数据库对象。
'sales'::regclass::oid是一个类型转换表达式，将表名 ‘sales’ 转换为 OID 类型。这是因为 starelid 存储的是表的 OID，而不是表名。因此，整个查询的目的是检索出与名为 “sales” 的列存储表相关联的统计信息，以供数据库管理员和开发人员用于性能优化和查询计划的生成。这些统计信息可以帮助数据库优化器更好地理解表中数据的分布，从而更好地选择执行查询的计划。
pg_statistic 表属性解读
pg_statistic 表中的每一列属性的含义如下：
| 属 性 | 含 义 |
|---|---|
| starelid | 与统计信息相关联的表的 OID（对象标识符）。 |
| starelkind | 关联的对象类型，通常是 ‘r’，表示表。 |
| staattnum | 与统计信息相关联的表的列的编号（从1开始）。 |
| stainherit | 一个布尔值，指示统计信息是否继承自父表（如果是分区表的一部分）。 |
| stanullfrac | NULL 值的分数比例，表示该列包含 NULL 值的比例。 |
| stawidth | 列的平均宽度（以字节为单位）。 |
| stadistinct | 不同的值的估计数量，表示该列的唯一值的数量估计。 |
| stakind1 到 stakind5 | 这些列包含有关统计信息种类的标志。它们可能包括：stakind1 = 1 表示直方图，stakind2 = 1 表示直方图的 B-tree，stakind3 = 1 表示多重模式，stakind4 = 1 表示单一模式，stakind5 = 1 表示单一数据值。 |
| staop1 到 staop5 | 这些列包含与统计信息相关的操作符的 OID（对象标识符）。 |
| stanumbers1 到 stanumbers5 | 这些列包含与统计信息相关的数值数据，通常是直方图的边界值。 |
| stavalues1 到 stavalues5 | 这些列包含与统计信息相关的值数据，通常是模式的集合。 |
| stadndistinct | 不同值的精确数量，通常在单一数据值模式的情况下使用。 |
| staextinfo | 附加信息，通常包含有关统计信息的额外信息。 |
pg_stats 视图和 pg_statistic 表的关系
pg_stats 视图（pg_catalog.pg_stats）是基于 pg_statistic 表的一个视图，提供了对表和列的统计信息的更友好的访问方式。pg_statistic 表存储了关于表和列的统计信息，如列的唯一值数量、NULL 值比例、直方图等。这些信息在查询优化期间用于生成最佳的查询执行计划。通常情况下，开发人员和管理员不会直接查询 pg_statistic 表，而是使用更易读的 pg_stats 视图。pg_stats 视图是对 pg_statistic 表的封装，提供了更方便的访问方式。它以表格形式呈现统计信息，每行代表一个列的统计信息。视图中的列名和内容更容易理解，因此更适合查询和分析。
我们执行以下 SQL 语句：
postgres=# select * from pg_stats where tablename='sales';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs |
histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+------------+-----------+-----------+-----------+------------+--------------+------------------+-------------------+--------------------
-------------------------------------------------+-------------+-------------------+------------------------+----------------------
public | sales | sale_id | f | 0 | 4 | -1 | 0 | | | {1,2,3}
| 1 | | |
public | sales | product_id | f | 0 | 4 | -.666667 | 0 | {101} | {.666667} |
| .5 | | |
public | sales | sale_date | f | 0 | 8 | -1 | 0 | | | {"2023-08-01 00:00:
00","2023-08-02 00:00:00","2023-08-03 00:00:00"} | 1 | | |
public | sales | quantity | f | 0 | 4 | -1 | 0 | | | {5,8,10}
| -.5 | | |
public | sales | amount | f | 0 | 11 | -1 | 0 | | | {50.00,80.00,100.00
} | -.5 | | |
(5 rows)
| 属 性 | 含 义 |
|---|---|
| schemaname | 模式名称，表示列所属的模式。 |
| tablename | 表名称，表示包含这个列的表的名称。 |
| attname | 列名称，表示列的名称。 |
| inherited | 一个布尔值，指示这个列是否是从父表继承而来的。如果是继承的列，该值为 true，否则为 false。 |
| null_frac | NULL 值比例，表示该列中包含的 NULL 值的比例。 |
| avg_width | 平均列宽度，表示该列的平均宽度（以字节为单位）。 |
| n_distinct | 唯一值的数量估计，表示该列中唯一值的数量的估算值。 |
| n_dndistinct： | 不重复的 NULL 值数量估计，表示该列中不同的 NULL 值的数量的估算值。 |
| most_common_vals | 最常见的值，一个数组，包含了该列中出现频率最高的值。 |
| most_common_freqs | 最常见值的频率，一个数组，包含了最常见值的频率。 |
| histogram_bounds | 直方图的边界值，一个数组，包含了直方图的边界值。直方图用于估算不同值的密度。 |
| correlation | 相关性估计，表示该列的数据分布与其他列之间的相关性。取值范围为 -1（完全负相关）到 1（完全正相关）。 |
| most_common_elems | 最常见的元素，一个数组，包含了该列中出现频率最高的元素。 |
| most_common_elem_freqs | 最常见元素的频率，一个数组，包含了最常见元素的频率。 |
| elem_count_histogram | 元素计数直方图，一个数组，包含了元素出现的频率。 |
以上查询结果显示了名为 “sales” 的表的列统计信息，每一行对应一个列。让我们一一解读这些信息：
- schemaname 和 tablename：这两列表示列所属的模式（schemaname）和表（tablename）的名称，都指向 “public” 模式下的 “sales” 表。
- attname：这是列的名称，分别为 “sale_id”、“product_id”、“sale_date”、“quantity” 和 “amount”。
- inherited：这一列显示了是否从父表继承了该列。所有这些列都没有从父表继承而来，所以值都为 “f”（假）。
- null_frac：表示列中 NULL 值的比例。在所有列中，NULL 值的比例都是 0，表示这些列没有 NULL 值。
- avg_width：这是列的平均宽度（以字节为单位）。不同列的平均宽度各不相同，分别为 4、4、8、4 和 11 字节。
- n_distinct：这列显示了唯一值的数量估计。其中，“sale_id”、“sale_date” 和 “quantity” 的唯一值数量估计为 -1，表示 PostgreSQL 无法准确估算唯一值的数量。而 “product_id” 和 “amount” 的唯一值数量估计分别为 -0.666667 和 -1，也表示无法准确估算。
- n_dndistinct：表示不同的 NULL 值的数量估计。在所有列中，这些值都是 0，表示没有不同的 NULL 值。
- most_common_vals 和 most_common_freqs：这两列分别表示最常见的值及其频率。例如，“product_id” 列中的最常见值是 101，频率为 0.666667。“sale_date” 列中包含了三个最常见的日期值，频率均为 1。
- histogram_bounds：这一列包含直方图的边界值。例如，“sale_id” 列有一个直方图，其中包含了三个边界值：1、2 和 3。
- correlation：表示列的数据分布与其他列之间的相关性。这些值介于 -1（完全负相关）和 1（完全正相关）之间。例如，“product_id” 列的相关性为 0.5，表示它与其他列之间存在一定程度的正相关性。
- most_common_elems 和 most_common_elem_freqs：这两列表示最常见的元素及其频率。在这个示例中，这些列都没有填充值。
- elem_count_histogram：这一列包含元素计数直方图，记录了元素出现的频率。在这个示例中，这些列都没有填充值。
