I'll try to explain my misunderstandings by the following example.
I didn't understand fundamentals of the
Bitmap Heap Scan Node. Consider the query
SELECT customerid, username FROM customers WHERE customerid < 1000 AND username <'user100'; the plan of which is this:
Bitmap Heap Scan on customers (cost=25.76..61.62 rows=10 width=13) (actual time=0.077..0.077 rows=2 loops=1) Recheck Cond: (((username)::text < 'user100'::text) AND (customerid < 1000)) -> BitmapAnd (cost=25.76..25.76 rows=10 width=0) (actual time=0.073..0.073 rows=0 loops=1) -> Bitmap Index Scan on ix_cust_username (cost=0.00..5.75 rows=200 width=0) (actual time=0.006..0.006 rows=2 loops=1) Index Cond: ((username)::text < 'user100'::text) -> Bitmap Index Scan on customers_pkey (cost=0.00..19.75 rows=1000 width=0) (actual time=0.065..0.065 rows=999 loops=1) Index Cond: (customerid < 1000)
My understanding of this node:
As explained there, the
bitmap heap scan reads table blocks in sequential order, so it doesn't produce random-table-access overhead which happens as doing just
Index Scan has been done, PostgreSQL doesn't know how to fetch the rows optimally, to avoid unneccessary
heap blocks reads (or
hits if there is a hot cache). So to figure it out it generates the structure (
Bitmap Index Scan) called
bitmap which in my case is being generated by generating two bitmaps of the indexes and performing
BITWISE AND. Since the bitmap has been generated it now can read the table optimally in a sequential order, avoiding unnecessary
That's the place where a lot of questions come.
QUESTION: We have just a bitmap. How does PostgreSQL knows by just a bitmap anything about rows' physical order? Or generates the bitmap so that any element of it can be mapped to the pointer to a page easily? If so, that explains everything, but it's just my guessing.
So, can we say simply that the
bitmap heap scan -> bitmap index scan is like a sequential scan but only of the appropriate part of the table?