PostgreSQL DBA(11) - 統計資訊在計算選擇率上的應用#1
本節以舉例的形式簡單介紹了PG資料庫中統計資訊(高頻值MCV和直方圖HISTOGRAM)在單條件等值查詢和單條件比較查詢計算選擇率上的應用。
一、計算選擇率
單條件等值查詢
測試資料生成指令碼如下:
insert into t_grxx(dwbh,grbh,xm,xb,nl)
select generate_series(1,100000)/10||'',generate_series(1,100000),'XM'||generate_series(1,100000),
(case when (floor(random()*2)=0) then '男' else '女' end),floor(random() * 100 + 1)::int;
SQL指令碼和執行計劃:
testdb=# explain verbose select * from t_grxx where dwbh = '6323';
QUERY PLAN
----------------------------------------------------------------------------------------
Index Scan using idx_t_grxx_dwbh on public.t_grxx (cost=0.29..46.90 rows=30 width=24)
Output: dwbh, grbh, xm, xb, nl
Index Cond: ((t_grxx.dwbh)::text = '6323'::text)
(3 rows)
testdb=# explain verbose select * from t_grxx where dwbh = '24';
QUERY PLAN
----------------------------------------------------------------------------------------
Index Scan using idx_t_grxx_dwbh on public.t_grxx (cost=0.29..20.29 rows=10 width=24)
Output: dwbh, grbh, xm, xb, nl
Index Cond: ((t_grxx.dwbh)::text = '24'::text)
(3 rows)
雖然都是等值查詢,但執行計劃中dwbh='6323'和dwbh='24'返回的行數(rows)卻不一樣,一個是rows=30,一個是rows=10,從生成資料的指令碼來看,'6323'和'24'的rows應該是一樣的,但執行計劃顯示的結果卻不同,原因是計算選擇率時'6323'出現在高頻值中,因此與其他值不同.
計算過程解析
查詢該列的統計資訊:
testdb=# \x
Expanded display is on.
testdb=# select starelid,staattnum,stainherit,stanullfrac,stawidth,stadistinct
from pg_statistic
where starelid = 16742 and staattnum = 1;
-[ RECORD 1 ]---------
starelid | 16742
staattnum | 1
stainherit | f
stanullfrac | 0
stawidth | 4
stadistinct | -0.10015
testdb=# select staattnum,stakind1,staop1,stanumbers1,stavalues1,
stakind2,staop2,stanumbers2,stavalues2,
stakind3,staop3,stanumbers3,stavalues3
from pg_statistic
where starelid = 16742
and staattnum = 1;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
staattnum | 1
stakind1 | 1
staop1 | 98
stanumbers1 | {0.0003}
stavalues1 | {6323}
stakind2 | 2
staop2 | 664
stanumbers2 |
stavalues2 | {0,1092,1181,1265,1350,1443,1529,1619,171,1797,1887,1972,2058,2151,2240,2334,2423,2520,2618,271,2798,2892,2987,3076,3162,3246,3332,3421,3510,3597,3685,3777,3860,3956,4051,4136,4227,4317,4408,45,4590,4671,4760,4850,4933,5025,5120,5210,5300,5396,548,5570,5656,5747,5835,5931,6017,6109,6190,6281,6374,6465,6566,6649,6735,6830,6921,7012,7101,7192,7278,737,7455,7544,7630,7711,7801,7895,7988,8081,8167,8260,8344,8430,8520,8615,8707,8809,8901,8997,9083,918,9272,9367,9451,9538,9630,9729,982,9904,9999}
stakind3 | 3
staop3 | 664
stanumbers3 | {0.819578}
stavalues3 |
條件語句是等值表示式,使用的運算子是"="(字串等值比較,texteq/eqsel/eqjoinsel),因此使用的統計資訊是高頻值MCV(注意:staop1=98,這是字串等值比較).'6323'出現在高頻值中,選擇率為0.0003,因此rows=100,000x0.0003=30.而'24'沒有出現在高頻值中,選擇率=(1-0.0003)/abs(stadistinct)/Tuples=(1-0.0003)/abs(-0.10015)/100000=0.000099820269595606590000,rows=(1-0.0003)/abs(stadistinct)=10(取整).
單條件比較查詢
測試指令碼:
testdb=# create table t_int(c1 int,c2 varchar(20));
CREATE TABLE
testdb=#
testdb=# insert into t_int select generate_series(1,100000)/10,'C2'||generate_series(1,100000)/100;
INSERT 0 100000
testdb=# ANALYZE t_int;
ANALYZE
testdb=# select oid from pg_class where relname='t_int';
oid
-------
16755
(1 row)
查詢c1列的統計資訊
testdb=# \x
Expanded display is on.
testdb=# select staattnum,stakind1,staop1,stanumbers1,stavalues1,
testdb-# stakind2,staop2,stanumbers2,stavalues2,
testdb-# stakind3,staop3,stanumbers3,stavalues3
testdb-# from pg_statistic
testdb-# where starelid = 16755
testdb-# and staattnum = 1;
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
staattnum | 1
stakind1 | 1
staop1 | 96
stanumbers1 | {0.0003}
stavalues1 | {8306}
stakind2 | 2
staop2 | 97
stanumbers2 |
stavalues2 | {0,108,215,318,416,514,611,704,809,912,1015,1111,1217,1312,1410,1511,1607,1705,1805,1903,2002,2094,2189,2287,2388,2487,2592,2695,2795,2896,2998,3112,3213,3304,3408,3507,3606,3707,3798,3908,4004,4106,4205,4312,4413,4505,4606,4714,4821,4910,5014,5118,5220,5321,5418,5516,5613,5709,5807,5916,6014,6127,6235,6341,6447,6548,6648,6741,6840,6931,7032,7131,7234,7330,7433,7532,7626,7727,7827,7925,8020,8120,8217,8322,8420,8525,8630,8730,8831,8934,9032,9128,9223,9323,9425,9527,9612,9706,9804,9904,9999}
stakind3 | 3
staop3 | 97
stanumbers3 | {1}
stavalues3 |
查詢語句:
testdb=# explain verbose select * from t_int where c1 < 2312;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on public.t_int (cost=0.00..1790.00 rows=23231 width=9)
Output: c1, c2
Filter: (t_int.c1 < 2312)
(3 rows)
SQL使用了非等值查詢(<,int4lt/scalarltsel/scalarltjoinsel),結合統計資訊中MCV和直方圖使用,
由於2312均小於MCV中的值,因此根據MCV得出的選擇率為0.
根據直方圖計算的選擇率=(1-0.0003)x(23+(2312-2287-1)/(2388-2287))/100=0.2323065247,rows=100000x0.2323065247=23231(取整)
其中:
除高頻值外的其他數值佔比=(1-0.0003)
直方圖中的總槽數=陣列元素總數-1即101-1=100
2312落在第24個槽中,槽佔比=(23+(2312-2287-1)/(2388-2287))/100
二、參考資料
pg_statistic
pg_statistic.h
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/6906/viewspace-2374841/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- PostgreSQL DBA(12) - 統計資訊在計算選擇率上的應用#2SQL
- PostgreSQL DBA(10) - 統計資訊SQL
- PostgreSQL中統計資訊計算SQL
- 【cbo計算公式】Join 選擇率(六)公式
- 【cbo計算公式】單表選擇率(二)公式
- 【cbo計算公式】Frequenccy 直方圖選擇率(三)公式直方圖
- oracle實驗記錄 (oracle單表選擇率與基數計算(1))Oracle
- SAMPLE語句在統計資訊收集中應用
- 基於CBO優化器謂詞選擇率的計算方法優化
- CBO中基數(cardinality)、可選擇率(selectivity)的計算公式公式
- 基於CBO最佳化器謂詞選擇率的計算方法
- 統計資訊不正確導致執行計劃的錯誤選擇
- 《論可計算數及其在判定上的應用》簡單理解
- 邊緣計算軟體的選擇
- 【cbo計算公式】Height-balance直方圖選擇率(四)公式直方圖
- 11g組合索引的統計資訊會用於對錶返回基數的計算索引
- Oracle直方圖統計資訊的應用Oracle直方圖
- 響應式設計:做出正確的選擇——資料資訊圖
- PostgreSQL百萬級每秒的流式實時統計應用SQL
- PostgreSQL DBA(4) - PG 11 New Features#1SQL
- PostgreSQL統計資訊的幾個重要檢視SQL
- 無線通訊在智慧公交系統上的設計應用
- oracle實驗記錄 (oracle單表選擇率與基數計算(2))Oracle
- oracle實驗記錄 (oracle單表選擇率與基數計算(3))Oracle
- MySQL中Innodb如何計算索引的統計資訊?MySql索引
- 微軟在Windows 11開發版裡帶來新的應用選擇器微軟Windows
- UML 在系統設計時的應用
- 雲端計算要選擇什麼模式的呢?雲端計算又有幾種模式?模式
- 留存率計算
- CSS 選擇器權重計算規則CSS
- PostgreSQL11preview-平行計算增強彙總SQLView
- [統計資訊系列7] Oracle 11g的自動統計資訊收集Oracle
- Forrester企業虛擬化率推進雲端計算應用REST
- 減少計算、簡化架構——TDengine在灌區資訊化平臺中的應用架構
- JuiceFS 在火山引擎邊緣計算的應用實踐UI
- 數學在計算機圖形學中的應用計算機
- Oracle11g 統計資訊-----統計資訊自動收集任務Oracle
- Oracle11g 統計資訊——統計資訊自動收集任務Oracle