共计 5658 个字符，预计需要花费 15 分钟才能阅读完成。
这篇文章主要讲解了“PostgreSQL 统计信息在计算选择率上的应用分析”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“PostgreSQL 统计信息在计算选择率上的应用分析”吧！
一、计算选择率
单条件等值查询
测试数据生成脚本如下:
insert into t_grxx(dwbh,grbh,xm,xb,nl)
select generate_series(1,100000)/10|| ,generate_series(1,100000), XM ||generate_series(1,100000),
(case when (floor(random()*2)=0) then 男 else 女 end),floor(random() * 100 + 1)::int;SQL 脚本和执行计划:
testdb=# explain verbose select * from t_grxx where dwbh = 6323
QUERY PLAN
----------------------------------------------------------------------------------------
Index Scan using idx_t_grxx_dwbh on public.t_grxx (cost=0.29..46.90 rows=30 width=24)
Output: dwbh, grbh, xm, xb, nl
Index Cond: ((t_grxx.dwbh)::text = 6323 ::text)
(3 rows)
testdb=# explain verbose select * from t_grxx where dwbh = 24
QUERY PLAN
----------------------------------------------------------------------------------------
Index Scan using idx_t_grxx_dwbh on public.t_grxx (cost=0.29..20.29 rows=10 width=24)
Output: dwbh, grbh, xm, xb, nl
Index Cond: ((t_grxx.dwbh)::text = 24 ::text)
(3 rows)虽然都是等值查询, 但执行计划中 dwbh= 6323 和 dwbh= 24 返回的行数 (rows) 却不一样, 一个是 rows=30, 一个是 rows=10, 从生成数据的脚本来看, 6323 和 24 的 rows 应该是一样的, 但执行计划显示的结果却不同, 原因是计算选择率时 6323 出现在高频值中, 因此与其他值不同.
计算过程解析
查询该列的统计信息:
testdb=# \x
Expanded display is on.
testdb=# select starelid,staattnum,stainherit,stanullfrac,stawidth,stadistinct
from pg_statistic
where starelid = 16742 and staattnum = 1;
-[ RECORD 1 ]---------
starelid | 16742
staattnum | 1
stainherit | f
stanullfrac | 0
stawidth | 4
stadistinct | -0.10015
testdb=# select staattnum,stakind1,staop1,stanumbers1,stavalues1,
stakind2,staop2,stanumbers2,stavalues2,
stakind3,staop3,stanumbers3,stavalues3
from pg_statistic
where starelid = 16742
and staattnum = 1;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
staattnum | 1
stakind1 | 1
staop1 | 98
stanumbers1 | {0.0003}
stavalues1 | {6323}
stakind2 | 2
staop2 | 664
stanumbers2 |
stavalues2 | {0,1092,1181,1265,1350,1443,1529,1619,171,1797,1887,1972,2058,2151,2240,2334,2423,2520,2618,271,2798,2892,2987,3076,3162,3246,3332,3421,3510,3597,3685,3777,3860,3956,4051,4136,4227,4317,4408,45,4590,4671,4760,4850,4933,5025,5120,5210,5300,5396,548,5570,5656,5747,5835,5931,6017,6109,6190,6281,6374,6465,6566,6649,6735,6830,6921,7012,7101,7192,7278,737,7455,7544,7630,7711,7801,7895,7988,8081,8167,8260,8344,8430,8520,8615,8707,8809,8901,8997,9083,918,9272,9367,9451,9538,9630,9729,982,9904,9999}
stakind3 | 3
staop3 | 664
stanumbers3 | {0.819578}
stavalues3 |条件语句是等值表达式, 使用的操作符是 = (字符串等值比较,texteq/eqsel/eqjoinsel), 因此使用的统计信息是高频值 MCV(注意:staop1=98, 这是字符串等值比较). 6323 出现在高频值中, 选择率为 0.0003, 因此 rows=100,000×0.0003=30. 而 24 没有出现在高频值中, 选择率 =(1-0.0003)/abs(stadistinct)/Tuples=(1-0.0003)/abs(-0.10015)/100000=0.000099820269595606590000,rows=(1-0.0003)/abs(stadistinct)=10(取整).
单条件比较查询
测试脚本:
testdb=# create table t_int(c1 int,c2 varchar(20));
CREATE TABLE
testdb=#
testdb=# insert into t_int select generate_series(1,100000)/10, C2 ||generate_series(1,100000)/100;
INSERT 0 100000
testdb=# ANALYZE t_int;
ANALYZE
testdb=# select oid from pg_class where relname= t_int
oid
-------
16755
(1 row)查询 c1 列的统计信息
testdb=# \x
Expanded display is on.
testdb=# select staattnum,stakind1,staop1,stanumbers1,stavalues1,
testdb-# stakind2,staop2,stanumbers2,stavalues2,
testdb-# stakind3,staop3,stanumbers3,stavalues3
testdb-# from pg_statistic
testdb-# where starelid = 16755
testdb-# and staattnum = 1;
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
staattnum | 1
stakind1 | 1
staop1 | 96
stanumbers1 | {0.0003}
stavalues1 | {8306}
stakind2 | 2
staop2 | 97
stanumbers2 |
stavalues2 | {0,108,215,318,416,514,611,704,809,912,1015,1111,1217,1312,1410,1511,1607,1705,1805,1903,2002,2094,2189,2287,2388,2487,2592,2695,2795,2896,2998,3112,3213,3304,3408,3507,3606,3707,3798,3908,4004,4106,4205,4312,4413,4505,4606,4714,4821,4910,5014,5118,5220,5321,5418,5516,5613,5709,5807,5916,6014,6127,6235,6341,6447,6548,6648,6741,6840,6931,7032,7131,7234,7330,7433,7532,7626,7727,7827,7925,8020,8120,8217,8322,8420,8525,8630,8730,8831,8934,9032,9128,9223,9323,9425,9527,9612,9706,9804,9904,9999}
stakind3 | 3
staop3 | 97
stanumbers3 | {1}
stavalues3 |查询语句:
testdb=# explain verbose select * from t_int where c1 2312;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on public.t_int (cost=0.00..1790.00 rows=23231 width=9)
Output: c1, c2
Filter: (t_int.c1 2312)
(3 rows)SQL 使用了非等值查询 (,int4lt/scalarltsel/scalarltjoinsel), 结合统计信息中 MCV 和直方图使用,
由于 2312 均小于 MCV 中的值, 因此根据 MCV 得出的选择率为 0.
根据直方图计算的选择率 =(1-0.0003)x(23+(2312-2287-1)/(2388-2287))/100=0.2323065247,rows=100000×0.2323065247=23231(取整)
其中:
除高频值外的其他数值占比 =(1-0.0003)
直方图中的总槽数 = 数组元素总数 - 1 即 101-1=100
2312 落在第 24 个槽中, 槽占比 =(23+(2312-2287-1)/(2388-2287))/100
感谢各位的阅读，以上就是“PostgreSQL 统计信息在计算选择率上的应用分析”的内容了，经过本文的学习后，相信大家对 PostgreSQL 统计信息在计算选择率上的应用分析这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！
