Urgent raster indexes in postgresql

Question

PostgreSQL 9.4

I came across a node called Bitmap Index Scan , and the concept of the so-called basic raster data structure is mentioned in this post . As far as I know, PostgreSQL does not support the creation of raster indexes.

Question: So, at any time when we need to use the bitmap image data structure to perform the Bitmap Index Scan , we need to build it first, or does PostgreSQL create it during the btree index build and rebuild it anytime the table changes?

Answer 1

A page bitmap is created dynamically for each request. It is not cached or reused and is discarded at the end of the scan of the bitmap index.

It does not make sense to create a raster image of the page in advance, because its content depends on the predicates of the request.

Say you're looking for x=1 and y=2 . You have b-tree indices on x and y . PostgreSQL does not combine x and y into a bitmap, and then searches for the bitmap. It scans the index x for the page address of all pages with x=1 and makes a bitmap where the pages that may contain x=1 are true. Then it scans y for page addresses, where y can be 2 , creating a bitmap. Then it ANDs to find pages where both x=1 and y=2 can be true. Finally, he scans the table on his own, reading only pages that may contain candidate values, reading each page and saving only rows, where x=1 and y=2 .

Now, if you are looking for something like a cached, pre-built bitmap index, there are such things in PostgreSQL 9.5: BRIN indexes . They are designed for very large tables and allow you to find the ranges of the table that you can skip, because they, as you know, do not contain the desired value.

Answer 2

A bitmap of data pages is created from an index or more indexes on request (for each request). It is used when an index returns more than fewer rows, or when two or more indexes are used in the same relation. The content of the bitmap controls which pages should be processed and which pages should be skipped.

The main requirement of this scanning method is the existing index in the table.