postgresql_internals-14 学习笔记(六)—— 统计信息

2023-11-22 18:10

本文主要是介绍postgresql_internals-14 学习笔记(六)—— 统计信息,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

不完全来自这本书,把查到的和之前的文章重新汇总整理了一把。

一、 统计信息的收集

 1. 主要参数

其中最主要的是track_counts,开启才会收集统计信息。

postgres=# select name,setting,short_desc,context from pg_settings where name like 'track%'; name            | setting |                          short_desc                          |  context   
---------------------------+---------+--------------------------------------------------------------+------------track_activities          | on      | Collects information about executing commands.               | superusertrack_activity_query_size | 1024    | Sets the size reserved for pg_stat_activity.query, in bytes. | postmastertrack_commit_timestamp    | off     | Collects transaction commit time.                            | postmastertrack_counts              | on      | Collects statistics on database activity.                    | superusertrack_functions           | none    | Collects function-level statistics on database activity.     | superusertrack_io_timing           | off     | Collects timing statistics for database I/O activity.        | superusertrack_wal_io_timing       | off     | Collects timing statistics for WAL I/O activity.             | superuser

2. 相关进程

  • pg 15之前,由stats collector进程负责统计信息收集

       之前看文档这个进程的启动跟 /etc/hosts 配置还有关系,设置错误会导致进程起不来,可以参考:故障:autovacuum和stats collector进程未正常启动 - 墨天轮

  • pg 15取消了stats collector进程

       统计信息由放在临时文件改为放至共享内存,并在停库前由检查点进程写到文件系统

PostgreSQL 15: Stats Collector Gone? What's New?

3. 自动收集

由autovacuum触发。触发条件:

  • autovacuum_analyze_threshold:表被修改行数阈值,默认50
  • autovacuum_analyze_scale_factor:表被修改行数比例,默认0.1
  • 计算公式:pg_stat_all_tables.n_mod_since_analyze (自上次analyze以来被修改的行数)> autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × pg_class.reltuples

       它也有对应普通表的表级同名参数,可以针对各表调整。toast表无需收集统计信息,因此没有针对它的参数。

postgresql_internals-14 学习笔记(二)常规vacuum_Hehuyi_In的博客-CSDN博客

4. 手动收集

analyze [verbose] [table[(column[,..])]]

  • verbose:显示收集进度
  • table:要收集的表名,如果不指定,则收集当前数据库中所有表的统计信息
  • column:要收集的列名,如果不指定,则收集所有字段的统计信息
  • analyze命令对表加4级锁,不阻塞写

5. 默认抽样数

300×default_statistics_target参数(默认100)

/* Default statistics target (GUC parameter) */
int			default_statistics_target = 100;

       为了调整所收集的统计信息的准确度,可以增大随机抽样比例,这个参数可以在session级别设置,也可以在列级别设置。

set default_statistics_target to xxx;
alter table tab_name alter column col_name set statistics xxx;

       

二、 基础统计信息

基础统计信息保存在pg_class中,主要是下面3项:

  • reltuples:表预估行数,也是执行计划里row=的来源之一,pg 14用-1表示没收集过统计信息,以区分于空表
  • relpages:表预估页数 relpages
  • relallvisible :vm(visibility map)文件中被标记的页数
SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 'tmp001';

未收集统计信息的表

CREATE TABLE tmp_copy(LIKE tmp001) WITH (autovacuum_enabled = false);
SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 'tmp_copy';

       在生成执行计划时,会按照表大小及字段宽度等预估行数,所以rows不会是0。并且如果表增大,预估的rows会相应增加(但如果delete删除表数据而表大小没有变化,则不会生效)。

具体可以参考:postgresql源码学习(56)—— explain是如何快速估算pg表行数的_Hehuyi_In的博客-CSDN博客

三、  详细统计信息

这部分内容存在pg_statistic系统表中,但里面的内容很难看懂,因此通常我们会看pg_stats视图

 pg_stats视图内容

下面是主要字段含义

1.  null_frac 空值比率

执行计划预估空值行数时,会用 reltuples * null_frac

insert into tmp001 select null from tmp001 limit 1000;

explain select * from tmp001 where aid is null;SELECT round(reltuples * s.null_frac) AS rows
FROM pg_class JOIN pg_stats s ON s.tablename = relname
WHERE s.tablename = 'tmp001' AND s.attname = 'aid';

也可以明显看出来,是有误差的。 

2. n_distinct 非重复值

  • 如果值为负数,其绝对值代表非重复值在列中占比(总行数/非重复值)

       例如-1表示所有值均不重复(总行数/非重复值=1),-3表示非重复值占0.3(总行数/非重复值=3)。

  • 非重复值占比超过10%时会用比例表示,否则使用具体数字

       这一项由于仅抽样部分行,有时可能很不准确,可以手工指定列有多少非重复值

alter table tab_name alter column col_name set (n_distinct=xxx);

       如果表是有继承关系的其他子表的父表,还可以设置n_distinct_inherited,这样子表会继续使用这个父表的设置值。

