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
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
