PostgreSQL游標與索引選擇實例詳細介紹
之前有寫過一個案例,order by limit因為數據分布不均而選擇了錯誤的索引,這是由于優(yōu)化器沒法判斷數據的分布關系,默認認為數據分布是均勻的所導致的。
而除了limit,當我們在使用游標時也要注意有可能會出現類似的情況。而往往這類在存儲過程中的SQL我們更難發(fā)現其選擇了錯誤的執(zhí)行計劃,所以需要注意。
1、建測試表
bill=# create table tbl (id int, c1 int, c2 int, c3 int, c4 int); CREATE TABLE
2、寫入一批隨機數據,ID從1到1000萬。
bill=# insert into tbl select generate_series(1,10000000), random()*100, random()*100, random()*100, random()*100; INSERT 0 10000000
3、寫入另一批100萬條數據,c1,c2 與前面1000萬的值不一樣。
bill=# insert into tbl select generate_series(10000001,11000000), 200,200,200,200; INSERT 0 1000000
4、創(chuàng)建兩個索引,也就是本文需要重點關注的,到底走哪個索引更劃算
bill=# create index idx_tbl_1 on tbl(id); CREATE INDEX bill=# create index idx_tbl_2 on tbl(c1,c2,c3,c4); CREATE INDEX
5、收集統計信息
bill=# vacuum analyze tbl; VACUUM
6、查看下面SQL的執(zhí)行計劃,走了正確的索引
bill=# explain select * from tbl where c1=200 and c2=200 order by id; QUERY PLAN ------------------------------------------------------------------------------------- Sort (cost=72109.20..72344.16 rows=93984 width=20) Sort Key: id -> Bitmap Heap Scan on tbl (cost=1392.77..60811.81 rows=93984 width=20) Recheck Cond: ((c1 = 200) AND (c2 = 200)) -> Bitmap Index Scan on idx_tbl_2 (cost=0.00..1369.28 rows=93984 width=0) Index Cond: ((c1 = 200) AND (c2 = 200)) (6 rows)
7、而當我們在游標中使用該SQL時,會發(fā)現執(zhí)行計劃出現了偏差
bill=# begin; BEGIN bill=*# explain declare tt cursor for select * from tbl where c1=200 and c2=200 order by id; QUERY PLAN ------------------------------------------------------------------------------- Index Scan using idx_tbl_1 on tbl (cost=0.43..329277.60 rows=93984 width=20) Filter: ((c1 = 200) AND (c2 = 200)) (2 rows)
為什么會出現這種情況呢,這其實是因為使用游標的SQL會根據cursor_tuple_fraction參數進行自動優(yōu)化,而該參數默認是0.1,表示只檢索前10%的行進行預估,這就和limit有點異曲同工的味道了。
因為對于這張表,優(yōu)化器認為數據是均勻分布的,而實際上,數據分布是不均勻的,c1=200 and c2=200的記錄在表的末端。當我們在游標中只檢索了前10%的行,所以會得到一個錯誤的執(zhí)行計劃。
具體的細節(jié)我們可以在parsenodes.h和planner.c中看到:
當使用cursor或者SPI_PREPARE_CURSOR函數時,會設置CURSOR_OPT_FAST_PLAN標志位,然后就會根據cursor_tuple_fraction參數對SQL進行自動優(yōu)化,所以對于一些數據分布不均的情況,可能就會
導致選擇了錯誤的執(zhí)行計劃。 /* Determine what fraction of the plan is likely to be scanned */ if (cursorOptions & CURSOR_OPT_FAST_PLAN) { /* * We have no real idea how many tuples the user will ultimately FETCH * from a cursor, but it is often the case that he doesn't want 'em * all, or would prefer a fast-start plan anyway so that he can * process some of the tuples sooner. Use a GUC parameter to decide * what fraction to optimize for. */ tuple_fraction = cursor_tuple_fraction; /* * We document cursor_tuple_fraction as simply being a fraction, which * means the edge cases 0 and 1 have to be treated specially here. We * convert 1 to 0 ("all the tuples") and 0 to a very small fraction. */ if (tuple_fraction >= 1.0) tuple_fraction = 0.0; else if (tuple_fraction <= 0.0) tuple_fraction = 1e-10; } else { /* Default assumption is we need all the tuples */ tuple_fraction = 0.0; }
到此這篇關于PostgreSQL游標與索引選擇實例詳細介紹的文章就介紹到這了,更多相關PostgreSQL游標與索引選擇內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
Postgresql psql文件執(zhí)行與批處理多個sql文件操作
這篇文章主要介紹了Postgresql psql文件執(zhí)行與批處理多個sql文件操作,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01Postgresql之時間戳long,TimeStamp,Date,String互轉方式
這篇文章主要介紹了Postgresql中的時間戳long,TimeStamp,Date,String互轉方式,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2023-03-03postgresql的now()與Oracle的sysdate區(qū)別說明
這篇文章主要介紹了postgresql的now()與Oracle的sysdate區(qū)別說明,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12