PostgreSQL DBA(12) - 统计信息在计算选择率上的应用#2

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

一、计算选择率

测试数据生成脚本详见上节,这里不再累述.

多条件单列查询

sql脚本和执行计划:

testdb=# explain verbose select * from t_int where c1 < 2312 and c1 > 500;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Seq Scan on public.t_int  (cost=0.00..2040.00 rows=18375 width=9)
   Output: c1, c2
   Filter: ((t_int.c1 < 2312) AND (t_int.c1 > 500))
(3 rows)

SQL语句有两个约束条件:c1 < 2312 和 c1 > 500,是同一个列,统计信息中并没有对应">"操作符的统计信息,PG实际上是把">"转换为"<="进行处理.
即"c1 < 2312 and c1 > 500"的选择率="c1 < 2312"选择率 - "c1 <= 500"选择率:
c1 < 2312 选择率=(1-0.0003)*(23+(2312-2287-1)/(2388-2287))/100=.232306525
c1 <= 500 选择率=(1-0.0003)*(4+(500-416)/(514-416))/100=.048556857
c1 < 2312 and c1 > 500选择率=.232306525 - .048556857=.183749668,执行计划中的rows=18375(取整)

多条件多列查询

SQL脚本和执行计划:

testdb=# explain verbose select * from t_int where c1 < 2312 and c2 = 'TEST';
                             QUERY PLAN                              
---------------------------------------------------------------------
 Seq Scan on public.t_int  (cost=0.00..2040.00 rows=23 width=9)
   Output: c1, c2
   Filter: ((t_int.c1 < 2312) AND ((t_int.c2)::text = 'TEST'::text))
(3 rows)

SQL语句有两个约束条件:c1 < 2312 and c2 = 'TEST'.
由于存在不同的两个列,运算符是AND,PG计算选择率的时候使用了概率论的方法,即:
P(A and B)=P(A) x P(B)
此例中,A=c1 < 2312,B=c2='TEST'
从上节已知,P(A)=.232306525,下面计算P(B)
c2 = 'TEST',操作符是"=",使用高频值进行计算:

testdb=# \x
Expanded display is on.
testdb=# select staattnum,stakind1,staop1,stanumbers1,stavalues1,
                 stakind2,staop2,stanumbers2,stavalues2,
                 stakind3,staop3,stanumbers3,stavalues3
from pg_statistic 
where starelid = 16755 
      and staattnum = 2;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
staattnum   | 2
stakind1    | 1
staop1      | 98
stanumbers1 | {0.0015,0.00146667,0.00143333,0.0014,0.0014,0.0014,0.0014,0.0014}
stavalues1  | {C2685,C2999,C2279,C2399,C2556,C2723,C2777,C2833}
stakind2    | 2
staop2      | 664
stanumbers2 | 
stavalues2  | {C20,C2106,C2116,C2125,C2134,C2142,C2151,C2160,C2169,C2178,C2187,C2196,C2203,C2212,C2220,C223,C2239,C2248,C2257,C2266,C2276,C2286,C2296,C2304,C2313,C2322,C2330,C2340,C235,C2358,C2367,C2376,C2385,C2394,C2403,C2411,C2421,C2430,C244,C2449,C2457,C2466,C2476,C2485,C2493,C2502,C2511,C252,C2529,C2538,C2547,C2555,C2565,C2574,C2583,C2592,C2600,C2610,C2620,C263,C264,C2649,C2658,C2666,C2674,C2683,C2693,C2701,C271,C2719,C2729,C2739,C2748,C2757,C2765,C2774,C2784,C2793,C2801,C2810,C2819,C2828,C2839,C2847,C2856,C2865,C2875,C2884,C2893,C2901,C2910,C2919,C2928,C2937,C2946,C2955,C2963,C2971,C2980,C299,C2998}
stakind3    | 3
staop3      | 664
stanumbers3 | {0.829913}
stavalues3  | 

testdb=# 
testdb=# select starelid,staattnum,stainherit,stanullfrac,stawidth,stadistinct 
testdb-# from pg_statistic 
testdb-# where starelid = 16755 and staattnum = 2;
-[ RECORD 1 ]------
starelid    | 16755
staattnum   | 2
stainherit  | f
stanullfrac | 0
stawidth    | 5
stadistinct | 1000

从以上统计信息中可知,'TEST'不在高频值中,包括高频值共有1000个不同值,因此c2='TEST'的选择率=(1-高频值比例)/(不同值个数 - 高频值个数),其中高频值比例=0.0015+0.00146667+0.00143333+0.0014+0.0014+0.0014+0.0014+0.0014=.0114,不同值个数=1000,高频值个数=6,代入公式,计算得到选择率P(B)=.000994567
P(A and B)=P(A) x P(B)=.232306525 x .000994567=.000231044,执行计划中的rows=.000231044*100000=23

二、参考资料

pg_statistic
pg_statistic.h
Row Estimation Examples