因接触partition by而对PostgreSQL explain有了一个小小的认识

hresh 692 0

因接触partition by而对PostgreSQL explain有了一个小小的认识

偶然情况下看到了别人写的关于 partition by 使用的 SQL，以及提到的索引使用，因为对这方面的知识有所欠缺，所以决定花点时间学习一下，于是就有了下文。

环境搭建及筹备样本数据

本文使用 Postgre SQL 数据库，版本号为 12.8。

创建数据表

关于数据库连接有如下三种方式：

1、使用 psql 连接到 PostgreSQL 数据库。psql 是 PostgreSQL 提供的交互式终端程序。通过 psql 工具，可以执行大量操作，比如：执行 SQL 语句、管理数据库对象等等。查看版本号（select version();)

2、使用 pgAdmin 图形界面连接 PostgreSQL 数据库

连接到数据库的第二种方法是使用pgAdmin GUI应用程序。 通过使用pgAdmin GUI应用程序，您可以通过直观的用户界面与PostgreSQL数据库服务器进行交互。

3、通过其他管理软件连接到 PostgreSQL 数据库，比如说 Navicat，或者 Dbeaver。

连接上默认数据库 postgres 后，接下来创建 course 表并插入样本数据。

CREATE TABLE public.course (
    id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    language_id int8 NOT NULL,
    "name" varchar(100) NOT NULL,
    "level" int4 NULL,
    created_date timestamp NULL,
    "version" varchar(50) NULL,
    total_lessons int4 NULL,
    country varchar(100) NOT NULL,
    CONSTRAINT course_pkey PRIMARY KEY (id)
);

样本数据可以在 csv 文件中随便造一些数据，然后导入到表中即可。

接下来我们分别使用 group by 和 partition by 按指定列对结果进行分组，并使用Avg()、Min()、Max() 等聚合函数计算所需值。

group by与partition by比较

目前需求，在 course 表中找到如下值（无实际意义）：

  • 一个国家的最大语言id；

  • 一个国家的最小语言id；

  • 一个国家的平均语言id；

group by分组

group by 执行语法如下：

SELECT expression, aggregate function ()
FROM tables
WHERE conditions
GROUP BY expression

接下来编写我们的 SQL 语句：

select
    country ,
    min(language_id) as minLanguageId,
    avg(language_id) as avgLanguageId,
    max(language_id) as maxLanguageId
from
    course
group by
    country ;

执行结果如下图所示，分组后只有10条数据，对应 10 个国家。查询时默认是无序的。

数据库表数据

如果此时我们想在查询结果中增加 languageId 列字段展示，如下所示：

select
    country ,
    language_id ,
    min(language_id) as minLanguageId,
    avg(language_id) as avgLanguageId,
    max(language_id) as maxLanguageId
from
    course
group by
    country ;

执行此查询后，我们会收到一条错误消息。在 SQL GROUP BY 子句中，如果在 Group by 子句中也使用了列，我们可以在 select 语句中使用该列。它不允许 select 子句中的任何列不是 GROUP BY 子句的一部分（抛开聚合函数，比如 count、sum、min 等等）。

ERROR: column "course.language_id" must appear in the GROUP BY clause or be used in an aggregate function

此时我们可以考虑使用 partition by 来解决这个问题。

除此之外，我们使用使用 order by 时，如果列字段不存在于 group by 子语句中，也会提示上述报错。

select
    country ,
    min(language_id) as minLanguageId,
    avg(language_id) as avgLanguageId,
    max(language_id) as maxLanguageId
from
    course
group by
    country
order by level asc;

正确写法如下所示，按照 country 和 level 进行分组，执行结果有 41 条数据。

select
    country ,
    level,
    min(language_id) as minLanguageId,
    avg(language_id) as avgLanguageId,
    max(language_id) as maxLanguageId
from
    course
group by
    country,level
order by level asc;

partition by分区

执行语句为：

select
    country ,
    language_id ,
    min(language_id) over(partition by country) as minLanguageId,
    avg(language_id) over(partition by country) as avgLanguageId,
    max(language_id) over(partition by country) as maxLanguageId
