【PostgreSQL内核学习（十六）—— （pg
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
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-treestakind3 = 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这一列包含元素计数直方图记录了元素出现的频率。在这个示例中这些列都没有填充值。
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |