数据库技术:PostgreSQL游标与索引选择实例详细介绍

之前有写过一个案例,order by limit因为数据分布不均而选择了错误的索引,这是由于优化器没法判断数据的分布关系,默认认为数据分布是均匀的所导致的。而除了limit,当我们在使用游标时也要注意

之前有写过一个案例,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、创建两个索引,也就是数据库技术:PostgreSQL游标与索引选择实例详细介绍需要重点关注的,到底走哪个索引更划算

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;  	}  

到此这篇关于postgresql游标与索引选择实例详细介绍的文章就介绍到这了,更多相关postgresql游标与索引选择内容请搜索<猴子技术宅>以前的文章或继续浏览下面的相关文章希望大家以后多多支持<猴子技术宅>!

需要了解更多数据库技术:PostgreSQL游标与索引选择实例详细介绍,都可以关注数据库技术分享栏目—猴子技术宅(www.ssfiction.com)

本文来自网络收集,不代表猴子技术宅立场,如涉及侵权请点击右边联系管理员删除。

如若转载,请注明出处:https://www.ssfiction.com/sqljc/1239993.html

(0)
上一篇 2022年9月19日 上午12:01
下一篇 2022年9月19日 上午12:02

精彩推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注