from
    course;

执行结果如下图所示，查询结果默认是升序的。

因接触partition by而对PostgreSQL explain有了一个小小的认识

增加不属于 partition by 字句的列，也不会报错。

这里也试一试 order by 语法，不过存在两种写法，含义也不同。

写法一：

select
    country ,
    language_id ,
    level ,
    min(language_id) over(partition by country order by level asc) as minLanguageId,
    avg(language_id) over(partition by country order by level asc) as avgLanguageId,
    max(language_id) over(partition by country order by level asc) as maxLanguageId
from
    course;

执行结果为：

因接触partition by而对PostgreSQL explain有了一个小小的认识

OVER(PARTITION BY... ORDER BY...) 的含义如下：先按照 country 和 level 字段进行分区，然后再按照 level 字段进行排序。

写法二：

select
    country ,
    language_id ,
    level ,
    min(language_id) over(partition by country) as minLanguageId,
    avg(language_id) over(partition by country) as avgLanguageId,
    max(language_id) over(partition by country) as maxLanguageId
from
    course
order by country,level asc;

执行结果为：

因接触partition by而对PostgreSQL explain有了一个小小的认识

此种写法还只是针对 country 进行分区，不过最后针对 country 和 level 进行排序，从结果就可以看出于写法一不同。

两者区别

partition by 与 group by 的区别有如下几点：

1、group by 分组后有多少条数据，就返回多少条数据记录；而 partition by 可以获取表中所有的记录。

2、group by 会按照分组只返回一行记录；而 partition by 则会给同一分区下的每条记录提供聚合列，且值相同。

3、select 后跟的内容范围不同，有 group by 的查询语句只能返回 group by 子语句中的列字段；而 partition by 可以返回任意字段。

4、group by 严格按照 group by 语句中包含的字段进行分组；而 partition by 除了包括 partition by 语句中的字段，还包括 order by 语句中的字段。

5、group by 查询语句默认是无序的；而 partition by 查询默认是根据分区字段升序的。

partition by用法

该函数使用格式如下所示：

SELECT *, aggregate function over(partition by expression)
FROM tables

over 关键字：

  • over 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 over 关键字来区分这两种用法。

  • over 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。

  • 如果 over 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

aggregate function 有很多种情况，下面我们挨个进行演示。

与 group by 子句不同，partition by 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。

在同一个 select 语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干扰。

row_number()

select
    country ,
    language_id ,
    row_number() over(partition by country) as rn,
    row_number() over() as rownum
from
    course;

执行结果如下所示：

因接触partition by而对PostgreSQL explain有了一个小小的认识

row_number() 是对每个分区的结果进行编号，rownum 是对所有记录进行编号。

rank()

select
    country ,
    language_id ,
    level,
    rank() over(partition by country order by level) as rn,
    row_number() over() as rownum
from
    course;

执行结果为：

因接触partition by而对PostgreSQL explain有了一个小小的认识

同一个 country 下，level 相同则会出现并列的情况，所以 rn 的值会跳跃排序。

dense_rank()

select
    country ,
    language_id ,
    level,
    dense_rank () over(partition by country order by level) as rn,
    row_number() over() as rownum
from
    course;

执行结果如下：

因接触partition by而对PostgreSQL explain有了一个小小的认识

和 rank()不同，dense_rank() 根据 level 相同的记录会顺序排序。

其他函数

count() ，对各分区下指定列进行计数。同一分区下的每条记录计数值一致。

max() ，获取各分区下指定列的最大值。

min() ，获取各分区下指定列的最小值。

sum()，获取各分区下指定列的累加和。

avg()，获取各分区下指定列的平均值。

first_value()，获取各分区下指定列的第一个数据。

last_value() ，获取各分区下指定列的最后一个数据。

lag() ，根据 partition by 语句中的列进行分区，获取上一行的某列的值，第一行值都为 null。

select
    country ,
    language_id ,
    level,
    lag (language_id) over(partition by country order by level) as languageIdSum,
    row_number() over() as rownum
from
    course;

执行结果如下：

因接触partition by而对PostgreSQL explain有了一个小小的认识

