1

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?

Ken White
  • 123,280
  • 14
  • 225
  • 444
KAction
  • 587
  • 2
  • 10

1 Answers1

3

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.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • `=# vacuum full analyze verbose sampletable ; INFO: vacuuming "public.sampletable" INFO: "sampletable": found 0 removable, 100000 nonremovable row versions in 443 pages DETAIL: 0 dead row versions cannot be removed yet. CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.03 s. INFO: analyzing "public.sampletable" INFO: "sampletable": scanned 443 of 443 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows VACUUM` and yet it still prefers bitmap index scan. – KAction May 19 '21 at 11:50
  • VACUUM FULL doesn't set the visibility map, only regular VACUUM does that. – jjanes May 19 '21 at 13:40