什么是“位图堆扫描”？在查询计划中？

本文介绍了什么是“位图堆扫描”？在查询计划中？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想知道位图堆扫描的原理，我知道在条件中使用 OR 执行查询时，这通常会发生。 / p>

谁可以解释位图堆扫描背后的原理？

解决方案

最好的解释是来自汤姆巷，这是算法的作者，除非我误会。另请参阅维基百科文章。

简而言之，它有点像seq扫描。区别在于，位图索引不是访问每个磁盘页面，而是将适用的索引与AND和OR一起扫描，并且只访问它需要的磁盘页面。

这与索引扫描不同，索引按顺序逐行访问 - 这意味着磁盘页面可能会被多次访问。

Re：您评论中的问题......是的，就是这样。

索引扫描将逐行检查，打开磁盘页面一次又一次，根据需要多次（有些人当然留在记忆中，但你明白了。）

位图索引扫描会依次打开一个短的 - 磁盘页面列表，并获取每个磁盘页面中的每个适用行（因此，您在查询计划中看到所谓的重新检查条件）。

请注意，另外，如何群集/行顺序会影响任一方法的相关成本。如果行以随机顺序遍布整个位置，则位图索引将更便宜。 （事实上​​，如果他们真的所有超过这个地方，seq扫描将是最便宜的，因为位图索引扫描并非没有一些开销。）

I want to know the principle of "Bitmap heap scan", I know this often happens when I execute a query with OR in the condition.

Who can explain the principle behind a "Bitmap heap scan"?

解决方案

The best explanation comes from Tom Lane, which is the algorithm's author unless I'm mistaking. See also the wikipedia article.

In short, it's a bit like a seq scan. The difference is that, rather than visiting every disk page, a bitmap index scan ANDs and ORs applicable indexes together, and only visits the disk pages that it needs to.

This is different from an index scan, where the index is visited row by row in order -- meaning a disk page may get visited multiple times.

Re: the question in your comment... Yep, that's exactly it.

An index scan will go through rows one by one, opening disk pages again and again, as many times as necessary (some will of course stay in memory, but you get the point).

A bitmap index scan will sequentially open a short-list of disk pages, and grab every applicable row in each one (hence the so-called recheck cond you see in query plans).

Note, as an aside, how clustering/row order affects the associated costs with either method. If rows are all over the place in a random order, a bitmap index will be cheaper. (And, in fact, if they're really all over the place, a seq scan will be cheapest, since a bitmap index scan is not without some overhead.)