0

When does PostgreSQL automatically create a Bitmap index for a table?

I saw the following examples from PostgreSQL' documentation, and wonder why changing the value in WHERE could make a difference? Thanks.

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..483.00 rows=7001 width=244)
   Filter: (unique1 < 7000)

and

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101
 width=0)
         Index Cond: (unique1 < 100)
Tim
  • 1
  • 141
  • 372
  • 590

1 Answers1

7

Postgres does not have "Bitmap Indexes".

What you see there is an "index scan" that uses a bitmap while scanning the index.

Tom Lane's answer on the mailing list explains it quite well:

A plain Index Scan fetches one tuple-pointer at a time from the index, and immediately visits that tuple in the table. A bitmap scan fetches all the tuple-pointers from the index in one go, sorts them using an in-memory "bitmap" data structure, and then visits the table tuples in physical tuple-location order.

See also this question https://dba.stackexchange.com/questions/119386 for a more detailed explanation.

  • Thanks. (1) If I understand your reply and the links correcty, it is combination of B+-tree index and bitmap? (2) Is it seems to me that bitmap is created for a specific value of the search key, so is only used for equality selection condition? How is bitmap used for range selection condition such as `WHERE unique1 < 7000`? (3) Why does changing `WHERE unique1 < 7000` t `WHERE unique1 < 100` change postgresql from using B+ tree index only to use the combination? – Tim Jun 20 '18 at 12:15
  • Thanks. In bitmap scan, (4) "sorts them using an in-memory "bitmap" data structure", does it mean sort the tuple pointers in terms of the tuples' search key values? (5) is the sorting done once for all queries, or once per query? It seems to me bitmap scan is more time consuming than plain index scan, because of sorting. – Tim Jun 28 '18 at 15:03