Postgres中的Seq扫描和位图堆扫描有什么区别? [英] What is the difference between Seq Scan and Bitmap heap scan in postgres?

问题描述

在explain命令的输出中，我发现了两个术语"Seq扫描"和位图堆扫描".有人可以告诉我这两种扫描类型有什么区别吗? (我正在使用PostgreSql)

In output of explain command I found two terms 'Seq Scan' and 'Bitmap heap Scan'. Can somebody tell me what is the difference between these two types of scan? (I am using PostgreSql)

推荐答案

http://www.postgresql.org/docs/8.2/static/using-explain.html

基本上，顺序扫描将转到实际的行，并从第1行开始读取，并继续直到满足查询为止(例如，在限制的情况下，这可能不是整个表)

Basically, a sequential scan is going to the actual rows, and start reading from row 1, and continue until the query is satisfied (this may not be the entire table, e.g., in the case of limit)

位图堆扫描意味着PostgreSQL找到了要提取的一小部分行(例如从索引中)，并将仅提取那些行.当然，这将寻求更多的解决方案，因此只有在需要一小部分行时才更快.

Bitmap heap scan means that PostgreSQL has found a small subset of rows to fetch (e.g., from an index), and is going to fetch only those rows. This will of course have a lot more seeking, so is faster only when it needs a small subset of the rows.

举个例子:

create table test (a int primary key, b int unique, c int);
insert into test values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

现在，我们可以轻松进行seq扫描:

Now, we can easily get a seq scan:

explain select * from test where a != 4

                       QUERY PLAN                        
---------------------------------------------------------
 Seq Scan on test  (cost=0.00..34.25 rows=1930 width=12)
   Filter: (a <> 4)

它进行了顺序扫描，因为它估计它将抢占绝大多数表；试图做到这一点(而不是大量阅读)是很愚蠢的.

It did a sequential scan because it estimates its going to grab the vast majority of the table; seeking to do that (instead of a big, seekless read) would be silly.

现在，我们可以使用索引了:

Now, we can use the index:

explain select * from test where a = 4 ;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Index Scan using test_pkey on test  (cost=0.00..8.27 rows=1 width=4)
   Index Cond: (a = 4)

最后，我们可以进行一些位图操作:

And finally, we can get some bitmap operations:

explain select * from test where a = 4 or a = 3;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=8.52..13.86 rows=2 width=12)
   Recheck Cond: ((a = 4) OR (a = 3))
   ->  BitmapOr  (cost=8.52..8.52 rows=2 width=0)
         ->  Bitmap Index Scan on test_pkey  (cost=0.00..4.26 rows=1 width=0)
               Index Cond: (a = 4)
         ->  Bitmap Index Scan on test_pkey  (cost=0.00..4.26 rows=1 width=0)
               Index Cond: (a = 3)

我们可以这样解读:

1. 为我们想要的a = 4建立行的位图. (位图索引扫描)
2. 为我们想要的a = 3建立行的位图. (位图索引扫描)
3. 将两个位图或在一起(BitmapOr)
4. 在表中查找这些行(位图堆扫描)，然后检查以确保a = 4或a = 3(重新检查条件)

[是的，这些查询计划很愚蠢，但这是因为我们未能分析test，如果我们对其进行了分析，它们将都是顺序扫描，因为有5条微小的行]

[Yes, these query plans are stupid, but that's because we failed to analyze test Had we analyzed it, they'd all be sequential scans, since there are 5 tiny rows]

这篇关于Postgres中的Seq扫描和位图堆扫描有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！