本节以举例的形式简单介绍了PG数据库中统计信息(高频值MCV和直方图HISTOGRAM)在单条件等值查询和单条件比较查询计算选择率上的应用。

一、计算选择率

单条件等值查询

测试数据生成脚本如下:

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=# \xExpanded display is on.testdb=# select starelid,staattnum,stainherit,stanullfrac,stawidth,stadistinct from pg_statistic where starelid = 16742 and staattnum = 1;-[ RECORD 1 ]---------starelid    | 16742staattnum   | 1stainherit  | fstanullfrac | 0stawidth    | 4stadistinct | -0.10015testdb=# select staattnum,stakind1,staop1,stanumbers1,stavalues1,                 stakind2,staop2,stanumbers2,stavalues2,                 stakind3,staop3,stanumbers3,stavalues3from pg_statistic where starelid = 16742       and staattnum = 1;-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------staattnum   | 1stakind1    | 1staop1      | 98stanumbers1 | {0.0003}stavalues1  | {6323}stakind2    | 2staop2      | 664stanumbers2 | 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    | 3staop3      | 664stanumbers3 | {0.819578}stavalues3  |

条件语句是等值表达式,使用的操作符是"="(字符串等值比较,texteq/eqsel/eqjoinsel),因此使用的统计信息是高频值MCV(注意:staop1=98,这是字符串等值比较).'6323'出现在高频值中,选择率为0.0003,因此rows=100,000x0.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 TABLEtestdb=# testdb=# insert into t_int select generate_series(1,100000)/10,'C2'||generate_series(1,100000)/100;INSERT 0 100000testdb=# ANALYZE t_int;ANALYZEtestdb=# select oid from pg_class where relname='t_int';  oid  ------- 16755(1 row)

查询c1列的统计信息

testdb=# \xExpanded display is on.testdb=# select staattnum,stakind1,staop1,stanumbers1,stavalues1,testdb-#                  stakind2,staop2,stanumbers2,stavalues2,testdb-#                  stakind3,staop3,stanumbers3,stavalues3testdb-# from pg_statistic testdb-# where starelid = 16755 testdb-#       and staattnum = 1;-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------staattnum   | 1stakind1    | 1staop1      | 96stanumbers1 | {0.0003}stavalues1  | {8306}stakind2    | 2staop2      | 97stanumbers2 | 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    | 3staop3      | 97stanumbers3 | {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=100000x0.2323065247=23231(取整) 其中: 除高频值外的其他数值占比=(1-0.0003) 直方图中的总槽数=数组元素总数-1即101-1=100 2312落在第24个槽中,槽占比=(23+(2312-2287-1)/(2388-2287))/100

二、参考资料

pg_statistic

pg_statistic.h