日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

學無先后，達者為師

網站首頁 編程語言 正文

PostgreSQL游標與索引選擇實例詳細介紹_PostgreSQL

作者：foucus、 ? 更新時間： 2022-11-07 編程語言

之前有寫過一個案例，order by limit因為數據分布不均而選擇了錯誤的索引，這是由于優化器沒法判斷數據的分布關系，默認認為數據分布是均勻的所導致的。

而除了limit，當我們在使用游標時也要注意有可能會出現類似的情況。而往往這類在存儲過程中的SQL我們更難發現其選擇了錯誤的執行計劃，所以需要注意。

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、創建兩個索引，也就是本文需要重點關注的，到底走哪個索引更劃算

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的執行計劃，走了正確的索引

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時，會發現執行計劃出現了偏差

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參數進行自動優化，而該參數默認是0.1，表示只檢索前10%的行進行預估，這就和limit有點異曲同工的味道了。

因為對于這張表，優化器認為數據是均勻分布的，而實際上，數據分布是不均勻的，c1=200 and c2=200的記錄在表的末端。當我們在游標中只檢索了前10%的行，所以會得到一個錯誤的執行計劃。

具體的細節我們可以在parsenodes.h和planner.c中看到：

當使用cursor或者SPI_PREPARE_CURSOR函數時，會設置CURSOR_OPT_FAST_PLAN標志位，然后就會根據cursor_tuple_fraction參數對SQL進行自動優化，所以對于一些數據分布不均的情況，可能就會

導致選擇了錯誤的執行計劃。
	/* 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;
	}

原文鏈接：https://blog.csdn.net/weixin_39540651/article/details/126857447

欄目分類
最近更新