企业官网建设流程全解析

5.1 统计信息

5.1.1 PG_STATISTIC系统表

统计信息的作用：

• 物理优化需要计算各种物理路径的代价
• 代价估算严重依赖于统计信息
• 统计信息是否能准确描述表中的数据分布是决定代价准确性的重要条件

单列统计信息类型（7种）：

多列统计信息类型（2种）：

选择率示例：

-- 高选择率，采用顺序扫描EXPLAINSELECT*FROMSTUDENTWHEREsno>2;-- 结果：Seq Scan on student (cost=0.00..224.00 rows=9999 width=10)-- 低选择率，采用索引扫描EXPLAINSELECT*FROMSTUDENTWHEREsno<2;-- 结果：Index Scan using student_pkey on student (cost=0.29..8.30 rows=1 width=10)

顺序读vs随机读：

#defineDEFAULT_SEQ_PAGE_COST1.0// 顺序读的单页代价#defineDEFAULT_RANDOM_PAGE_COST4.0// 随机读的单页代价

PG_STATISTIC系统表结构：

-- 基本字段starelid-- 对应表的OID（来自PG_CLASS）staattnum-- 列的编号（来自PG_ATTRIBUTES）stanullfrac-- NULL值率，NULL值所占比例stawidth-- 列的平均宽度stadistinct-- 消重之后的数据个数或比例

stadistinct取值：

• = 0：代表未知或者未计算的情况

• 0：代表消除重复值之后的个数

• < 0：绝对值是去重之后的个数占总个数的比例（通常使用）

示例分析：

-- STUDENT表统计信息starelid|staattnum|stanullfrac|stawidth|stadistinct---------+-----------+-------------+----------+-------------24582|1|0|4|-1-- sno列24582|2|0.142857|3|-0.571429-- sname列24582|3|0.142857|4|-0.285714-- ssex列

槽位系统：

• 每个属性最多支持5种统计方法（STATISTIC_NUM_SLOTS = 5）
• 每个槽位包含：stakind、staop、stanumbers、stavalues
• 不同列使用不同数量的槽位

槽位示例：

-- 第一个槽位starelid|staattnum|stakind1|staop1|stanumbers1|stavalues1---------+-----------+----------+--------+----------------------+------------------24582|1|2|97||{1,2,3,4,5,6,7}-- sno直方图24582|2|1|98|{0.285714,0.285714}|{ls,zs}-- sname MCV24582|3|1|96|{0.571429,0.285714}|{1,2}-- ssex MCV

5.1.2 PG_STATISTIC_EXT系统表

创建多列统计信息：

CREATESTATISTICSSTATEXT_STUDENTONsno,sname,ssexFROMSTUDENT;

系统表结构：

示例：

CREATESTATISTICSSTATEXT_STUDENTONsno,sname,ssexFROMSTUDENT;ANALYZESTUDENT(sno,sname,ssex);-- 查看统计信息SELECT*FROMpg_statistic_extWHEREstxname='statext_student';-- 结果包含NDISTINCT和DEPENDENCIES信息

5.1.3 单列统计信息生成

生成步骤：

1. 数据采样（acquire_sample_rows函数）
2. 数据统计（compute_scalar_stats函数等）

采样算法：

• 第一阶段：S算法（选择抽样技术）对页面进行随机采样
• 第二阶段：Z(Vitter)算法（蓄水池抽样）对元组进行采样

S算法（简化版）：

从N个记录中随机选取n个记录（0 < n <= N） S1. [初始化] K = N - t, k = n - m S2. [生成随机数] 0 <= V <= 1 S3. [生成跳过条件] p = 1 - k/K S4. [检测] 如果 V < p，跳转至S6 S5. [纳入样本] m++, t++, 如果 m < n 跳转至S2 S6. [跳过] t++, K--, p *= 1 - k/K, 跳转至S4

Vitter算法改进：

1. 随机数生成算法改进
2. 通过随机变量跳过记录，避免O(N)复杂度
3. 使用临界点（22.0 × n）在X算法和Z算法之间切换

采样框架：

