As mentioned in the previous chapter, PostgreSQL is capable of combining indexes together when two of them are relevant for answering a query. The customers table in the Dell Store example has an index on both the customerid and the username. These are both pretty boring bits of data:
The following somewhat contrived query only returns two rows, but it can use both indexes to accelerate that, and after tweaking the number of values referenced, it's possible to demonstrate that:
Here the query optimizer thought, based on its statistics, that it might have one or two hundred rows returned by each of the two relevant indexes to sort through, so using the index to find them out of the 20,000 possible rows and then AND-ing the two conditions together was considered the faster approach. It had no way of knowing that just searching on the username would quickly find the only two matching rows.
Bitmap index scans are executed by reading the index first, populating the bitmap, and then reading the table in sequential order. This makes them read sequentially as the data is expected to be laid out of disk, approximately, regardless of what the real underlying row ordering is. Each block is read and then the drive is expected to skip forward to the next block. Because this gives output sorted via physical order on disk, the results can easily require sorting afterwards for upper nodes that expect ordered input.
The index bitmaps built by the scans can be combined with standard bit operations, including either AND (return rows that are on both lists) or OR (return rows that are on either list) when appropriate. For non-trivial queries on real-world databases, the bitmap index scan is a versatile workhorse that you'll find showing up quite often.