CODEWITHSUNDEEP
postgresqlindexingpostgresql-10
KAction
KAction

Reputation: 625

Reason to perform Bitmap index scan to Index-only scan?

As I understood by reading this blog post, Bitmap index scan may be superior to index scan, since it batches access to pages of the table itself to fetch data that is not present in the index.

Yet, it still needs to traverse index, so I see no reason why it may be better than Index-Only scan when all data requested present in index. Yet it looks like in many cases Postgres prefers bitmap index scan to index-only.

Taking example from another blog post:

#  INSERT INTO sampletable
        SELECT random() * 10000
        FROM generate_series(1, 100000);
# analyze sampletable;
# \d+ sampletable
                               Table "public.sampletable"
 Column |  Type  | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------+-----------+----------+---------+---------+--------------+-------------
 x      | bigint |           |          |         | plain   |              |
Indexes:
    "sampletable_x_idx" btree (x)
# explain SELECT x FROM sampletable WHERE x < 10;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Bitmap Heap Scan on sampletable  (cost=5.01..234.48 rows=93 width=8)
   Recheck Cond: (x < 10)
   ->  Bitmap Index Scan on sampletable_x_idx  (cost=0.00..4.99 rows=93 width=0)
         Index Cond: (x < 10)
(4 rows)

So what I am missing in understanding of trade-offs of Index-Only/Bitmap Index scans?

Upvotes: 1

Views: 754

Answers (1)

jjanes
jjanes

Reputation: 44167

Until the table has been vacuumed, it would be an index-only scan in name only. Each tuple would have to be verified in the heap. The planner knows that, and penalizes the index only scan accordingly. Once the table has been vacuumed and all the pages set to "all visible", then the planner starts preferring the index-only scan.

Upvotes: 3

Related Questions