PostgreSQL中的不同扫描方式

PostgreSQL支持以下的扫描方式：

·顺序扫描

·索引扫描

·仅索引扫描

·位图扫描

·tid扫描

每一种扫描方式都是有用的，取决于查询和参数配置，比如：表的cardinality、表的选择性、磁盘io代价、随机io代价、顺序io代价等等。

 

创建示例表

postgres=# create table abce(id int, num numeric);
CREATE TABLE
postgres=# create index idx_abce on abce(num);
CREATE INDEX
postgres=# insert into abce select generate_series(1,1000000),random()*1000;
INSERT 0 1000000
postgres=# analyze;
ANALYZE
postgres=# 

 

1.顺序扫描

顾名思义，顺序扫描是通过顺序扫描对应表的所有页面的所有项指针来完成的。 因此，如果一个特定表有100个页，然后每个页中有1000条记录，作为顺序扫描的一部分，它将获取100*1000条记录，并检查它是否符合每个隔离级别和谓词子句。因此，即使整个表扫描只选择了一条记录，它也必须扫描100K的记录来根据条件找到合格的记录。

根据上面的表和数据，下面的查询将导致一个顺序扫描，因为大多数数据都被选中了。

postgres=# explain select * from abce where num < 20000;
                          QUERY PLAN                           
---------------------------------------------------------------
 Seq Scan on abce  (cost=0.00..17990.00 rows=1000000 width=15)
   Filter: (num < '20000'::numeric)
(2 rows)

postgres=# 

顺序扫描一般满足以下条件：

(1)谓词的列上没有索引

(2)查询返回大多数的行

 

2.索引扫描

与顺序扫描不同，索引扫描并不是顺序地获取所有记录。相反，它使用与查询中涉及的索引相对应的不同数据结构(取决于索引的类型)，并通过非常小的扫描定位所需的数据(根据谓词)子句。然后，使用索引扫描找到的条目直接指向表中的数据，然后根据隔离级别提取该条目来检查可见性。

索引扫描有两个步骤:

·从索引相关的数据结构中获取数据。它返回表中相应数据的TID。 ·然后直接访问相应的表页以获得整个数据。需要这一额外步骤的原因如下: --查询可能请求获取比相应索引列更多列。 --可见性信息不与索引数据一起维护。因此，为了按照隔离级别检查数据的可见性，它需要访问表数据。

我们可能想知道为什么不总是使用索引扫描，如果它是如此有效。 每件事都有成本。这里所涉及的成本与我们所做的I/O类型有关。在索引扫描的情况下，索引存储中发现的每个记录涉及到随机I/O，它必须从表中取回相应的数据，而在顺序扫描的情况下，顺序I/O涉及到大约只占用随机I/O时间的25%。

因此，只有在总体增益超过随机I/O开销时，才应该选择索引扫描。

根据上面的表和数据，下面的查询将导致索引扫描，因为只有一条记录被选中。因此，随机I/O较少，并且搜索相应的记录是很快的。

postgres=# explain select * from abce where num = 20000;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Index Scan using idx_abce on abce  (cost=0.42..8.44 rows=1 width=15)
   Index Cond: (num = '20000'::numeric)
(2 rows)

postgres=# 

 

3.仅索引扫描

Index Only Scan与Index Scan类似，除了第二步，即，顾名思义，它只扫描索引数据结构。

选择仅索引扫描有两个额外的先决条件:

·查询应该只获取索引中的部分的列。 ·选中表页上的所有元组(记录)都应该是可见的。正如前面所讨论的，索引数据结构并不维护可见性信息，因此为了只从索引中选择数据，我们应该避免检查可见性，如果该页面的所有数据都被认为是可见的，就会发生这种情况。

postgres=# explain select num from abce where num = 20000;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Index Only Scan using idx_abce on abce  (cost=0.42..8.44 rows=1 width=11)
   Index Cond: (num = '20000'::numeric)
(2 rows)

postgres=# 

 

4.位图扫描

如果你只选择了少量的行，PostgreSQL将决定进行索引扫描——如果你选择了大部分行，PostgreSQL将决定完全读取表。 但是，如果对索引扫描来说读取的数据太多而对顺序扫描来说读取的数据太少，该怎么办?解决这个问题的方法是使用位图扫描。

位图扫描的原理是在扫描过程中单个块只使用一次。

位图扫描是索引扫描和顺序扫描的混合。它试图解决索引扫描的缺点，但仍然保持其优点。

如上所述，对于在索引数据结构中找到的每个元组，它需要在表中找到相应的数据。因此，它需要一次获取索引页，然后再获取表页，这将导致大量的随机I/O。

位图扫描方法利用了索引扫描的优点，而不需要随机I/O。具体实现过程分为以下两个层次:

(1)位图索引扫描:

首先从索引数据结构中获取所有索引数据，并创建所有TID的位图。为了简单理解，可以认为此位图包含所有页的哈希值(基于page no哈希)，每个页面条目包含该页面中所有偏移量的数组。

(2)位图堆扫描:

顾名思义，它读取页的位图，然后扫描与存储页和偏移量对应的堆中的数据。最后，它检查可见性和谓词等，并根据所有这些检查的结果返回元组。

 

下面的查询将导致位图扫描，因为它不是选择非常少的索引记录(即太多的索引扫描)，同时没有选择大量的记录(即太少的顺序扫描)。

postgres=# explain select * from abce where num < 200;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Bitmap Heap Scan on abce  (cost=5468.94..13430.74 rows=197744 width=15)
   Recheck Cond: (num < '200'::numeric)
   ->  Bitmap Index Scan on idx_abce  (cost=0.00..5419.51 rows=197744 width=0)
         Index Cond: (num < '200'::numeric)
(4 rows)

postgres=# 

现在考虑下面的查询，它选择相同数量的记录，但只选择关键字段(即仅索引列)。因为它只选择键，所以不需要为数据的其他部分引用表页，因此不涉及随机I/O。所以这个查询将选择仅索引扫描而不是位图扫描。

postgres=# explain select num from abce where num < 200;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Bitmap Heap Scan on abce  (cost=5468.94..13430.74 rows=197744 width=11)
   Recheck Cond: (num < '200'::numeric)
   ->  Bitmap Index Scan on idx_abce  (cost=0.00..5419.51 rows=197744 width=0)
         Index Cond: (num < '200'::numeric)
(4 rows)

postgres=# 

　　

如果你想使用多个索引来扫描单个表，那么它也非常有用。

 

5.TID扫描

如上所述，TID是6字节的数字，由4字节的页码和剩余的2字节的页面内元组索引组成。 在PostgreSQL中，TID扫描是一种非常特殊的扫描，只有在查询谓词中有TID时才会被选中。

下面演示TID扫描的查询:

postgres=# select ctid from abce where id=20000;
   ctid    
-----------
 (109,143)
(1 row)

postgres=# explain select * from abce where ctid='(109,143)';
                     QUERY PLAN                      
-----------------------------------------------------
 Tid Scan on abce  (cost=0.00..4.01 rows=1 width=15)
   TID Cond: (ctid = '(109,143)'::tid)
(2 rows)

postgres=#