需要注意的是虽然上述语句是按照 country 和 level 进行分区，但是对于 lag 函数，它只认 country 字段作为分区依据。我们可以看下述 SQL 语句的执行结果。

select
    country ,
    language_id ,
    level,
    lag (language_id) over(partition by country, level) as languageIdSum,
    row_number() over() as rownum
from
    course;

因接触partition by而对PostgreSQL explain有了一个小小的认识

lag 有三个参数，第一个参数是列名（必填），第二个参数是偏移的 offset，默认为 1，第三个参数是超出记录窗口时的默认值，默认为 null。

示例如下：

select
    country ,
    language_id ,
    level,
    lag (language_id,2,'9999') over(partition by country order by level) as languageIdSum,
    row_number() over() as rownum
from
    course;

执行结果为：

因接触partition by而对PostgreSQL explain有了一个小小的认识

lead() ，和 lag 函数相反，获取后一行的值，最后一行值为 null。

关于 partition by 的用法已经讲述完毕，当被查询的表数据量过大，我们一般都会考虑创建索引，但是并没有好转，那么此时要查看 SQL 语句执行时是否使用到索引，就需要用到 explain 命令。虽然对于 MySQL 的 explain 命令比较熟悉，PostgreSQL 同样执行 explain 命令查看执行计划，返回结果却有很大的差异。所以接下来我们来学习一下 PostgreSQL 中的 explain 命令。

explain命令

在 MySQL 中我们使用 explain 命令来查看执行计划，确认 SQL 语句是否命中索引，从而解决 SQL 执行过慢的问题。

PostgreSQL 中也支持 explain 命令，也是做相同的事情，只是得到的结果有所不同，相较于 MySQL 的执行计划内容更简单一些。

简单示例

explain select * from product p;
                       QUERY PLAN                        
---------------------------------------------------------
Seq Scan on product p  (cost=0.00..629.27 rows=5527 width=648)
(1 row)

由于此查询没有WHERE子句，它必须扫描所有表的行，所以规划器已经选择使用一个简单的顺序扫描计划。

输出结果分析：

  • Seq Scan on course 分为两部分：即：Seq Scan 表示全表扫描（顺序扫描） ，如果数据量较大的话，那么这种查询方式为最慢的，那就需要考虑优化表结构或者优化查询 SQL 了，还有 product 表示查询的表。

  • cost=0.00..629.27 rows=5527 width=648 ，分别表示预计启动开销，预计总消耗，查询行数和规划节点的行平均宽度(以字节计算)。
    • cost 由 .. 分割成两段即 0.00 和 629.27，第一个数字表示预计启动开销，获取第一行数据需要的开销，如果存在 where 条件，则该值可能不为 0；第二个数字表示返回所有数据的总开销。该值的计算是有迹可循的，待会做介绍。
    • rows 表示返回行数，示例中结果 rows=5527 则表示会返回 5527 行。
    • width 表示每行平均宽度，示例中每行平均宽度为 648 字节。

关于 cost 中 3.51 值的计算，可以先执行下述语句：

postgres=# SELECT relpages, reltuples FROM pg_class WHERE relname = 'product';
 relpages | reltuples 
----------+-----------
       574|      5527.0
(1 row)

根据结果可知 product 表有 574 个磁盘页面和 5527 行，估计成本通过（磁盘页面读取

seq_page_cost）+（行扫描

cpu_tuple_cost）计算。默认情况下， seq_page_cost是1.0，cpu_tuple_cost是0.01, 因此估计成本为(574 * 1.0) + (5527 * 0.01) = 629.27。

语法

ANALYZE

ANALYZE 选项可以查看实际执行 SQL 来获得 SQL 命令的实际执行计划，因为被真正执行过，所以可以看到执行计划每一步耗费了多长时间，以及它实际返回的行数。

explain(ANALYZE) select * from product p where p.id =30243;

Index Scan using product_pkey on product p  (cost=0.28..8.30 rows=1 width=648) (actual time=0.018..0.019 rows=1 loops=1)
  Index Cond: (id = 30243)
