PostgreSQL游标与索引选择实例详细介绍
之前有写过一个案例,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;
到此这篇关于PostgreSQL游标与索引选择实例详细介绍的文章就介绍到这了,更多相关PostgreSQL游标与索引选择内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 PostgreSQL游标与索引选择实例详细介绍
相关文章
- java p2p实例_java文件p2p传输[通俗易懂]
- 教你Postgresql如何限制用户登录错误次数的实例
- Debian中PostgreSQL数据库安装配置实例
- Postgresql复制技术:一次解决永久问题(postgresql复制)
- AWS省钱攻略:预留实例怎么玩
- PostgreSQL:实现数据库的继承性(postgresql继承)
- 安装PostgreSQL 极速上手(postgresql安装)
- Linux UDP 实例:快速掌握基础知识与实践技巧(linuxudp实例)
- 快速安全!PostgreSQL下载,数据库管理必备!(postgresql下载)
- 深入浅出:PostgreSQL编程指南(postgresql编程)
- 优化精细化PostgreSQL查询优化实践(postgresql查询)
- 使用C语言和MySQL快速抓取实例的下载方法(c mysql 实例下载)
- jquery获取dom固定元素添加样式的简单实例
- ASP中巧用Split()函数生成SQL查询语句的实例
- 实例讲解Python中的私有属性