什么是“位图堆扫描"?在查询计划中? [英] What is a "Bitmap heap scan" in a query plan?

问题描述

我想知道Bitmap heap scan"的原理，我知道这种情况经常发生当我在条件中使用 OR 执行查询时.

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"?

推荐答案

最好的解释来自 来自 Tom Lane，他是算法的作者，除非我弄错了.另请参阅维基百科文章.

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

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

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).

请注意，顺便说一句，聚类/行顺序如何影响任一方法的相关成本.如果行以随机顺序到处都是，位图索引会更便宜.(而且，事实上，如果他们真的全部到处都是，那么 seq 扫描将是最便宜的，因为位图索引扫描并非没有一些开销.)

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.)

这篇关于什么是“位图堆扫描"?在查询计划中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！