Planning Time: 0.060 ms
Execution Time: 0.034 ms

actual time 数值是以真实时间的毫秒计的，而cost估计值则是以任意的单位； 因此它们很可能不一致。实际查询的行数也和 cost 中的有所不同，loops 表示只执行了1次。

Planning time 表示生成查询计划的时间。

Execution time 表示实际的 SQL 执行时间，其中不包括查询计划的生成时间。

BUFFERS

BUFFERS 选项为TRUE 会显示关于缓存的使用信息，默认为 FALSE。该参数只能与ANALYZE 参数一起使用。缓冲区信息包括共享块（常规表或者索引块）、本地块（临时表或者索引块）和临时块（排序或者哈希等涉及到的短期存在的数据块）的命中块数，更新块数，挤出块数。

explain(ANALYZE,BUFFERS) select * from product p where p.id =30243;

Index Scan using product_pkey on product p  (cost=0.28..8.30 rows=1 width=648) (actual time=0.030..0.031 rows=1 loops=1)
  Index Cond: (id = 30243)
  Buffers: shared hit=6
Planning Time: 0.355 ms
Execution Time: 0.073 ms

索引

PostgreSQL 提供了好几种索引类型：B-tree, Hash, GiST, SP-GiST 和 GIN 。 每种索引类型都比较适合某些特定的查询类型，因为它们用了不同的索引结构。缺省时， CREATE INDEX 命令将创建 B-tree 索引，它适合大多数情况。

B-tree 适合处理那些能够按顺序存储的数据之上的等于和范围查询。 特别是在一个建立了索引的字段涉及到使用<、<=、=、>=、> 操作符之一进行比较的时候，PostgreSQL 的查询规划器都会考虑使用 B-tree 索引。等效于这些操作符组合的构造， 比如 BETWEEN和IN，也可以用搜索 B-tree 索引实现。 同样，索引列中的IS NULL或IS NOT NULL条件可以和 B-tree 索引一起使用。

PostgreSQL 提供了五种索引方式：唯一索引、主键索引、多属性索引、部分索引、表达式索引。

  • 唯一索引：用来强制列值的唯一性，或者是多个列组合值的唯一性。当前，只有B-tree能够被声明为唯一。当一个索引被声明为唯一时，索引中不允许多个表行具有相同的索引值。空值被视为不相同。注意：不需要手工在唯一列上创建索引，如果那样做也只是重复了自动创建的索引而已。

  • 主键索引：主键列会被自动创建一个唯一索引来实现主键约束，主键索引是唯一索引的特殊类型。

  • 多属性索引：即联合索引，定义在多个列上。目前，只有 B-tree、GiST、GIN 和 BRIN 索引类型支持多列索引，最多可以指定32个列（该限制可以在源代码文件pg_config_manual.h中修改，但是修改后需要重新编译PostgreSQL）。

  • 部分索引：建立在一个表的子集上的索引，该子集由一个表达式定义（表达式即部分索引的谓词），该索引只包含表中那些满足这个谓词的元组。

    create index stu_name_idx on student(name) where id>1 and id<255;
  • 表达式索引：一个索引列并不一定是底层表的一个列，也可以是从表的一列或多列计算而来的一个函数或者标量表达式。这种特性对于根据计算结果快速获取表中内容是有用的。比如说我们针对某个字段的小写结果建立索引： 
    CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

    将表达式移到=右侧，避免无法命中索引。

在对表进行扫描的时候，查询优化器选择顺序扫描还是索引扫描，用户通常没有办法决定（有些数据库提供了 HINT 功能， PostgreSQL 数据库没有该功能），路径的选择是由数据库查询优化模块来完成的 。

扫描方式

顺序扫描

顺序扫描又叫做全表扫描，是最基本的扫描方式，它的算法复杂度是O(N)，其中 N代表整个表中元组的数量，也就是说一个表中所有的元组都会被读取出来。如果数据量比较大，则该种查询方式最慢，则需要通过构建索引进行优化。

explain(ANALYZE,BUFFERS) select * from product p where p.id =30243;
                       QUERY PLAN                        
