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游标与索引选择内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • PostgreSQL长事务与失效的索引查询浅析介绍

    最近刚写了一篇文章介绍了下长事务,以及一些长事务常见的危害,如无法及时的垃圾回收导致表膨胀之类的问题,最近刚好又碰到一个问题也是长事务所导致的. 上周六早上接到同事电话,说某个库CPU一直很高,看了下全是某张大表的全表扫描导致,但是奇怪的是相关的查询都有用到索引列,不知道为啥查询全部都没走索引. 当我连上去查看时发现确实如此,如果只是某个查询不走索引那可能是SQL本身写的有问题,但是这张表相关的所有SQL都不走索引,那自然会想到是索引本身的原因了.那是不是索引失效了呢?经过检查发现这张表上的索引

  • PostgreSQL长事务概念解析

    我们在很多地方应该都听到过长事务的危害,比方说长事务会导致表膨胀之类的.那么在PostgreSQL中什么才算是长事务呢? 首先,在PostgreSQL的官方文档中并没有所谓“长事务”这一定义,似乎大家约定俗称的把一个执行了很长却没有提交的事务认为是“长事务”了,而在不同的数据库中关于长事务的定义往往也不尽相同,那么在PostgreSQL中什么是长事务呢? 打个比方,如下所示,我在一个会话中通过begin开启一个事务,然后执行了个简单的查询语句后迟迟不提交,这算不算长事务呢? bill=# beg

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

    之前有写过一个案例,order by limit因为数据分布不均而选择了错误的索引,这是由于优化器没法判断数据的分布关系,默认认为数据分布是均匀的所导致的. 而除了limit,当我们在使用游标时也要注意有可能会出现类似的情况.而往往这类在存储过程中的SQL我们更难发现其选择了错误的执行计划,所以需要注意. 1.建测试表 bill=# create table tbl (id int, c1 int, c2 int, c3 int, c4 int); CREATE TABLE 2.写入一批随机数据

  • MYSQL索引无效和索引有效的详细介绍

    1.WHERE字句的查询条件里有不等于号(WHERE column!=...),MYSQL将无法使用索引2.类似地,如果WHERE字句的查询条件里使用了函数(如:WHERE DAY(column)=...),MYSQL将无法使用索引3.在JOIN操作中(需要从多个数据表提取数据时),MYSQL只有在主键和外键的数据类型相同时才能使用索引,否则即使建立了 索引也不会使用4.如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP,MYSQL只有在搜索模板的第一个字符不是通配符的情况下才

  • jQuery 选择符详细介绍及整理

    jQuery 选择符 CSS选择符, 如: $('#title1 > li')为取得ID为title1(#title)的子元素(>)中所有的列表项(li). $('#title1 li:not(.class1)')为取得ID为title的后代元素中没有(not)class1类的所有列表项. jQuery库支持XPath选择符. 如: $('a[@title]')为取得所有带title属性的链接.也可以不用@符号. 如: $('div[ol]')为取得所以包含一个ol属性的div元素.当然,还允

  • php 三元运算符实例详细介绍

    三元运算符的功能与"if....else"流程语句一致,它在一行中书写,代码精练.执行效率高.在PHP程序中恰当地使用三元运算符能够让脚本更为简洁.高效.代码的语法如下: (expr1)?(expr2):(expr3); //表达式1?表达式2:表达式3 解释:如果条件"expr1"成立,则执行语句"expr2",否则执行"expr3". <?PHP $a=10; $b=20; $c=$a>$b?($a-$b):(

  • C++多继承同名隐藏实例详细介绍

    如果某个派生类的部分或者全部直接基类是从另一个共同的基类派生而来,在这些俄直接基类中, 从上一级基类继承来的成员就拥有相同的名称,因此派生类中就会出现同名现象.对这种类型的同名成员也要使用作用域分辨符来唯一标识,而且必须使用直接基类来进行限定. -------------------------------------------------- /* * File: main.cpp * Author: yubao * * Created on May 31, 2009, 8:54 AM */

  • MySql索引详细介绍及正确使用方法

    MySql索引详细介绍及正确使用方法 1.前言: 索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点. 索引是存储引擎用于快速查找记录的一种数据结构,通过合理的使用数据库索引可以大大提高系统的访问性能,接下来主要介绍在MySql数据库中索引类型,以及如何创建出更加合理且高效的索引技巧. 注:这里主要针对的是InnoDB存储引擎的B+Tree索引数据结构 2.索引的优点 1.大大减轻了服务器需要扫描的数据量,从而提高了数据的检索速度 2.帮助服务器避免排序和临时表 3.可以将

  • MySQL索引优化之分页探索详细介绍

    目录 ​​MySQL​​索引优化之分页探索 案例一 案例二 ​​MySQL​​索引优化之分页探索 表结构 CREATE TABLE `demo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0'

  • mysql数据库之索引详细介绍

    目录 思维导图 简单理解 索引模型的演变 二叉查找树 自平衡二叉树 B树 B+树 聚集索引与二级索引 总结 如果你想深入了解为什么mysql可以快速的进行检索数据,那么你一定要来了解一下mysql的索引原理 思维导图 简单理解 你可以把索引理解为一本书的目录,我们可以通过索引快速的找到我们需要的数据,大概就像下面这个图,索引就像是右边的二叉树,每个节点指向具体的数据的物理地址,先通过二叉树找到数据的位置,然后再去物理磁盘中获取数据. 但是不同的二叉树的特性不同,我们还要选择合适的树来作为索引,所

  • vue.js实例对象+组件树的详细介绍

    vue的实例对象 首先用js的new关键字实例化一个vue el: vue组件或对象装载在页面的位置,可通过id或class或标签名 template: 装载的内容.HTML代码/包含指令或者其他组件的HTML片段,template将是我们使用的模板 **data:** 数据通过data引入到组件中 在组件中的data要以函数的形式返回数据,当不同的界面用了同一个组件时,才不会以为一个组件的值发生改变而改变其他页面的内容. {{ }} 双括号语法里面放入数据的变量 组件注册语法糖 全局组件 A方

  • 服务器安装什么系统好 服务器系统详细介绍与选择推荐

    下面是小编收藏的服务器系统详细介绍及如何选择的教程,整理自阿里云论坛.非常不错的教程,小编也正在学习中. 当前阿里云总共提供了两大类17种操作系统选择.站长一看到这么多操作系统一般第一反应就是不知所措,那么应该如何选择适合自己的服务器呢?这篇文章来为大家解答. Windows篇 阿里云提供了6种window系统,涵盖了Server 2003 sp2以及Server 2008 R2这两大类操作系统.其中又分为了32位和64位 (1)如何选择32位还是64位 32位系统相比64位系统,最主要的限制体

随机推荐