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
http://www.sunline.cc/db/33375
最后修改時間：2021-11-08 16:33:27
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