---------------------------------------------------------
Seq Scan on product p  (cost=0.00..629.27 rows=1 width=648) (actual time=0.012..0.916 rows=1 loops=1)
    Filter: (id = 30243)
    Rows Removed by Filter: 5526
  Buffers: shared hit=574
Planning Time: 0.055 ms
Execution Time: 0.937 ms
  • Filter: (id = 30243) 表明了 Seq Scan 节点之上的Filter 操作，即全表扫描时对每行记录进行过滤操作，过滤条件为 id = 30243
  • Rows Removed by Filter: 5526 表明了过滤操作过滤了多少行记录。
  • Buffers: shared hit=574 表明了从共享缓存中命中了 574个 BLOCK，属于 Seq Scan 节点的 BUFFERS 信息，只有 EXPLAIN 命令中的 BUFFERS 选项为on 时才会显示。

索引扫描

Index Scan

explain(ANALYZE,BUFFERS) select * from product p where p.id =30243;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
Index Scan using product_pkey on product p  (cost=0.28..8.30 rows=1 width=648) (actual time=0.017..0.018 rows=1 loops=1)
  Index Cond: (id = 30243)
  Buffers: shared hit=3
Planning Time: 0.066 ms
Execution Time: 0.035 ms

其中用 Index Scan 表示索引扫描。Index Cond: (id = 30243) 表明索引扫描的条件。

可以看出，使用了索引之后，对相同表的相同条件的扫描速度变快了。这是因为从全表扫描变为索引扫描，通过 Buffers: shared hit=3 可以看出，需要扫描的BLOCK（或者说元组）少了，所以需要的代价也就小了，速度也就快了。

IndexOnly Scan

IndexOnly Scan 是覆盖索引扫描，所需的返回结果能被所扫描的索引全部覆盖，不需要回表查询。

explain(ANALYZE,BUFFERS) select p.id from product p where p.id =30243;

Index Only Scan using product_pkey on product p  (cost=0.28..8.30 rows=1 width=8) (actual time=0.033..0.034 rows=1 loops=1)
  Index Cond: (id = 30243)
  Heap Fetches: 1
  Buffers: shared hit=4
Planning Time: 0.055 ms
Execution Time: 0.048 ms

注意：并不是有索引就会走索引扫描，如果 where 条件中的列有索引，但因为规划器可能觉得整表扫描需要的开销更小一些，所以还是走全表扫描。如下例所示：

explain(ANALYZE,BUFFERS) select * from course c where c.id =6039;

Seq Scan on course c  (cost=0.00..6.88 rows=1 width=68) (actual time=0.007..0.023 rows=1 loops=1)
  Filter: (id = 6039)
  Rows Removed by Filter: 150
  Buffers: shared hit=5
Planning Time: 0.130 ms
Execution Time: 0.038 ms

位图扫描

位图扫描分为：BitmapIndex Scan 位图索引扫描与 BitmapHeap Scan 位图堆扫描。

BitmapIndex Scan 与 Index Scan 很相似，都是基于索引的扫描，但是 BitmapIndex Scan 节点每次执行返回的是一个位图而不是一个元组，其中位图中每位代表了一个扫描到的数据块。而 BitmapHeap Scan一般会作为BitmapIndex Scan 的父节点，将 BitmapIndex Scan 返回的位图转换为对应的元组。这样做最大的好处就是把Index Scan 的随机读转换成了按照数据块的物理顺序读取，在数据量比较大的时候，这会大大提升扫描的性能。

至于上述内容说 BitmapHeap Scan一般会作为BitmapIndex Scan 的父节点，这是为什么呢？

位图扫描路径按照深度优先算法遍历的，如下图所示。BitmapIndex Scan 把满足条件的行或块在内存中建一个位图，扫描完索引后，BitmapHeap Scan 再根据位图到表的数据文件中把相应的数据读出来。如果走了两个索引，可以把两个索引形成的位图通过 AND 或 OR 计算合并成一个，再到表的数据文件中把数据读出来。