alter table tab_name alter column col_name set (n_distinct_inherited=xxx);

3. 最频繁值 Most Common Values

pg_stats视图的most_common_vals 和 most_common_freqs字段。

SELECT most_common_vals AS mcv,left(most_common_freqs::text,60) || '...' AS mcf
FROM pg_stats
WHERE tablename = 'pgbench_branches' and attname='bbalance';select bbalance,count(*) from pgbench_branches group by bbalance;

表示bbalance字段最频繁值是0和1,出现比率一个约71%,一个约28%

这个最常用于 column = value 的条件,例如

也适用于范围查询行数预估,本质上是一样的

4. 直方图

        如果distinct值太多,pg不可能一个个存起来,就会使用直方图保存。直方图的基本原理是将数据排序后分成若干个桶(bucket),并记录每个桶中数据的最大值、最小值、出现频次占比等信息。

 上面的bid是存了所有唯一值,下面aid就只用直方图存了部分值

最常见的直方图分为两类

  • 等宽直方图 Equi-width Histogram

       将数据按最大、小值区间等分为N,即所谓"等宽"。

假设某一列各个值的分布如下

划分为4个桶,则等宽直方图为

       优点是简洁清晰,缺点则是无法根据各值出现频率进行统计。如果桶中值偏差度过高,预估的返回行数可能差距会很大。

  • 等高直方图 Equi-depth Histogram

        将数据按总频次等分为N,每个桶中数值的频次之和为总行数的 1/N,即所谓"等高"。

       优点是增加选择率估算的准确性;且数据分散的区间内每个桶中的数值跨度更大,有利于减小储存直方图所消耗的内存。缺点是如果某个值占比极高,会导致它自己占很多个桶,其他大量值挤在一个桶中。

5. 非标准类型的统计信息

       most_common_elems,most_common_elem_freqs,elem_count_histogram 会显示非标准类型的元素的mcv,mcf,直方图信息,通常适用于数组、向量、范围等数据类型。

6. 平均宽度 avg_width

顾名思义,列中存储值的平均宽度,通常对变长的字符串类型比较有意义。

select avg_width FROM pg_stats where tablename='tmp002';

7. 相关度 Correlation

元组顺序与物理存储顺序。1表示完全一致,-1表示完全相反,越一致通常性能越好。

postgres=# create table test2(id int);
CREATE TABLE
postgres=# insert into test2 SELECT ceil(random() * 5) AS num FROM generate_series(1,5);
INSERT 0 5
postgres=# select * from test2;id 
----52143
(5 rows)postgres=# analyze test2;
ANALYZE
postgres=# select correlation from pg_stats where tablename  = 'test2';correlation 
--------------0.2
(1 row)

       cluster命令可以对表进行进行聚簇,不过只对存量数据有效,增量数据无法保证,并且是8级锁,通常不会这样来用。

postgres=# create index on test2(id);
CREATE INDEX
postgres=# cluster test2 USING test2_id_idx ;
CLUSTER
postgres=# select * from test2;id 
----12345
(5 rows)postgres=# analyze test2;
ANALYZE
postgres=#  select correlation from pg_stats where tablename  = 'test2';correlation 
-------------1
(1 row)

四、 表达式统计信息

1. 直接为表达式收集统计信息

       当条件是表达式时(function-call = constant),pg给它预估的返回行占比总是0.5%,这可能会非常不准确。例如下面这个例子,条件是month,而最多只有12个月,因此占比预估约为1/12.

 因此,pg引入了作为扩展的表达式统计信息,用法有点类似函数索引。

CREATE STATISTICS flights_expr ON (extract(
month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow'
))
FROM flights;

       扩展统计信息存在pg_statistic_ext系统表中,收集的数据单独存在pg_statistic_ext_data表中,而表达式统计信息可以在pg_stats_ext_exprs视图中查到。

2. 为表达式索引收集统计信息

另一个方法是为表达式创建索引,并对该索引收集统计信息(在pg_stats中查询)

