1

pg 列统计信息学习笔记 -利来国际app

范计杰 2021-11-08
108

pg 统计信息学习笔记

列统计信息基表 pg_catalog.pg_statistic

             table "pg_catalog.pg_statistic"
   column    |   type   | collation | nullable | default
------------- ---------- ----------- ---------- ---------
 starelid    | oid      |           | not null |
 staattnum   | smallint |           | not null |
 stainherit  | boolean  |           | not null |
 stanullfrac | real     |           | not null |
 stawidth    | integer  |           | not null |
 stadistinct | real     |           | not null |
 stakind1    | smallint |           | not null |
 stakind2    | smallint |           | not null |
 stakind3    | smallint |           | not null |
 stakind4    | smallint |           | not null |
 stakind5    | smallint |           | not null |
 staop1      | oid      |           | not null |
 staop2      | oid      |           | not null |
 staop3      | oid      |           | not null |
 staop4      | oid      |           | not null |
 staop5      | oid      |           | not null |
 stacoll1    | oid      |           | not null |
 stacoll2    | oid      |           | not null |
 stacoll3    | oid      |           | not null |
 stacoll4    | oid      |           | not null |
 stacoll5    | oid      |           | not null |
 stanumbers1 | real[]   |           |          |
 stanumbers2 | real[]   |           |          |
 stanumbers3 | real[]   |           |          |
 stanumbers4 | real[]   |           |          |
 stanumbers5 | real[]   |           |          |
 stavalues1  | anyarray |           |          |
 stavalues2  | anyarray |           |          |
 stavalues3  | anyarray |           |          |
 stavalues4  | anyarray |           |          |
 stavalues5  | anyarray |           |          |
indexes:
    "pg_statistic_relid_att_inh_index" unique, btree (starelid, staattnum, stainherit)
starelid      被描述列所属的表或索引
staattnum     被描述列的编号
              pg_attribute.attnum
stainherit    如果为真,统计包含了继承后代的列而不仅仅是指定关系的列
stanullfrac   列的项为空的比例
stawidth      非空项的平均存储宽度,以字节计
stadistinct   列中非空唯一值的数目。
              一个大于零的值是唯一值的真正数目。
              一个小于零的值是表中行数的乘数的负值;例如,对于一个 80% 的值为非空且每个非空值平均出现两次的列,可以表示为stadistinct = -0.4。   0.8/2
	     stadistinct 应该随着行数进行缩放而不是固定值。 
	     if (stats->stadistinct > 0.1 * totalrows)
	          stats->stadistinct = -(stats->stadistinct / totalrows)
	     使用distinct=total_rows*abs(stadistinct)
             一个0值表示唯一值的数目未知。 
			  
stakindn      一个代码,它表示存储在该pg_statistic行中第n个“槽位”的统计类型。 
              stakind==1 most_common_vals,most_common_freqs   列中最常用值的一个列表,最常用值的频率列表;
	      stakind==2 histogram_bounds  将列值划分成大小接近的组的值列表。如果存在most_common_vals,其中的值会被直方图计算所忽略
              stakind==3 correlation   物理行顺序和列值逻辑顺序之间的统计关联。其范围从-1到 1。当值接近-1或 1时,在列上的一个索引扫描被认为比值接近0时的代价更低,因为这种情况减少了对磁盘的随机访问
	      stakind==4 most_common_elem_freqs
	      stakind==5 elem_count_histogram
			  
staopn        一个用于生成这些存储在第n个“槽位”的统计信息的操作符。 比如,一个柱面图槽位会用<操作符,该操作符定义了该数据的排序顺序。 
              pg_operator.oid
stacolln      排序规则用于导出存储在第n个“槽”中的统计信息。例如,可排序列的直方图槽将显示定义数据排序顺序的排序规则。对于不可整理数据,为零。 
              pg_collation.oid
stanumbersn   第n个“槽位”的类型的数值类型统计, 如果该槽位不涉及数值类型则为null 
stavaluesn    第n个“槽位”的类型的列值,如果该槽位类型不存储任何数据值则为 null。 
              每个数组的元素值实际上都是指定列的数据类型或者是一个相关类型(如数组元素类型), 因此,除了把这些列的类型定义成anyarray之外别无他法。
