postgresql - PostgreSQL 什么时候自动为表创建位图索引？

Question

PostgreSQL 什么时候自动为表创建位图索引？

我从PostgreSQL' documentation看到了以下例子，想知道为什么改变 WHERE 中的值会有所不同？谢谢。

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)

和

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)

Answer 1

Postgres 没有“位图索引”。

您看到的是在扫描索引时使用位图的“索引扫描”。

Tom Lane's answer在邮件列表上解释得很好:

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.

另见这个问题 https://dba.stackexchange.com/questions/119386以获得更详细的解释。