PostgreSQL游标与索引选择实例详细介绍

随心笔谈2年前发布编辑

174 0

🌐 经济型：买域名、轻量云服务器、用途：游戏网站等《腾讯云》特点：特价机便宜适合初学者用点我优惠购买

🚀 拓展型：买域名、轻量云服务器、用途：游戏网站等《阿里云》特点：中档服务器便宜域名备案事多点我优惠购买

🛡️ 稳定型：买域名、轻量云服务器、用途：游戏网站等《西部数码》特点：比上两家略贵但是稳定性超好事也少点我优惠购买

之前有写过一个案例，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进行自动优化，所以对于一些数据分布不均的情况，可能就会

导致选择了错误的执行计划。

if (cursorOptions & CURSOR_OPT_FAST_PLAN)
{

tuple_fraction=cursor_tuple_fraction;

if (tuple_fraction >=1.0)
tuple_fraction=0.0;
else if (tuple_fraction <=0.0)
tuple_fraction=1e-10;
}
else
{

tuple_fraction=0.0;
}

到此这篇关于PostgreSQL游标与索引选择实例详细介绍的文章就介绍到这了,更多相关PostgreSQL游标与索引选择内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

您可能感兴趣的文章:PostgreSQL长事务概念解析PostgreSQL长事务与失效的索引查询浅析介绍