一次PostgreSQL行估算偏差导致的慢查询分析
本文為DBAPlus投稿文章, 原文鏈接：?http://dbaplus.cn/news-19-1514-1.html
一次PostgreSQL行估算偏差導致的慢查詢分析
問題
最近某業務系統上線了新功能，然后我們就發現PostgreSQL日志中多了很多慢查詢。這些SQL語句都比較相似，下面是其中一個SQL的explain analyze執行計劃輸出。
這個SQL執行了18秒，從上面的執行計劃不難看出，時間主要耗在兩次嵌套join時對子表的順序掃描(圖中藍線部分)。乘以5429的循環次數，每個join都要順序掃描2000多萬條記錄。
分析
既然是順序掃描惹的禍，那么在join列上加個索引是不是就可以了呢？
但是查看相關表定義后，發現在相關的表上已經有索引了；而且即使沒有索引，PG也應該可以通過Hash join回避大量的順序掃描。
再仔細看下執行計劃里的cost估算，發現PG估算出的rows只有1行，而實際是5429(圖中紅線部分)。看來是行數估算的巨大偏差導致PG選錯了執行計劃。
為什么估算行數偏差這么大？
通過嘗試，發現問題出在下面的過濾條件上。不加這個過濾條件估算行數和實際行數是基本吻合的，一加就相差的離譜。
Filter: (((zsize)::text = '2'::text) AND ((tmall_flg)::text = '1'::text))而上面的zsite的數據類型是char(10),tmall_flg的數據類型是int，難道是類型轉換惹的禍？ 在測試環境把嘗試去掉SQL里的類型轉換，發現執行時間立刻從10幾秒降到1秒以內。看來原因就是它了。
zsize::text = '2' AND tmall_flg::text = '1'==》
zsize = '2' AND tmall_flg = 1生產環境下，因為修改應用的SQL需要時間，臨時采用下面的回避措施
alter table bi_dm.tdm_wh_zl057_rt alter zsize type varchar(10);即把zsize的類型從char(10)改成varchar(10)(varchar到text的類型轉換不會影響結果行估算)。由于沒有改tmall_flg,修改之后，估算的行數是79行，依然不準確。但是這帶來的cost計算值的變化已經足以讓PG選擇索引掃描而不是順序掃描了。修改之后的執行時間只有311毫秒。
原理
PG如何估算結果行數
PG通過收集的統計信息估算結果行數，并且收集的統計信息也很全面，包括唯一值數量，頻繁值分布，柱狀圖和相關性，正常情況下應該是比較準確的?？聪旅娴睦?
-
無where條件
postgres=# explain select * from bi_dm.tdm_wh_zl057_rt;QUERY PLAN ---------------------------------------------------------------------------Seq Scan on tdm_wh_zl057_rt (cost=0.00..81318.21 rows=2026121 width=154) (1 row)全表數據的估算值來自pg_class
postgres=# select reltuples from pg_class where relname='tdm_wh_zl057_rt';reltuples -----------2026121 (1 row)估算值和實際值的誤差只有5%左右
postgres=# select count(*) from bi_dm.tdm_wh_zl057_rt;count ---------2103966 (1 row) -
帶等值where條件
postgres=# explain select * from bi_dm.tdm_wh_zl057_rt where tmall_flg = 1;QUERY PLAN --------------------------------------------------------------------------Seq Scan on tdm_wh_zl057_rt (cost=0.00..86403.32 rows=523129 width=154)Filter: (tmall_flg = 1) (2 rows)帶where條件后，PG根據pg_stats收集的列值分布信息估算出where條件的選擇率。tmall_flg = 1屬于頻繁值，most_common_freqs中直接記錄了其選擇率為0.258133322
postgres=# select * from pg_stats where tablename='tdm_wh_zl057_rt' and attname='tmall_flg'; -[ RECORD 1 ]----------+-------------------------------------- schemaname | bi_dm tablename | tdm_wh_zl057_rt attname | tmall_flg inherited | f null_frac | 0.00033333333 avg_width | 4 n_distinct | 5 most_common_vals | {0,1,2} most_common_freqs | {0.626866639,0.258133322,0.114566669} histogram_bounds | {3,4} correlation | 0.491312951 most_common_elems | most_common_elem_freqs | elem_count_histogram |結合總記錄數，可以算出估算結果行數。
postgres=# select 2026121*0.258133322;?column? ------------------523009.344503962 (1 row)估算值和實際值的誤差只有1%左右
postgres=# select count(*) from bi_dm.tdm_wh_zl057_rt where tmall_flg = 1;count --------532630 (1 row) -
帶等值where條件，且條件列帶類型轉換
postgres=# explain select * from bi_dm.tdm_wh_zl057_rt where tmall_flg::text = '1';QUERY PLAN -------------------------------------------------------------------------Seq Scan on tdm_wh_zl057_rt (cost=0.00..96561.46 rows=10131 width=155)Filter: ((tmall_flg)::text = '1'::text) (2 rows)一旦在條件列上引入包括類型轉換，函數調用之類的計算，PG就無法通過pg_stats計算選擇率了，于是籠統的采用了一個0.005的默認值。通過這個默認的選擇率計算的結果行數可能會和實際結果行數有巨大的偏差。如果where條件中這樣的列不止一個，偏差會被進一步放大。
postgres=# select 2026121*0.005;?column? -----------10130.605 (1 row)
相關代碼
src/include/utils/selfuncs.h:
/* default selectivity estimate for equalities such as "A = b" */ #define DEFAULT_EQ_SEL 0.005src/backend/utils/adt/selfuncs.c:
Datum eqsel(PG_FUNCTION_ARGS) { .../** If expression is not variable = something or something = variable, then* punt and return a default estimate.*/if (!get_restriction_variable(root, args, varRelid,&vardata, &other, &varonleft))PG_RETURN_FLOAT8(DEFAULT_EQ_SEL);總結
在條件列上引入計算帶來的危害:
回避方法:
將計算從列轉移到常量上
比如:
where c1 + 1 = 1000改成
where c1 = 1000 - 1改成其它等價的寫法
比如:
where substring(c2,2) = 'ZC'改成
where c2 >= 'ZC' and c2也可以改成更簡潔的正則表達式
where c2 ~ '^ZC'但是，正則表達式中如果帶了類似^$*這樣的內容，行數估算準確性也受一定的影響
總結
以上是生活随笔為你收集整理的一次PostgreSQL行估算偏差导致的慢查询分析的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： php使用curl下载指定大小的文件
- 下一篇： 人工智能与机器学习有哪些不同