其中 BitmapAnd（两个索引范围搜索条件用 and 连接）和 BitmapOr （两个索引范围搜索条件用 or 连接）操作可以将原来 “扁平的 ”“次扫描”的索引扫描路径扩展成“树状的”“多次扫描”的位图扫描路径，这棵树的叶子节点一定是 lndexPath，只不过这个 lndexPath 负责生成的是位图，而不再去进行堆表扫描，堆表扫描的工作留给 BitmapHeapPath 来做。

位图扫描

在 PostgreSQL 中，计划执行使用自顶向下的方法。它首先会执行 Bitmap Heap Scan 节点，但是这个节点还没有任何数据，所以它会询问它的子节点（在这种情况下是 Bitmap Index Scan）。一旦位图索引扫描返回位图，位图堆扫描将继续处理。

先来看个简单的例子：

explain(ANALYZE,BUFFERS) select * from product p where p.id <3243;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
Bitmap Heap Scan on product p  (cost=22.79..534.94 rows=324 width=648) (actual time=0.040..0.293 rows=324 loops=1)
  Recheck Cond: (id < 3243)
  Heap Blocks: exact=99
  Buffers: shared hit=105
  ->  Bitmap Index Scan on product_pkey  (cost=0.00..22.71 rows=324 width=0) (actual time=0.023..0.023 rows=324 loops=1)
        Index Cond: (id < 3243)
        Buffers: shared hit=6
Planning Time: 0.340 ms
Execution Time: 0.354 ms

正如你所看到的计划的一部分，第一个节点是位图堆扫描，它的子节点是位图索引扫描。采取以下步骤来执行此操作：

  1. 位图堆扫描从位图索引扫描请求一个元组。
  2. 位图索引扫描根据条件 (id < 3243) 扫描索引，几乎与正常索引扫描中的扫描方式相同。但是它没有返回对应于堆数据的 TID（由页号和其中的偏移量组成），而是在位图中添加这些 TID。关于 PostgreSQL 数据存储结构下次专门写一篇文章进行讲解。
  3. 然后 Bitmap Heap Scan 读取位图，得到存储的页码和偏移量对应的堆数据。然后它检查可见性、资格等，并根据所有这些检查的结果返回元组。

另外通过 Bitmap Heap Scan 和 BitmapIndex Scan 的 actual time 也可以看出，位图堆扫描的代价是包含位图索引扫描的。

接下来让我们看几个有趣案例。

让我们在上述语句中再增加一个 where 条件。

explain select * from product p where p.id <3243 and "type" ='FREE';
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Bitmap Heap Scan on product p  (cost=22.71..535.67 rows=5 width=648)
   Recheck Cond: (id < 3243)
   Filter: ((type)::text = 'FREE'::text)
   ->  Bitmap Index Scan on product_pkey  (cost=0.00..22.71 rows=324 width=0)
         Index Cond: (id < 3243)
(5 rows)

新增的条件"type" ='FREE'减少了预计的输出行，但是没有减少开销， 因为我们仍然需要访问相同的行。 请注意，type子句不能当做一个索引条件使用，因为这个索引只建立在id列上。 它被当做一个从索引中检索出的行的过滤器来使用。 因此开销实际上略微增加了一些以反映这个额外的检查。

如果我们增加 limit 条件。

explain select * from product p where p.id <3243 limit 100;
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Limit  (cost=0.28..168.20 rows=100 width=648)
   ->  Index Scan using product_pkey on product p  (cost=0.28..544.33 rows=324 width=648)
         Index Cond: (id < 3243)
(3 rows)

虽然上述语句走的是索引扫描，但是如果修改 limit 的值，比如说改成 300，则仍然会走位图扫描。至于 limit 后值在什么区间时会走不同的扫描方式，这点需要测试，还和环境配置有所关联。

条件过滤

不管是全表扫描还是索引扫描，都可能会使用到条件过滤，即增加 where 条件。

explain select * from product p where  "type" ='FREE';
                          QUERY PLAN                          
--------------------------------------------------------------
 Seq Scan on product p  (cost=0.00..643.09 rows=89 width=648)
   Filter: ((type)::text = 'FREE'::text)
(2 rows)

