Postgresql sequential scans, index scans, index only scan,and bitmap index scans扫描方法理解

 

hank=> CREATE TABLE sampletable (x numeric);

CREATE TABLE

hank=> INSERT INTO sampletable

hank->          SELECT random() * 10000

hank->          FROM generate_series(1, 10000000);

INSERT 0 10000000

hank=> CREATE INDEX idx_x ON sampletable(x);

CREATE INDEX

hank=> analyze sampletable ;


扫描数据占比很小的时候直接走index only scan,因这里只有一列x，所以数据都在索引里面

hank=> explain SELECT * FROM sampletable WHERE x = 42353;

                                 QUERY PLAN           

-------------------------------------------------------------------------------

Index Only Scan using idx_x on sampletable  (cost=0.43..8.45 rows=1 width=11)

  Index Cond: (x = '42353'::numeric)

(2 rows)



如果这里有多个列，select的时候取整个行，那么就是index scan,也就是说先通过索引扫描定位位置，然后再去表中取对应的行。



结果集是比较多的行，则走全表扫描

hank=> explain SELECT * FROM sampletable WHERE x < 42353;

                              QUERY PLAN          

------------------------------------------------------------------------

Seq Scan on sampletable  (cost=0.00..179055.78 rows=10000062 width=11)

  Filter: (x < '42353'::numeric)

(2 rows)




使用bitmap index scan就是把符合条件的索引项 全部取出来，然后在内存进行排序，然后根据索引项再取数据，当数据库需要合并索引的子集时，会用到这种扫描方法

bitmap index scan 和bitmap heap scan是成对出现的。

索引扫描的效率高，但是顺序扫描的又太少，那么久会使用到bitmap scan,单个块在扫描期间只扫描一次。如下示例，or或者and也会发生bitmap scan

hank=> explain SELECT * FROM sampletable WHERE x < 423;

                                  QUERY PLAN           

---------------------------------------------------------------------------------

Bitmap Heap Scan on sampletable  (cost=10334.37..69966.68 rows=446185 width=11)

  Recheck Cond: (x < '423'::numeric)

  -> Bitmap Index Scan on idx_x  (cost=0.00..10222.82 rows=446185 width=0)

        Index Cond: (x < '423'::numeric)

(4 rows)

 

以上只有bitmap index scan稍微难理解一些，看完希望自己多想一下就明白了

补一个index scan的例子，希望更好理解

hank=> create table tt1( a bigint ,b bigint);
CREATE TABLE
hank=> insert into tt1 select random()*10000, id from generate_series(1,10000000) id;
INSERT 0 10000000
hank=> create index idx_tt1_b on tt1(b);
CREATE INDEX

hank=> explain select * from tt1 where b=20;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Index Scan using idx_tt1_b on tt1  (cost=0.43..8.45 rows=1 width=16)
   Index Cond: (b = 20)
(2 rows)

hank=> explain select a from tt1 where b=20; 
                             QUERY PLAN                              
---------------------------------------------------------------------
 Index Scan using idx_tt1_b on tt1  (cost=0.43..8.45 rows=1 width=8)
   Index Cond: (b = 20)
(2 rows)

可以看到上面选择整行或者a字段都是index scan,只有select b的时候就是index only scan 
hank=> explain select b from tt1 where b=20; 
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Index Only Scan using idx_tt1_b on tt1  (cost=0.43..8.45 rows=1 width=8)
   Index Cond: (b = 20)
(2 rows)

参考：https://www.cybertec-postgresql.com/en/postgresql-indexing-index-scan-vs-bitmap-scan-vs-sequential-scan-basics/