示例
select * from pg_catalog.pg_statistic st where starelid='bmsql_customer'::regclass order by staattnum
starelid|staattnum|stainherit|stanullfrac|stawidth|stadistinct|stakind1|stakind2|stakind3|stakind4|stakind5|staop1|staop2|staop3|staop4|staop5|stacoll1|stacoll2|stacoll3|stacoll4|stacoll5|stanumbers1                                                                                                                                                                                                                                                    |stanumbers2    |stanumbers3    |stanumbers4|stanumbers5|stavalues1                                                                                                                                                                                                                                                     |stavalues2                                                                                                                                                                                                                                                     |stavalues3|stavalues4|stavalues5|
--------|---------|----------|-----------|--------|-----------|--------|--------|--------|--------|--------|------|------|------|------|------|--------|--------|--------|--------|--------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------------|---------------|-----------|-----------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------|----------|----------|
   41080|        1|false     |        0.0|       4|        1.0|       1|       3|       0|       0|       0|    96|    97|     0|     0|     0|       0|       0|       0|       0|       0|{1.0}                                                                                                                                                                                                                                                          |{1.0}          |null           |null       |null       |{1}                                                                                                                                                                                                                                                            |                                                                                                                                                                                                                                                               |          |          |          |
   41080|        2|false     |        0.0|       4|       10.0|       1|       3|       0|       0|       0|    96|    97|     0|     0|     0|       0|       0|       0|       0|       0|{0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1}                                                                                                                                                                                                                      |{0.80067205}   |null           |null       |null       |{1,2,3,4,5,6,7,8,9,10}                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                               |          |          |          |
   41080|        3|false     |        0.0|       4|     3000.0|       1|       2|       3|       0|       0|    96|    97|    97|     0|     0|       0|       0|       0|       0|       0|{0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0|null           |{0.06640282}   |null       |null       |{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88|{101,129,158,187,216,245,274,303,332,361,390,419,448,477,506,535,564,593,622,651,680,709,738,767,796,825,854,883,912,941,970,999,1028,1057,1086,1115,1144,1173,1202,1231,1260,1289,1318,1347,1376,1405,1434,1463,1492,1521,1550,1579,1608,1637,1666,1695,1724,1|          |          |          |
   41080|        4|false     |        0.0|       4|-0.16623333|       1|       2|       3|       0|       0|  1752|  1754|  1754|     0|     0|       0|       0|       0|       0|       0|{0.0006,0.00056667,0.00053333,0.0005,0.00046667,0.00046667,0.00046667,0.00046667,0.00046667,0.00046667,0.00046667,0.00046667,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.0004333|null           |{-0.0043730875}|null       |null       |{0.3390,0.3427,0.0496,0.3632,0.0595,0.1933,0.2698,0.3200,0.3305,0.3595,0.3767,0.4278,0.0469,0.0648,0.0975,0.1194,0.1217,0.1254,0.1313,0.1331,0.1449,0.1538,0.1558,0.1971,0.2143,0.2533,0.3289,0.3294,0.3426,0.3443,0.3622,0.3856,0.4008,0.4095,0.4098,0.4198,0.|{0.0000,0.0048,0.0097,0.0151,0.0207,0.0268,0.0323,0.0374,0.0431,0.0486,0.0533,0.0584,0.0633,0.0681,0.0727,0.0775,0.0828,0.0878,0.0923,0.0971,0.1021,0.1069,0.1115,0.1162,0.1209,0.1261,0.1310,0.1359,0.1408,0.1457,0.1506,0.1555,0.1608,0.1655,0.1707,0.1752,0.|          |          |          |
   41080|        5|false     |        0.0|       3|        2.0|       1|       3|       0|       0|       0|  1054|  1058|     0|     0|     0|     100|     100|       0|       0|       0|{0.90096664,0.09903333}                                                                                                                                                                                                                                        |{0.7925414}    |null           |null       |null       |{gc,bc}                                                                                                                                                                                                                                                        |                                                                                                                                                                                                                                                               |          |          |          |
   41080|        6|false     |        0.0|      12|     1000.0|       1|       2|       3|       0|       0|    98|   664|   664|     0|     0|     100|     100|     100|       0|       0|{0.017633334,0.017033333,0.016866667,0.0136,0.0067,0.0065,0.0064666667,0.0064,0.0063333334,0.0063,0.0062666666,0.006233333,0.0061666667,0.0061333333,0.0061,0.0060666665,0.0060666665,0.0060333335,0.006,0.0059,0.0059,0.005866667,0.005866667,0.005866667,0.00|null           |{0.0035713348} |null       |null       |{ationantiought,pripreseing,antibarese,oughtoughtcally,baresepri,ationesecally,ableationese,callypripri,esecallypri,callyeingcally,pripresation,prioughtcally,ationesepri,ationeseeing,esepresought,ationableeing,prescallycally,eseeingcally,eseationeing,bara|{ableableable,ableantically,ableationable,ableationpres,ablebareing,ablecallybar,ableeingbar,ableeseanti,ableoughtanti,ableoughtpres,ableprespri,antiableation,antiantically,antiationanti,antibarbar,anticallypri,antieingpri,antioughtable,antipreseing,antip|          |          |          |
   41080|        7|false     |        0.0|      12|       -1.0|       2|       3|       0|       0|       0|   664|   664|     0|     0|     0|     100|     100|       0|       0|       0|null                                                                                                                                                                                                                                                           |{-0.009829705} |null           |null       |null       |{a00sipdhjlrfblz,acuvx1sdnk8hgn,akx25jrbf2,at3ellj7,b3qrcozp1,beedzsux,bmliqky7,bus1frzoa08irwks,c7e5d0ba,cgmobyv8tq07d,cohgkkgj,cwacbex2ajy66i,d7rnzjgxynb,dglh9vpc6e,dodgclyxuylf,dvpcjd9f9hfhmtax,e7jdul0ml6hfahr,eg3kzrqnp,eopoizmogdt6kl,ewhkjlpgokb26,f9l|                                                                                                                                                                                                                                                               |          |          |          |
   41080|        8|false     |        0.0|       5|        1.0|       1|       3|       0|       0|       0|  1752|  1754|     0|     0|     0|       0|       0|       0|       0|       0|{1.0}                                                                                                                                                                                                                                                          |{1.0}          |null           |null       |null       |{50000.00}                                                                                                                                                                                                                                                     |                                                                                                                                                                                                                                                               |          |          |          |
   41080|        9|false     |        0.0|       7|    -0.8715|       1|       2|       3|       0|       0|  1752|  1754|  1754|     0|     0|       0|       0|       0|       0|       0|{0.12666667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0|null           |{0.10737501}   |null       |null       |{-10.00,-12885.34,-12135.82,-8433.36,-6336.20,-5140.10,-4830.48,-3446.05,-3276.68,-2638.97,-2356.89,-1619.21,-1055.56,-1008.78,-918.61,-425.33,-367.67,-303.05,-244.95,-221.68,822.42,931.83,1392.76,1650.58,1652.92,1656.46,2002.80,2274.70,2309.88,2486.46,26|{-120589.86,-42179.18,-32035.78,-27128.04,-23178.40,-20005.91,-17595.02,-15551.48,-13955.66,-12595.12,-11360.08,-10263.07,-9156.81,-8282.22,-7526.06,-6846.82,-6172.50,-5523.57,-4912.86,-4508.90,-4128.93,-3722.89,-3337.31,-2970.99,-2599.08,-2207.51,-1889.4|          |          |          |
   41080|       10|false     |        0.0|       6|-0.63776666|       1|       2|       3|       0|       0|  1752|  1754|  1754|     0|     0|       0|       0|       0|       0|       0|{0.3586,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.000|null           |{0.20263122}   |null       |null       |{10.00,40.77,74.92,98.77,283.32,310.02,893.83,1002.44,1022.16,1050.17,1142.25,1490.76,1557.29,1649.96,1680.64,1703.44,1782.84,1805.01,1851.50,1896.63,1917.02,2005.20,2061.49,2097.47,2114.43,2328.48,2391.44,2400.93,2426.32,2469.44,2510.30,2562.95,2605.37,2|{12.14,174.95,344.12,502.36,660.53,794.18,927.06,1058.91,1204.70,1367.97,1522.46,1658.55,1798.19,1916.90,2041.17,2186.42,2313.44,2451.00,2576.84,2692.39,2806.35,2905.92,3012.45,3114.66,3227.46,3333.73,3444.66,3553.28,3659.34,3769.09,3883.75,3987.61,4104.3|          |          |          |
   41080|       11|false     |        0.0|       4|       51.0|       1|       2|       3|       0|       0|    96|    97|    97|     0|     0|       0|       0|       0|       0|       0|{0.3586,0.19516666,0.11313333,0.07456667,0.055033334,0.042466667,0.033566665,0.026033333,0.018466666,0.015533334,0.0117,0.009666666,0.0073666666,0.0065333336,0.0057,0.004766667,0.0042666667,0.003,0.0022333334,0.0017,0.0016,0.0011,0.00086667,0.00076667,0.0|null           |{0.26628405}   |null       |null       |{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,25,23,24,28,31,34,36,26,35,27,30,33,37,29,38,32,39,43,41,44,47,40,42,46,49,51}                                                                                                                       |{45,48,50}                                                                                                                                                                                                                                                     |          |          |          |
   41080|       12|false     |        0.0|       4|       57.0|       1|       2|       3|       0|       0|    96|    97|    97|     0|     0|       0|       0|       0|       0|       0|{0.266,0.2491,0.16836667,0.09793333,0.061933335,0.039666668,0.027366666,0.020633332,0.0144,0.009933333,0.0073666666,0.0062,0.004166667,0.004,0.0033666666,0.0031333333,0.0031333333,0.0023666667,0.0016333334,0.0014,0.0013666666,0.00096667,0.00086667,0.0007,|null           |{0.26039952}   |null       |null       |{2,1,3,4,5,6,7,8,9,10,11,12,14,13,15,16,17,18,19,21,20,23,22,24,25,26,43,36,38,40,42,45,47,51,41,49,27,28,29,30,31,35,39,46,32,33,34,37,44,48,50,52,53,57}                                                                                                     |{54,58,60}                                                                                                                                                                                                                                                     |          |          |          |
   41080|       13|false     |        0.0|      15|       -1.0|       2|       3|       0|       0|       0|   664|   664|     0|     0|     0|     100|     100|       0|       0|       0|null                                                                                                                                                                                                                                                           |{-0.0016376677}|null           |null       |null       |{a00vmhvhp2fa,acsdru3juyg,ajtbxirem5tputpf,at1530k2en1z3eu6h,b38f64pyfm5te48eqx2,bfhqrj7tpg8g,bn83bawhgwky2i4d4,bvaajvtiz65zer,c5xnswweyqgdqgdxu,cfnvvcyynbwj0,cn288xr4nricp3kqznmw,cvxtz6ev8tdjhdtpe,d99wbi0odu,dh9ypbrmfgoj2vb5ro,dphvztuw5ggo4x,dxoxuqyr3ry,|                                                                                                                                                                                                                                                               |          |          |          |
   41080|       14|false     |        0.0|      15|       -1.0|       2|       3|       0|       0|       0|   664|   664|     0|     0|     0|     100|     100|       0|       0|       0|null                                                                                                                                                                                                                                                           |{0.005798556}  |null           |null       |null       |{a03zzkwwlfdmuk7,actyehl7karc,akxu0rnegjjzzo,assmjneelbuexzybklc,b0ujw8srqmd0j,bducvtl7e4nwew,bm2ns2wmqjhp0fwtp,bu3bdw0jnhpncf0d,c6rzmoujut9ly38,cgs2uwghgnrurxdakok,cohz9yixprpy,cwjfimkginah7o,d921u3jcei,dh8p8dlctgfwes6wp6z,dpiqmfbqagfdbugoilt,dyb38ri7lqq|                                                                                                                                                                                                                                                               |          |          |          |
   41080|       15|false     |        0.0|      15|       -1.0|       2|       3|       0|       0|       0|   664|   664|     0|     0|     0|     100|     100|       0|       0|       0|null                                                                                                                                                                                                                                                           |{0.0036391425} |null           |null       |null       |{a01iygmihvxcdkongd,ackhbp4fubb1e,ajx27wuqnppfla,aru4fwwwqj,azkftl13hi2x3t0v,bcoc6ob2hw9cy2n,bkkkagbqoekgzjsn,bruzinteje5binf7wx,c1eggv5o63rzc46bp49l,ce14udibanv0g,cmer4nmqdhbwa6so,cuin42dmetiwtmtmtan,d6pfvczlimprlz,dhcr1hpvug00p,dpuecnjrniazzsnvpfcn,dxwu|                                                                                                                                                                                                                                                               |          |          |          |
   41080|       16|false     |        0.0|       3|      676.0|       1|       2|       3|       0|       0|  1054|  1058|  1058|     0|     0|     100|     100|     100|       0|       0|{0.0021,0.0021,0.0020666667,0.0020333333,0.0020333333,0.0020333333,0.0020333333,0.002,0.002,0.002,0.002,0.002,0.0019666667,0.0019666667,0.0019666667,0.0019333333,0.0019333333,0.0019333333,0.0019333333,0.0019333333,0.0019,0.0019,0.0019,0.0019,0.0019,0.0019|null           |{0.010709598}  |null       |null       |{hv,sd,gf,cu,eg,pb,wq,bx,nd,oy,ri,zr,ci,ul,wa,ap,at,kf,qp,zq,ae,ig,iq,kx,ld,mi,pp,rg,rr,su,xe,bh,bo,ed,iy,mc,oi,pf,pl,uh,yh,zs,bi,bk,bz,eb,fw,jb,kb,oo,px,uf,wy,gc,ib,is,qe,qj,rb,ry,tn,tw,ut,uv,vt,wr,xa,ah,dj,im,it,ko,mm,mq,ng,us,vi,wn,wp,yn,zb,da,eh,fb,fg|{aa,ag,ao,av,bb,bj,bq,bw,ce,ck,cq,cw,dc,di,dp,dv,ec,ek,eq,ew,fd,fk,fp,fv,gd,gk,gr,gx,hd,hj,ho,hu,ic,ik,iu,jc,jh,jn,ju,ka,kh,kn,ku,la,li,lo,lu,ma,mh,mo,mv,nc,nj,np,nv,ob,oj,or,ow,ph,po,pv,qc,qk,qs,qx,re,rm,rt,sa,sh,sn,st,ta,tg,tl,ts,tz,ui,up,uy,vd,vk,vq,vw|          |          |          |
   41080|       17|false     |        0.0|      10|-0.31656668|       1|       2|       3|       0|       0|  1054|  1058|  1058|     0|     0|     100|     100|     100|       0|       0|{0.00036667,0.00036667,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0|null           |{-0.007099603} |null       |null       |{134711111,803711111,188211111,241111111,242211111,272311111,430511111,534911111,677811111,720611111,755311111,786811111,877011111,929011111,938111111,964911111,004411111,025211111,034411111,043811111,050311111,108511111,112411111,134111111,185211111,2150|{000011111,011211111,021411111,031711111,042311111,053011111,063511111,073411111,083511111,093911111,105111111,116511111,126111111,137211111,147911111,157611111,167811111,178811111,188611111,198111111,208211111,218011111,228411111,237811111,247811111,2577|          |          |          |
   41080|       18|false     |        0.0|      17|       -1.0|       2|       3|       0|       0|       0|  1058|  1058|     0|     0|     0|     100|     100|       0|       0|       0|null                                                                                                                                                                                                                                                           |{-0.0010758232}|null           |null       |null       |{0000720554618279,0094493104245076,0194298937558310,0291388573568474,0386565393374964,0490430978121291,0589927576378785,0694300579363929,0790469558694240,0893798134357361,0996763766620530,1105087662319494,1206554809150304,1302687329603163,1401486226018001|                                                                                                                                                                                                                                                               |          |          |          |
   41080|       19|false     |        0.0|       8|-0.20226666|       1|       2|       3|       0|       0|  2060|  2062|  2062|     0|     0|       0|       0|       0|       0|       0|{0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.000|null           |{0.7676967}    |null       |null       |{"2021-10-25 14:29:25.057","2021-10-25 14:29:28.019","2021-10-25 14:29:30.016","2021-10-25 14:29:30.162","2021-10-25 14:29:30.208","2021-10-25 14:29:30.225","2021-10-25 14:29:32.674","2021-10-25 14:29:36.477","2021-10-25 14:29:38.353","2021-10-25 14:29:38|{"2021-10-25 14:29:24.854","2021-10-25 14:29:24.973","2021-10-25 14:29:25.081","2021-10-25 14:29:25.17","2021-10-25 14:29:25.241","2021-10-25 14:29:25.314","2021-10-25 14:29:25.435","2021-10-25 14:29:25.554","2021-10-25 14:29:25.708","2021-10-25 14:29:25.|          |          |          |
   41080|       20|false     |        0.0|       3|        1.0|       1|       3|       0|       0|       0|  1054|  1058|     0|     0|     0|     100|     100|       0|       0|       0|{1.0}                                                                                                                                                                                                                                                          |{1.0}          |null           |null       |null       |{oe}                                                                                                                                                                                                                                                           |                                                                                                                                                                                                                                                               |          |          |          |
   41080|       21|false     |        0.0|     409|       -1.0|       2|       3|       0|       0|       0|   664|   664|     0|     0|     0|     100|     100|       0|       0|       0|null                                                                                                                                                                                                                                                           |{-0.014546744} |null           |null       |null       |{a04viyysbyos01zoser0z07x85iyyyrgfnoqyeloimqilqxq4t0dtw5tc0p9m2emrvfxn4vccq0azdw2exbvuodwniqq2p3gkt4hlxciizwj1xbv2cbkvogibehwsdz8fqd38jauxhhbb9qurd0zx1wxnyq2mbzntkhpiky7nsaybernildllhrss4drgwgdm95hx0zd21emshqsaic5fkfsctbguszhfagxvit24lthpfmcs6ss1qkzrhb02c|                                                                                                                                                                                                                                                               |          |          |          |
   

pg_stats(基于pg_statistic可读性视图)


                     view "pg_catalog.pg_stats"
         column         |   type   | collation | nullable | default
------------------------ ---------- ----------- ---------- ---------
 schemaname             | name     |           |          |
 tablename              | name     |           |          |
 attname                | name     |           |          |
 inherited              | boolean  |           |          |
 null_frac              | real     |           |          |
 avg_width              | integer  |           |          |
 n_distinct             | real     |           |          |
 most_common_vals       | anyarray |           |          |
 most_common_freqs      | real[]   |           |          |
 histogram_bounds       | anyarray |           |          |
 correlation            | real     |           |          |
 most_common_elems      | anyarray |           |          |
 most_common_elem_freqs | real[]   |           |          |
 elem_count_histogram   | real[]   |           |          |
n_distinct         如果大于零,表示列中可区分值的估计个数。如果小于零,是可区分值个数除以行数的负值(当analyze认为可区分值的数量会随着表增长而增加时采用负值的形式,而如果认为列具有固定数量的可选值时采用正值的形式)。例如,-1表示一个唯一列,即其中可区分值的个数等于行数。 
most_common_vals   列中最常用值的一个列表(如果没有任何一个值看起来比其他值更常用,此列为空) 
most_common_freqs  最常用值的频率列表,即每一个常用值的出现次数除以总行数(如果most_common_vals为空,则此列为空) 
histogram_bounds   将列值划分成大小接近的组的值列表。如果存在most_common_vals,其中的值会被直方图计算所忽略(如果列类型没有一个<操作符或者most_common_vals等于整个值集合,则此列为空) 
correlation        物理行顺序和列值逻辑顺序之间的统计关联。其范围从-1到 1。当值接近-1或 1时,在列上的一个索引扫描被认为比值接近0时的代价更低,因为这种情况减少了对磁盘的随机访问(如果列数据类型不具有一个<操作符,则此列为空) 
most_common_elems  在列值中,最经常出现的非空元素列表(对标度类型为空) 
most_common_elem_freqs 最常用元素值的频度列表,即含有至少一个给定值实例的行的分数。在每个元素的频度之后有二至三个附加值,它们是每个元素频度的最小和最大值,以及可选的空元素的频度(如果most_common_elems为空,则此列为空) 
elem_count_histogram  在列值中可区分非空元素值计数的一个直方图,后面跟随可区分非空元素的平均数(对于标度类型为空) 
示例
select * from pg_stats where tablename='bmsql_customer';
schemaname|tablename     |attname       |inherited|null_frac|avg_width|n_distinct |most_common_vals                                                                                                                                                                                                                                               |most_common_freqs                                                                                                                                                                                                                                              |histogram_bounds                                                                                                                                                                                                                                               |correlation  |most_common_elems|most_common_elem_freqs|elem_count_histogram|
----------|--------------|--------------|---------|---------|---------|-----------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-------------|-----------------|----------------------|--------------------|
public    |bmsql_customer|c_w_id        |false    |      0.0|        4|        1.0|{1}                                                                                                                                                                                                                                                            |{1.0}                                                                                                                                                                                                                                                          |                                                                                                                                                                                                                                                               |          1.0|                 |null                  |null                |
public    |bmsql_customer|c_d_id        |false    |      0.0|        4|       10.0|{1,2,3,4,5,6,7,8,9,10}                                                                                                                                                                                                                                         |{0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1}                                                                                                                                                                                                                      |                                                                                                                                                                                                                                                               |   0.80067205|                 |null                  |null                |
public    |bmsql_customer|c_state       |false    |      0.0|        3|      676.0|{hv,sd,gf,cu,eg,pb,wq,bx,nd,oy,ri,zr,ci,ul,wa,ap,at,kf,qp,zq,ae,ig,iq,kx,ld,mi,pp,rg,rr,su,xe,bh,bo,ed,iy,mc,oi,pf,pl,uh,yh,zs,bi,bk,bz,eb,fw,jb,kb,oo,px,uf,wy,gc,ib,is,qe,qj,rb,ry,tn,tw,ut,uv,vt,wr,xa,ah,dj,im,it,ko,mm,mq,ng,us,vi,wn,wp,yn,zb,da,eh,fb,fg|{0.0021,0.0021,0.0020666667,0.0020333333,0.0020333333,0.0020333333,0.0020333333,0.002,0.002,0.002,0.002,0.002,0.0019666667,0.0019666667,0.0019666667,0.0019333333,0.0019333333,0.0019333333,0.0019333333,0.0019333333,0.0019,0.0019,0.0019,0.0019,0.0019,0.0019|{aa,ag,ao,av,bb,bj,bq,bw,ce,ck,cq,cw,dc,di,dp,dv,ec,ek,eq,ew,fd,fk,fp,fv,gd,gk,gr,gx,hd,hj,ho,hu,ic,ik,iu,jc,jh,jn,ju,ka,kh,kn,ku,la,li,lo,lu,ma,mh,mo,mv,nc,nj,np,nv,ob,oj,or,ow,ph,po,pv,qc,qk,qs,qx,re,rm,rt,sa,sh,sn,st,ta,tg,tl,ts,tz,ui,up,uy,vd,vk,vq,vw|  0.010709598|                 |null                  |null                |
public    |bmsql_customer|c_credit      |false    |      0.0|        3|        2.0|{gc,bc}                                                                                                                                                                                                                                                        |{0.90096664,0.09903333}                                                                                                                                                                                                                                        |                                                                                                                                                                                                                                                               |    0.7925414|                 |null                  |null                |
public    |bmsql_customer|c_credit_lim  |false    |      0.0|        5|        1.0|{50000.00}                                                                                                                                                                                                                                                     |{1.0}                                                                                                                                                                                                                                                          |                                                                                                                                                                                                                                                               |          1.0|                 |null                  |null                |
public    |bmsql_customer|c_delivery_cnt|false    |      0.0|        4|       57.0|{2,1,3,4,5,6,7,8,9,10,11,12,14,13,15,16,17,18,19,21,20,23,22,24,25,26,43,36,38,40,42,45,47,51,41,49,27,28,29,30,31,35,39,46,32,33,34,37,44,48,50,52,53,57}                                                                                                     |{0.266,0.2491,0.16836667,0.09793333,0.061933335,0.039666668,0.027366666,0.020633332,0.0144,0.009933333,0.0073666666,0.0062,0.004166667,0.004,0.0033666666,0.0031333333,0.0031333333,0.0023666667,0.0016333334,0.0014,0.0013666666,0.00096667,0.00086667,0.0007,|{54,58,60}                                                                                                                                                                                                                                                     |   0.26039952|                 |null                  |null                |
public    |bmsql_customer|c_street_2    |false    |      0.0|       15|       -1.0|                                                                                                                                                                                                                                                               |null                                                                                                                                                                                                                                                           |{a03zzkwwlfdmuk7,actyehl7karc,akxu0rnegjjzzo,assmjneelbuexzybklc,b0ujw8srqmd0j,bducvtl7e4nwew,bm2ns2wmqjhp0fwtp,bu3bdw0jnhpncf0d,c6rzmoujut9ly38,cgs2uwghgnrurxdakok,cohz9yixprpy,cwjfimkginah7o,d921u3jcei,dh8p8dlctgfwes6wp6z,dpiqmfbqagfdbugoilt,dyb38ri7lqq|  0.005798556|                 |null                  |null                |
public    |bmsql_customer|c_city        |false    |      0.0|       15|       -1.0|                                                                                                                                                                                                                                                               |null                                                                                                                                                                                                                                                           |{a01iygmihvxcdkongd,ackhbp4fubb1e,ajx27wuqnppfla,aru4fwwwqj,azkftl13hi2x3t0v,bcoc6ob2hw9cy2n,bkkkagbqoekgzjsn,bruzinteje5binf7wx,c1eggv5o63rzc46bp49l,ce14udibanv0g,cmer4nmqdhbwa6so,cuin42dmetiwtmtmtan,d6pfvczlimprlz,dhcr1hpvug00p,dpuecnjrniazzsnvpfcn,dxwu| 0.0036391425|                 |null                  |null                |
public    |bmsql_customer|c_middle      |false    |      0.0|        3|        1.0|{oe}                                                                                                                                                                                                                                                           |{1.0}                                                                                                                                                                                                                                                          |                                                                                                                                                                                                                                                               |          1.0|                 |null                  |null                |
public    |bmsql_customer|c_phone       |false    |      0.0|       17|       -1.0|                                                                                                                                                                                                                                                               |null                                                                                                                                                                                                                                                           |{0000720554618279,0094493104245076,0194298937558310,0291388573568474,0386565393374964,0490430978121291,0589927576378785,0694300579363929,0790469558694240,0893798134357361,0996763766620530,1105087662319494,1206554809150304,1302687329603163,1401486226018001|-0.0010758232|                 |null                  |null                |
public    |bmsql_customer|c_id          |false    |      0.0|        4|     3000.0|{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88|{0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0|{101,129,158,187,216,245,274,303,332,361,390,419,448,477,506,535,564,593,622,651,680,709,738,767,796,825,854,883,912,941,970,999,1028,1057,1086,1115,1144,1173,1202,1231,1260,1289,1318,1347,1376,1405,1434,1463,1492,1521,1550,1579,1608,1637,1666,1695,1724,1|   0.06640282|                 |null                  |null                |
public    |bmsql_customer|c_discount    |false    |      0.0|        4|-0.16623333|{0.3390,0.3427,0.0496,0.3632,0.0595,0.1933,0.2698,0.3200,0.3305,0.3595,0.3767,0.4278,0.0469,0.0648,0.0975,0.1194,0.1217,0.1254,0.1313,0.1331,0.1449,0.1538,0.1558,0.1971,0.2143,0.2533,0.3289,0.3294,0.3426,0.3443,0.3622,0.3856,0.4008,0.4095,0.4098,0.4198,0.|{0.0006,0.00056667,0.00053333,0.0005,0.00046667,0.00046667,0.00046667,0.00046667,0.00046667,0.00046667,0.00046667,0.00046667,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.00043333,0.0004333|{0.0000,0.0048,0.0097,0.0151,0.0207,0.0268,0.0323,0.0374,0.0431,0.0486,0.0533,0.0584,0.0633,0.0681,0.0727,0.0775,0.0828,0.0878,0.0923,0.0971,0.1021,0.1069,0.1115,0.1162,0.1209,0.1261,0.1310,0.1359,0.1408,0.1457,0.1506,0.1555,0.1608,0.1655,0.1707,0.1752,0.|-0.0043730875|                 |null                  |null                |
public    |bmsql_customer|c_payment_cnt |false    |      0.0|        4|       51.0|{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,25,23,24,28,31,34,36,26,35,27,30,33,37,29,38,32,39,43,41,44,47,40,42,46,49,51}                                                                                                                       |{0.3586,0.19516666,0.11313333,0.07456667,0.055033334,0.042466667,0.033566665,0.026033333,0.018466666,0.015533334,0.0117,0.009666666,0.0073666666,0.0065333336,0.0057,0.004766667,0.0042666667,0.003,0.0022333334,0.0017,0.0016,0.0011,0.00086667,0.00076667,0.0|{45,48,50}                                                                                                                                                                                                                                                     |   0.26628405|                 |null                  |null                |
public    |bmsql_customer|c_ytd_payment |false    |      0.0|        6|-0.63776666|{10.00,40.77,74.92,98.77,283.32,310.02,893.83,1002.44,1022.16,1050.17,1142.25,1490.76,1557.29,1649.96,1680.64,1703.44,1782.84,1805.01,1851.50,1896.63,1917.02,2005.20,2061.49,2097.47,2114.43,2328.48,2391.44,2400.93,2426.32,2469.44,2510.30,2562.95,2605.37,2|{0.3586,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.000|{12.14,174.95,344.12,502.36,660.53,794.18,927.06,1058.91,1204.70,1367.97,1522.46,1658.55,1798.19,1916.90,2041.17,2186.42,2313.44,2451.00,2576.84,2692.39,2806.35,2905.92,3012.45,3114.66,3227.46,3333.73,3444.66,3553.28,3659.34,3769.09,3883.75,3987.61,4104.3|   0.20263122|                 |null                  |null                |
public    |bmsql_customer|c_street_1    |false    |      0.0|       15|       -1.0|                                                                                                                                                                                                                                                               |null                                                                                                                                                                                                                                                           |{a00vmhvhp2fa,acsdru3juyg,ajtbxirem5tputpf,at1530k2en1z3eu6h,b38f64pyfm5te48eqx2,bfhqrj7tpg8g,bn83bawhgwky2i4d4,bvaajvtiz65zer,c5xnswweyqgdqgdxu,cfnvvcyynbwj0,cn288xr4nricp3kqznmw,cvxtz6ev8tdjhdtpe,d99wbi0odu,dh9ypbrmfgoj2vb5ro,dphvztuw5ggo4x,dxoxuqyr3ry,|-0.0016376677|                 |null                  |null                |
public    |bmsql_customer|c_data        |false    |      0.0|      409|       -1.0|                                                                                                                                                                                                                                                               |null                                                                                                                                                                                                                                                           |{a04viyysbyos01zoser0z07x85iyyyrgfnoqyeloimqilqxq4t0dtw5tc0p9m2emrvfxn4vccq0azdw2exbvuodwniqq2p3gkt4hlxciizwj1xbv2cbkvogibehwsdz8fqd38jauxhhbb9qurd0zx1wxnyq2mbzntkhpiky7nsaybernildllhrss4drgwgdm95hx0zd21emshqsaic5fkfsctbguszhfagxvit24lthpfmcs6ss1qkzrhb02c| -0.014546744|                 |null                  |null                |
public    |bmsql_customer|c_first       |false    |      0.0|       12|       -1.0|                                                                                                                                                                                                                                                               |null                                                                                                                                                                                                                                                           |{a00sipdhjlrfblz,acuvx1sdnk8hgn,akx25jrbf2,at3ellj7,b3qrcozp1,beedzsux,bmliqky7,bus1frzoa08irwks,c7e5d0ba,cgmobyv8tq07d,cohgkkgj,cwacbex2ajy66i,d7rnzjgxynb,dglh9vpc6e,dodgclyxuylf,dvpcjd9f9hfhmtax,e7jdul0ml6hfahr,eg3kzrqnp,eopoizmogdt6kl,ewhkjlpgokb26,f9l| -0.009829705|                 |null                  |null                |
public    |bmsql_customer|c_last        |false    |      0.0|       12|     1000.0|{ationantiought,pripreseing,antibarese,oughtoughtcally,baresepri,ationesecally,ableationese,callypripri,esecallypri,callyeingcally,pripresation,prioughtcally,ationesepri,ationeseeing,esepresought,ationableeing,prescallycally,eseeingcally,eseationeing,bara|{0.017633334,0.017033333,0.016866667,0.0136,0.0067,0.0065,0.0064666667,0.0064,0.0063333334,0.0063,0.0062666666,0.006233333,0.0061666667,0.0061333333,0.0061,0.0060666665,0.0060666665,0.0060333335,0.006,0.0059,0.0059,0.005866667,0.005866667,0.005866667,0.00|{ableableable,ableantically,ableationable,ableationpres,ablebareing,ablecallybar,ableeingbar,ableeseanti,ableoughtanti,ableoughtpres,ableprespri,antiableation,antiantically,antiationanti,antibarbar,anticallypri,antieingpri,antioughtable,antipreseing,antip| 0.0035713348|                 |null                  |null                |
public    |bmsql_customer|c_balance     |false    |      0.0|        7|    -0.8715|{-10.00,-12885.34,-12135.82,-8433.36,-6336.20,-5140.10,-4830.48,-3446.05,-3276.68,-2638.97,-2356.89,-1619.21,-1055.56,-1008.78,-918.61,-425.33,-367.67,-303.05,-244.95,-221.68,822.42,931.83,1392.76,1650.58,1652.92,1656.46,2002.80,2274.70,2309.88,2486.46,26|{0.12666667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0.00006667,0|{-120589.86,-42179.18,-32035.78,-27128.04,-23178.40,-20005.91,-17595.02,-15551.48,-13955.66,-12595.12,-11360.08,-10263.07,-9156.81,-8282.22,-7526.06,-6846.82,-6172.50,-5523.57,-4912.86,-4508.90,-4128.93,-3722.89,-3337.31,-2970.99,-2599.08,-2207.51,-1889.4|   0.10737501|                 |null                  |null                |
public    |bmsql_customer|c_zip         |false    |      0.0|       10|-0.31656668|{134711111,803711111,188211111,241111111,242211111,272311111,430511111,534911111,677811111,720611111,755311111,786811111,877011111,929011111,938111111,964911111,004411111,025211111,034411111,043811111,050311111,108511111,112411111,134111111,185211111,2150|{0.00036667,0.00036667,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0|{000011111,011211111,021411111,031711111,042311111,053011111,063511111,073411111,083511111,093911111,105111111,116511111,126111111,137211111,147911111,157611111,167811111,178811111,188611111,198111111,208211111,218011111,228411111,237811111,247811111,2577| -0.007099603|                 |null                  |null                |
public    |bmsql_customer|c_since       |false    |      0.0|        8|-0.20226666|{"2021-10-25 14:29:25.057","2021-10-25 14:29:28.019","2021-10-25 14:29:30.016","2021-10-25 14:29:30.162","2021-10-25 14:29:30.208","2021-10-25 14:29:30.225","2021-10-25 14:29:32.674","2021-10-25 14:29:36.477","2021-10-25 14:29:38.353","2021-10-25 14:29:38|{0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.00033333,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.000|{"2021-10-25 14:29:24.854","2021-10-25 14:29:24.973","2021-10-25 14:29:25.081","2021-10-25 14:29:25.17","2021-10-25 14:29:25.241","2021-10-25 14:29:25.314","2021-10-25 14:29:25.435","2021-10-25 14:29:25.554","2021-10-25 14:29:25.708","2021-10-25 14:29:25.|    0.7676967|                 |null                  |null                |

create view pg_stats with (security_barrier) as
    select
        nspname as schemaname,
        relname as tablename,
        attname as attname,
        stainherit as inherited,
        stanullfrac as null_frac,
        stawidth as avg_width,
        stadistinct as n_distinct,
        case
            when stakind1 = 1 then stavalues1
            when stakind2 = 1 then stavalues2
            when stakind3 = 1 then stavalues3
            when stakind4 = 1 then stavalues4
            when stakind5 = 1 then stavalues5
        end as most_common_vals,
        case
            when stakind1 = 1 then stanumbers1
            when stakind2 = 1 then stanumbers2
            when stakind3 = 1 then stanumbers3
            when stakind4 = 1 then stanumbers4
            when stakind5 = 1 then stanumbers5
        end as most_common_freqs,
        case
            when stakind1 = 2 then stavalues1
            when stakind2 = 2 then stavalues2
            when stakind3 = 2 then stavalues3
            when stakind4 = 2 then stavalues4
            when stakind5 = 2 then stavalues5
        end as histogram_bounds,
        case
            when stakind1 = 3 then stanumbers1[1]
            when stakind2 = 3 then stanumbers2[1]
            when stakind3 = 3 then stanumbers3[1]
            when stakind4 = 3 then stanumbers4[1]
            when stakind5 = 3 then stanumbers5[1]
        end as correlation,
        case
            when stakind1 = 4 then stavalues1
            when stakind2 = 4 then stavalues2
            when stakind3 = 4 then stavalues3
            when stakind4 = 4 then stavalues4
            when stakind5 = 4 then stavalues5
        end as most_common_elems,
        case
            when stakind1 = 4 then stanumbers1
            when stakind2 = 4 then stanumbers2
            when stakind3 = 4 then stanumbers3
            when stakind4 = 4 then stanumbers4
            when stakind5 = 4 then stanumbers5
        end as most_common_elem_freqs,
        case
            when stakind1 = 5 then stanumbers1
            when stakind2 = 5 then stanumbers2
            when stakind3 = 5 then stanumbers3
            when stakind4 = 5 then stanumbers4
            when stakind5 = 5 then stanumbers5
        end as elem_count_histogram
    from pg_statistic s join pg_class c on (c.oid = s.starelid)
         join pg_attribute a on (c.oid = attrelid and attnum = s.staattnum)
         left join pg_namespace n on (n.oid = c.relnamespace)
    where not attisdropped
    and has_column_privilege(c.oid, a.attnum, 'select')
    and (c.relrowsecurity = false or not row_security_active(c.oid));

pg统计信息之列correlation

https://www.modb.pro/db/33375

最后修改时间:2021-11-08 16:33:27
「喜欢文章,快来给作者赞赏墨值吧」
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论