CREATE INDEX ON flights(extract(month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow'));
ANALYZE flights;

五、 多元统计信息 multivariate statistics

       类似oracle,这是为了解决列之间的相关性问题(correlated predicates)。一个经典案例是问出生在9月并且是处女座的人有多少,如果将两列当作毫无关系,预估值就会大大降低。

CREATE STATISTICS flights_dep(dependencies)
ON flight_no, departure_airport FROM flights;

        在pg 14中,不仅可以为多列创建扩展统计信息,还可以为多个表达式列也创建统计信息。

参考

《Postgresql修炼之道:从小工到专家(第二版)》

深度剖析PostgreSQL中的统计信息

PostgreSQL学习篇13.1 统计信息的收集_在路上-CSDN博客_postgresql 收集统计信息

PostgreSQL中的统计信息

这篇关于postgresql_internals-14 学习笔记(六)—— 统计信息的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/411959

相关文章

HarmonyOS学习(七)——UI(五)常用布局总结

自适应布局 1.1、线性布局(LinearLayout) 通过线性容器Row和Column实现线性布局。Column容器内的子组件按照垂直方向排列,Row组件中的子组件按照水平方向排列。 属性说明space通过space参数设置主轴上子组件的间距,达到各子组件在排列上的等间距效果alignItems设置子组件在交叉轴上的对齐方式,且在各类尺寸屏幕上表现一致,其中交叉轴为垂直时,取值为Vert

Ilya-AI分享的他在OpenAI学习到的15个提示工程技巧

Ilya(不是本人,claude AI)在社交媒体上分享了他在OpenAI学习到的15个Prompt撰写技巧。 以下是详细的内容: 提示精确化:在编写提示时,力求表达清晰准确。清楚地阐述任务需求和概念定义至关重要。例:不用"分析文本",而用"判断这段话的情感倾向:积极、消极还是中性"。 快速迭代:善于快速连续调整提示。熟练的提示工程师能够灵活地进行多轮优化。例:从"总结文章"到"用

【前端学习】AntV G6-08 深入图形与图形分组、自定义节点、节点动画(下)

【课程链接】 AntV G6:深入图形与图形分组、自定义节点、节点动画(下)_哔哩哔哩_bilibili 本章十吾老师讲解了一个复杂的自定义节点中,应该怎样去计算和绘制图形,如何给一个图形制作不间断的动画,以及在鼠标事件之后产生动画。(有点难,需要好好理解) <!DOCTYPE html><html><head><meta charset="UTF-8"><title>06

学习hash总结

2014/1/29/   最近刚开始学hash,名字很陌生,但是hash的思想却很熟悉,以前早就做过此类的题,但是不知道这就是hash思想而已,说白了hash就是一个映射,往往灵活利用数组的下标来实现算法,hash的作用:1、判重;2、统计次数;

hdu1496(用hash思想统计数目)

作为一个刚学hash的孩子,感觉这道题目很不错,灵活的运用的数组的下标。 解题步骤:如果用常规方法解,那么时间复杂度为O(n^4),肯定会超时,然后参考了网上的解题方法,将等式分成两个部分,a*x1^2+b*x2^2和c*x3^2+d*x4^2, 各自作为数组的下标,如果两部分相加为0,则满足等式; 代码如下: #include<iostream>#include<algorithm

零基础学习Redis(10) -- zset类型命令使用

zset是有序集合,内部除了存储元素外,还会存储一个score,存储在zset中的元素会按照score的大小升序排列,不同元素的score可以重复,score相同的元素会按照元素的字典序排列。 1. zset常用命令 1.1 zadd  zadd key [NX | XX] [GT | LT]   [CH] [INCR] score member [score member ...]

【机器学习】高斯过程的基本概念和应用领域以及在python中的实例

引言 高斯过程(Gaussian Process,简称GP)是一种概率模型,用于描述一组随机变量的联合概率分布,其中任何一个有限维度的子集都具有高斯分布 文章目录 引言一、高斯过程1.1 基本定义1.1.1 随机过程1.1.2 高斯分布 1.2 高斯过程的特性1.2.1 联合高斯性1.2.2 均值函数1.2.3 协方差函数(或核函数) 1.3 核函数1.4 高斯过程回归(Gauss

业务中14个需要进行A/B测试的时刻[信息图]

在本指南中,我们将全面了解有关 A/B测试 的所有内容。 我们将介绍不同类型的A/B测试,如何有效地规划和启动测试,如何评估测试是否成功,您应该关注哪些指标,多年来我们发现的常见错误等等。 什么是A/B测试? A/B测试(有时称为“分割测试”)是一种实验类型,其中您创建两种或多种内容变体——如登录页面、电子邮件或广告——并将它们显示给不同的受众群体,以查看哪一种效果最好。 本质上,A/B测

【学习笔记】 陈强-机器学习-Python-Ch15 人工神经网络(1)sklearn

系列文章目录 监督学习:参数方法 【学习笔记】 陈强-机器学习-Python-Ch4 线性回归 【学习笔记】 陈强-机器学习-Python-Ch5 逻辑回归 【课后题练习】 陈强-机器学习-Python-Ch5 逻辑回归(SAheart.csv) 【学习笔记】 陈强-机器学习-Python-Ch6 多项逻辑回归 【学习笔记 及 课后题练习】 陈强-机器学习-Python-Ch7 判别分析 【学

系统架构师考试学习笔记第三篇——架构设计高级知识(20)通信系统架构设计理论与实践

本章知识考点:         第20课时主要学习通信系统架构设计的理论和工作中的实践。根据新版考试大纲,本课时知识点会涉及案例分析题(25分),而在历年考试中,案例题对该部分内容的考查并不多,虽在综合知识选择题目中经常考查,但分值也不高。本课时内容侧重于对知识点的记忆和理解,按照以往的出题规律,通信系统架构设计基础知识点多来源于教材内的基础网络设备、网络架构和教材外最新时事热点技术。本课时知识