PostgreSQL DBA(12) - 統計資訊在計算選擇率上的應用#2
本節以舉例的形式簡單介紹了PG資料庫中統計資訊(頻值MCV和直方圖HISTOGRAM)在多條件查詢計算選擇率上的應用。
一、計算選擇率
測試資料生成指令碼詳見上節,這裡不再累述.
多條件單列查詢
SQL指令碼和執行計劃:
testdb=# explain verbose select * from t_int where c1 < 2312 and c1 > 500;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on public.t_int (cost=0.00..2040.00 rows=18375 width=9)
Output: c1, c2
Filter: ((t_int.c1 < 2312) AND (t_int.c1 > 500))
(3 rows)
SQL語句有兩個約束條件:c1 < 2312 和 c1 > 500,是同一個列,統計資訊中並沒有對應">"運算子的統計資訊,PG實際上是把">"轉換為"<="進行處理.
即"c1 < 2312 and c1 > 500"的選擇率="c1 < 2312"選擇率 - "c1 <= 500"選擇率:
c1 < 2312 選擇率=(1-0.0003)*(23+(2312-2287-1)/(2388-2287))/100=.232306525
c1 <= 500 選擇率=(1-0.0003)*(4+(500-416)/(514-416))/100=.048556857
c1 < 2312 and c1 > 500選擇率=.232306525 - .048556857=.183749668,執行計劃中的rows=18375(取整)
多條件多列查詢
SQL指令碼和執行計劃:
testdb=# explain verbose select * from t_int where c1 < 2312 and c2 = 'TEST';
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on public.t_int (cost=0.00..2040.00 rows=23 width=9)
Output: c1, c2
Filter: ((t_int.c1 < 2312) AND ((t_int.c2)::text = 'TEST'::text))
(3 rows)
SQL語句有兩個約束條件:c1 < 2312 and c2 = 'TEST'.
由於存在不同的兩個列,運算子是AND,PG計算選擇率的時候使用了機率論的方法,即:
P(A and B)=P(A) x P(B)
此例中,A=c1 < 2312,B=c2='TEST'
從上節已知,P(A)=.232306525,下面計算P(B)
c2 = 'TEST',運算子是"=",使用高頻值進行計算:
testdb=# \x
Expanded display is on.
testdb=# select staattnum,stakind1,staop1,stanumbers1,stavalues1,
stakind2,staop2,stanumbers2,stavalues2,
stakind3,staop3,stanumbers3,stavalues3
from pg_statistic
where starelid = 16755
and staattnum = 2;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
staattnum | 2
stakind1 | 1
staop1 | 98
stanumbers1 | {0.0015,0.00146667,0.00143333,0.0014,0.0014,0.0014,0.0014,0.0014}
stavalues1 | {C2685,C2999,C2279,C2399,C2556,C2723,C2777,C2833}
stakind2 | 2
staop2 | 664
stanumbers2 |
stavalues2 | {C20,C2106,C2116,C2125,C2134,C2142,C2151,C2160,C2169,C2178,C2187,C2196,C2203,C2212,C2220,C223,C2239,C2248,C2257,C2266,C2276,C2286,C2296,C2304,C2313,C2322,C2330,C2340,C235,C2358,C2367,C2376,C2385,C2394,C2403,C2411,C2421,C2430,C244,C2449,C2457,C2466,C2476,C2485,C2493,C2502,C2511,C252,C2529,C2538,C2547,C2555,C2565,C2574,C2583,C2592,C2600,C2610,C2620,C263,C264,C2649,C2658,C2666,C2674,C2683,C2693,C2701,C271,C2719,C2729,C2739,C2748,C2757,C2765,C2774,C2784,C2793,C2801,C2810,C2819,C2828,C2839,C2847,C2856,C2865,C2875,C2884,C2893,C2901,C2910,C2919,C2928,C2937,C2946,C2955,C2963,C2971,C2980,C299,C2998}
stakind3 | 3
staop3 | 664
stanumbers3 | {0.829913}
stavalues3 |
testdb=#
testdb=# select starelid,staattnum,stainherit,stanullfrac,stawidth,stadistinct
testdb-# from pg_statistic
testdb-# where starelid = 16755 and staattnum = 2;
-[ RECORD 1 ]------
starelid | 16755
staattnum | 2
stainherit | f
stanullfrac | 0
stawidth | 5
stadistinct | 1000
從以上統計資訊中可知,'TEST'不在高頻值中,包括高頻值共有1000個不同值,因此c2='TEST'的選擇率=(1-高頻值比例)/(不同值個數 - 高頻值個數),其中高頻值比例=0.0015+0.00146667+0.00143333+0.0014+0.0014+0.0014+0.0014+0.0014=.0114,不同值個數=1000,高頻值個數=6,代入公式,計算得到選擇率P(B)=.000994567
P(A and B)=P(A) x P(B)=.232306525 x .000994567=.000231044,執行計劃中的rows=.000231044*100000=23
二、參考資料
pg_statistic
pg_statistic.h
Row Estimation Examples
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/6906/viewspace-2374840/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- PostgreSQL DBA(11) - 統計資訊在計算選擇率上的應用#1SQL
- PostgreSQL DBA(10) - 統計資訊SQL
- PostgreSQL中統計資訊計算SQL
- 【cbo計算公式】Join 選擇率(六)公式
- 【cbo計算公式】單表選擇率(二)公式
- 【cbo計算公式】Frequenccy 直方圖選擇率(三)公式直方圖
- oracle實驗記錄 (oracle單表選擇率與基數計算(2))Oracle
- SAMPLE語句在統計資訊收集中應用
- 基於CBO優化器謂詞選擇率的計算方法優化
- CBO中基數(cardinality)、可選擇率(selectivity)的計算公式公式
- 基於CBO最佳化器謂詞選擇率的計算方法
- 統計資訊不正確導致執行計劃的錯誤選擇
- 《論可計算數及其在判定上的應用》簡單理解
- 邊緣計算軟體的選擇
- 【cbo計算公式】Height-balance直方圖選擇率(四)公式直方圖
- Oracle直方圖統計資訊的應用Oracle直方圖
- 響應式設計:做出正確的選擇——資料資訊圖
- PostgreSQL百萬級每秒的流式實時統計應用SQL
- PostgreSQL統計資訊的幾個重要檢視SQL
- 無線通訊在智慧公交系統上的設計應用
- oracle實驗記錄 (oracle單表選擇率與基數計算(1))Oracle
- oracle實驗記錄 (oracle單表選擇率與基數計算(3))Oracle
- PostgreSQL DBA(125) - PG 12(TPCC)SQL
- MySQL中Innodb如何計算索引的統計資訊?MySql索引
- UML 在系統設計時的應用
- 雲端計算要選擇什麼模式的呢?雲端計算又有幾種模式?模式
- 留存率計算
- CSS 選擇器權重計算規則CSS
- Forrester企業虛擬化率推進雲端計算應用REST
- 減少計算、簡化架構——TDengine在灌區資訊化平臺中的應用架構
- JuiceFS 在火山引擎邊緣計算的應用實踐UI
- 數學在計算機圖形學中的應用計算機
- 《初級會計電算化應用教程(金蝶KIS專業版)》——1.3電算化會計資訊系統
- 關於DBA工具的選擇
- 表示式計算(棧的應用)
- 1.1 計算機網路在資訊時代的作用計算機網路
- 【統計資訊】Oracle統計資訊Oracle
- PostgreSQL DBA(53) - PG 12 Generated columnsSQL