如果 where 条件的列有索引，那么既有可能走全表扫描加条件过滤，也有可能走位图扫描，还有可能走索引扫描。

 explain select * from product p where p.id <32243 ;
                           QUERY PLAN                           
----------------------------------------------------------------
 Seq Scan on product p  (cost=0.00..643.09 rows=4487 width=648)
   Filter: (id < 32243)
(2 rows)

explain select * from product p where p.id <3243;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Bitmap Heap Scan on product p  (cost=22.79..534.94 rows=324 width=648)
   Recheck Cond: (id < 3243)
   ->  Bitmap Index Scan on product_pkey  (cost=0.00..22.71 rows=324 width=0)
         Index Cond: (id < 3243)
(4 rows)

explain select * from product p where p.id <323;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Index Scan using product_pkey on product p  (cost=0.28..6.27 rows=1 width=648)
   Index Cond: (id < 323)
(2 rows)

总结

  • 大多数情况下，Index Scan 要比 Seq Scan 快。但是如果获取的结果集占所有数据的比重很大时，这时 Index Scan 因为要先扫描索引再读表数据反而不如直接全表扫描来的快。
  • 如果获取的结果集的占比比较小，但是元组数很多时，可能 Bitmap Index Scan 的性能要比Index Scan 好。
  • 如果获取的结果集能够被索引覆盖，则 Index Only Scan 因为不用去读数据，只扫描索引，性能一般最好。但是如果VM 文件未生成，可能性能就会比Index Scan 要差。

扩展

为什么在获取 PostgreSQL 中较大比例的表时，位图扫描比索引扫描快？

普通索引扫描一次从索引中获取一个元组指针，并立即访问表中的该元组。位图扫描一次性从索引中获取所有元组指针，使用内存中的“位图”数据结构对它们进行排序，然后以物理元组位置顺序访问表元组。位图扫描提高了对表的引用的局部性，代价是管理“位图”数据结构的更多开销——并且代价是不再按索引顺序检索数据。如果你要根据索引对应的列字段进行排序，则规划器会选择普通索引扫描。

通俗点讲：PostgreSQL 数据存储在 page 中，每条行记录我们又称之为元组，那么索引是怎么定位到行记录的呢？索引的数据包含两部分（key=xxx，TID=(block=xxx,offset=xxx)），key表示真实数据，TID 代表指向数据行的指针，具体 block 代表页面号，offset 代表行偏移量，指向数据页面的 line pointer，即指向真实元组的位置。

例如对于第一个索引数据 I1，堆数据将指向 {blkno-5, offset = 20}，对于 I2 指向 {blkno-1 , offset = 30}, 对于 I3 指向 {blkno-8, offset=40} 等等。如下图所示，如果是根据普通索引进行扫描，我们知道索引是有序的，但是每个索引对应的 block no 并不是连续有序的。

位图扫描比索引扫描快

而位图索引扫描得到的位图包含了 TID 数据，虽然位图需要额外空间来存储 TID，但是存储的 TID 是有序的，即 {blkno-1 , offset = 30}，{blkno-5, offset = 20}，{blkno-8, offset=40}等等。block 列表被排序后，因此顺序扫描将仅使用顺序 I/O。而我们知道顺序 I/O 是优于随机 I/O 的。

比如上述位图扫描的执行语句，我们稍微修改一下，看是否还走位图扫描。

explain(ANALYZE,BUFFERS) select * from product p where p.id <3243 order by p.id;

Index Scan using product_pkey on product p  (cost=0.28..544.33 rows=324 width=648) (actual time=0.008..0.300 rows=324 loops=1)
  Index Cond: (id < 3243)
  Buffers: shared hit=325
Planning Time: 0.089 ms
Execution Time: 0.331 ms

可以看出，如果要根据 id 进行排序，则最好是利用索引的有序性，所以这里使用索引扫描，代价更小一些。

参考文献

PostgreSQL12索引

PostgreSQL书籍链接

位图扫描在 PostgreSQL

为什么在获取 PostgreSQL 中较大比例的表时，位图扫描比索引扫描快？

本文作者为hresh，转载请注明。

发表评论 取消回复
表情 图片 链接 代码

分享