acquire_sample_rows{BlockSampler_Init;// S算法赋初值reservoir_init_selection_state;// Z算法赋初值while(BlockSampler_HasMore){block=BlockSampler_Next;// S算法核心foreach(tuple in block){if(蓄水池未满){记录TUPLE到蓄水池;}if(rowstoskip<0){// Z算法中的随机变量Trowstoskip=reservoir_get_next_S;if(rowstoskip<=0){随机替换蓄水池中的元组;}}}}}

元组密度估算（EMA方法）：

old_density=old_rel_tuples/old_rel_pages;new_density=scanned_tuples/scanned_pages;multiplier=(double)scanned_pages/(double)total_pages;updated_density=old_density+(new_density-old_density)*multiplier;reltuples=floor(updated_density*total_pages+0.5);

采样相关变量：

统计方法选择：

if(OidIsValid(eqopr)&&OidIsValid(ltopr)){stats->compute_stats=compute_scalar_stats;// 有等值和不等值操作符}elseif(OidIsValid(eqopr)){stats->compute_stats=compute_distinct_stats;// 只有等值操作符}else{stats->compute_stats=compute_trivial_stats;// 其他情况}

compute_scalar_stats函数处理流程：

以sname列为例：

初始状态： samplerows = 7 -- 样本容量 null_cnt = 1 -- NULL值个数 nonnull_cnt = 6 -- 非NULL值个数 toowide_cnt = 0 -- 超长值的个数 total_width = 18 -- 非NULL值的长度总和 排序后： ndistinct = 4 -- 去掉重复值和NULL值之后的个数 stanullfrac = 1/7 -- NULL值比例 stdwidth = 3 -- 平均宽度 stadistinct = 4/7 -- 去重比例

MCV（高频值）确定：

• 情况1：所有值都可以做MCV值（满足条件时）
• 情况2：部分值做MCV值（不满足条件时）

直方图生成：

• 使用等频直方图
• 桶数计算：num_hist = ndistinct - num_mcv
• 边界值确定：通过排序后的值均匀分布

相关系数计算：

// 排序前编号：0, 1, 2, 3, 4, 5// 排序后编号：1, 5, 2, 3, 0, 4// 相关系数公式：ρ = (n * ∑xd - ∑x * ∑d) / (√(n * ∑x² - (∑x)²) * √(n * ∑d² - (∑d)²))

5.1.4 多列统计信息生成

STATS_EXT_NDISTINCT类型：

• 记录多列组合的去重数据量
• 例如：{sno, sname}、{sno, ssex}、{sname, ssex}、{sno, sname, ssex}的去重数据量

生成流程：

for(k=2;k<=numattrs;k++){generator=generator_init(numattrs,k);while((combination=generator_next(generator))){item->ndistinct=ndistinct_for_combination(...);}}

STATS_EXT_DEPENDENCIES类型：

• 计算列之间的函数依赖度
• 函数依赖度定义：两个属性之间满足函数依赖的值占总体数量的比例

函数依赖度计算：

for(i=1;i<=numrows;i++){if(当前值和上一个值不同){if(n_violations==0){n_supporting_rows+=group_size;}n_violations=0;group_size=1;}else{if(第k个值相同){group_size++;}else{n_violations++;}}}// 函数依赖度 = n_supporting_rows / numrows

数据结构：

// MVDependencies：记录整个多列统计信息的函数依赖关系typedefstructMVDependencies{uint32 magic;uint32 type;uint32 ndeps;MVDependency*deps[FLEXIBLE_ARRAY_MEMBER];}MVDependencies;// MVDependency：记录每个单独的函数依赖关系typedefstructMVDependency{doubledegree;// 函数依赖度AttrNumber nattributes;// 涉及的属性数量AttrNumber attributes[FLEXIBLE_ARRAY_MEMBER];}MVDependency;

5.2 选择率

5.2.1 使用函数依赖计算选择率

使用条件：

• 约束条件中只涉及一个表
• 该表上有基于函数依赖的统计信息
• 约束条件形式：Var = Const或RelabelType = Const

统计信息选择原则：

1. 约束条件的属性和函数依赖关系统计信息中的键值取交集
2. 交集中的键值多的最适用
3. 如果交集中键值数相同，则键值数少的获胜

示例：

-- 约束条件：A = 1 AND B = 1-- 统计信息键值：{A, B}, {A, B, C}, {B, C, D}-- 最适合使用：{A, B}（交集=2，键值数=2）

选择率计算公式：

P(A = Const, B = Const) = P(A = Const) * (d + (1-d) * P(B = Const)) 其中d是{A -> B}的函数依赖度

递归计算：

while(true){dependency=find_strongest_dependency(stat,dependencies,clauses_attnums);s2=clause_selectivity(root,clause,...);s1*=(dependency->degree+(1-dependency->degree)*s2);}

5.2.2 子约束条件的选择率

默认选择率：

• clause_selectivity函数默认选择率：0.5
• 缓存机制：RestrictInfo->norm_selec和outer_selec

选择率计算函数表：

5.2.3 基于范围的约束条件的选择率修正

问题：约束条件A > 5 AND A > 7 AND A < 9 AND A < 8中的子条件不独立

解决方法：使用RangeQueryClause链表

数据结构：

typedefstructRangeQueryClause{structRangeQueryClause*next;Node*var;// 属性（列）bool have_lobound;// 是否有下界bool have_hibound;// 是否有上界Selectivity lobound;// 下界选择率Selectivity hibound;// 上界选择率}RangeQueryClause;

修正逻辑：

if(上界和下界同时存在){s2=hibound+lobound-1.0;// P(A > x) + P(A < y) = 1 - P(A > x AND A < y)s2+=nulltestsel(IS_NULL);// 考虑NULL值s1*=s2;}else{if(只有下界)s1*=lobound;if(只有上界)s1*=hibound;}

5.3 OpExpr的选择率

连接条件vs过滤条件：

• varRelid有值：过滤条件
• sjinfo == NULL：过滤条件
• 其他：根据引用表数量判断

操作符选择率函数对照表：

5.3.1 eqsel函数

处理流程：

eqsel/neqsel ↓ eqsel_internal(negate) ↓ const? → var_eq_const nonconst? → var_eq_non_const

var_eq_const函数逻辑：

1. 如果列有唯一性：选择率 = 1/元组数量
2. 如果常量在高频值数组中：直接使用高频值频率
3. 否则：计算剩余值的平均选择率

选择率计算：

// 从高频值数组获取for(i=0;i<sslot.nvalues;i++){if(匹配成功){selec=sslot.numbers[i];break;}}// 未匹配时sumcommon=sum(所有高频值比例);selec=1.0-sumcommon-nullfrac;// 去除NULL和高频值otherdistinct=get_variable_numdistinct()-sslot.nnumbers;selec/=otherdistinct;// 平均分配// 限制：不能比高频值中最低的比例高if(selec>sslot.numbers[sslot.nnumbers-1])selec=sslot.numbers[sslot.nnumbers-1];

5.3.2 scalargtsel函数

处理流程：

1. 保证Var在操作符左侧
2. 调用scalarineqsel函数
3. 结合MCV和直方图计算

直方图计算：

// 二分法找Const所在的桶while(lobound<hibound){intprobe=(lobound+hibound)/2;// get_actual_variable_range：修正边界值}// 计算桶内比例if(桶边界相等)binfrac=0.5;elseif(val<=low)binfrac=0.0;elseif(val>=high)binfrac=1.0;elsebinfrac=(val-low)/(high-low);// 总选择率histfrac=(满足条件的桶数+binfrac)/总桶数;selec=(1.0-NULL比例-高频值比例)× histfrac+mcv_selec;

5.3.3 eqjoinsel函数

连接选择率计算的4个部分：

1. NULL值率部分：一个表的某列NULL值比例
2. 高频值数组中互相匹配部分：两个表的高频值数组取交集
3. 高频值数组中未匹配部分：去掉互相匹配的值
4. 其他值部分：去掉NULL值和高频值后的值

选择率公式：

selec = S(x2)×S(y2) + S(x3)×S(y4) + S(x4)×S(y3) + S(x4)×S(y4) ───────────────────────────────────────────────────────── N × N

其中：

• S(x2), S(y2)：高频值数组中互相匹配的部分
• S(x3), S(y3)：高频值数组中未匹配的部分
• S(x4), S(y4)：其他值的部分

匹配算法：

for(i=0;i<sslot1.nvalues;i++){for(j=0;j<sslot2.nvalues;j++){if(值相等){matchprodfreq+=sslot1.numbers[i]*sslot2.numbers[j];hasmatch1[i]=hasmatch2[j]=true;nmatches++;break;}}}

5.4 小结

核心要点：

1. 统计信息是选择率计算的基础
2. PostgreSQL 10.0之前只有单列统计信息，10.0增加了多列统计信息
3. 选择率估算依赖统计信息的准确性
4. 假设数据分布平坦可能导致估算偏差
5. 无法使用统计信息时采用默认值

局限性：

• 统计信息基于样本，样本显著性影响结果
• 数据更新后不会立即更新统计信息
• 基于概率的方法不适用于所有情况
• 默认值可能导致代价估算偏差

关键概念速查

常用SQL查询

-- 查看表的统计信息SELECT*FROMpg_statisticWHEREstarelid='表名'::regclass;-- 查看表的基本信息SELECTrelname,relpages,reltuplesFROMpg_classWHERErelname='表名';-- 创建多列统计信息CREATESTATISTICSstats_nameONcol1,col2,col3FROMtable_name;-- 更新统计信息ANALYZEtable_name;-- 查看多列统计信息SELECT*FROMpg_statistic_extWHEREstxname='stats_name';-- 查看查询计划EXPLAINANALYZESELECT*FROMtable_nameWHERE条件;