postgresql postgres中的Seq Scan和Bitmap堆扫描有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/410586/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the difference between Seq Scan and Bitmap heap scan in postgres?
提问by Sunil
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)
在解释命令的输出中，我发现了两个术语“Seq Scan”和“Bitmap heap Scan”。有人能告诉我这两种扫描有什么区别吗？（我正在使用 PostgreSql）
回答by derobert
http://www.postgresql.org/docs/8.2/static/using-explain.html
http://www.postgresql.org/docs/8.2/static/using-explain.html
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)
基本上，顺序扫描将转到实际行，并从第 1 行开始读取，并继续直到满足查询（这可能不是整个表，例如，在限制的情况下）
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.
位图堆扫描意味着 PostgreSQL 找到了一小部分要获取的行（例如，从索引中），并且将只获取那些行。这当然会有更多的搜索，所以只有当它需要一小部分行时才会更快。
Take an example:
举个例子：
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);
Now, we can easily get a seq scan:
现在，我们可以轻松获得 seq 扫描：
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)
We can read this as:
我们可以这样读：
- Build a bitmap of the rows we want for a=4. (Bitmap index scan)
- Build a bitmap of the rows we want for a=3. (Bitmap index scan)
- Or the two bitmaps together (BitmapOr)
- Look those rows up in the table (Bitmap Heap Scan) and check to make sure a=4 or a=3 (recheck cond)
- 为 a=4 构建我们想要的行的位图。（位图索引扫描）
- 为 a=3 构建我们想要的行的位图。（位图索引扫描）
- 或者将两个位图放在一起（BitmapOr）
- 在表中查找这些行（位图堆扫描）并检查以确保 a=4 或 a=3（重新检查 cond）
[Yes, these query plans are stupid, but that's because we failed to analyze testHad we analyzed it, they'd all be sequential scans, since there are 5 tiny rows]
[是的，这些查询计划很愚蠢，但那是因为我们没有分析test如果我们分析过，它们都是顺序扫描，因为有 5 个小行]
