PostgreSQL中Undestanding位图索引 [英] Undestanding bitmap indexes in postgresql

问题描述

PostgreSQL的9.4

PostgreSQL 9.4

我只是面临着被称为节点位图索引扫描和所谓的底层位图数据结构的metnioned概念的在这个职位。至于我，的PostgreSQL 不支持创建位图索引。

I just faced with the node called Bitmap Index Scan and the concept of so-called underlying bitmap data structure metnioned in this post. As far as I got, PostgreSQL doesn't support creating bitmap indexes.

Querstion：的因此，我们需要使用位图数据结构，以执行任何时间位图索引扫描我们首先需要构建它或PostgreSQL建设 B树指数的过程中创建并重建它任何时候表的改变？

Querstion: So any time we need to use bitmap data-structure in order to perform Bitmap Index Scan we need to build it first or PostgreSQL creates it during construction of a btree index and rebuild it any time the table's changed?

推荐答案

动态为每个查询创建页面的位图。它没有被缓存或再使用，并且在该位图索引扫描结束被丢弃。

The bitmap of pages is created dynamically for each query. It is not cached or re-used, and is discarded at the end of the bitmap index scan.

这是没有意义的，提前创建页面的位图，因为的它的内容取决于查询predicates 的

It doesn't make sense to create the page bitmap in advance because its contents depend on the query predicates.

假设你正在寻找 X = 1和y = 2 。你对 B树索引X 和是。 PostgreSQL并不将 X 和是为位图，然后搜索该位图。它扫描指数 X 与所有页面的网页地址x = 1 ，使一个位图，其中的页面可能包含 X = 1 是真实的。然后，它会扫描是找的网页地址，其中是可能等于 2 ，使得从一个位图。然后，它ANDS他们找到网页，既 X = 1 和 Y = 2 可能是真实的。最后，它扫描表自身，只读取可能包含候选值的页面，阅读每一页，并只保留行，其中 X = 1和y = 2 。

Say you're searching for x=1 and y=2. You have b-tree indexes on x and y. PostgreSQL doesn't combine x and y into a bitmap then search the bitmap. It scans index x for the page address of all pages with x=1 and makes a bitmap where the pages that might contain x=1 are true. Then it scans y looking for the page addresses where y might equal 2, making a bitmap from that. Then it ANDs them to find pages where both x=1 and y=2 might be true. Finally, it scans the table its self, reading only the pages that might contain candidate values, reading each page and keeping only the rows where x=1 and y=2.

现在，如果你正在寻找的东西像一个缓存，pre-内置位图索引，但在PostgreSQL 9.5这样一件事：的BRIN指标的。这些旨在用于非常大的表，并提供一种方法来找到，因为它们已知不包含所期望的值，可以跳过表中的范围。

Now, if you're looking for something like a cached, pre-built bitmap index, there is such a thing in PostgreSQL 9.5: BRIN indexes. These are intended for very large tables, and provide a way to find ranges of the table that can be skipped over because they're known not to contain a desired value.

这篇关于PostgreSQL中Undestanding位图索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！