Bitmap index scan behavior in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Bitmap Index Scan in PostgreSQL?

A Bitmap Index Scan is a method PostgreSQL uses to quickly find rows matching a condition by creating a bitmap of matching row locations, which it then uses to fetch the actual rows efficiently.

intermediate

How does PostgreSQL use a bitmap during a Bitmap Index Scan?

PostgreSQL builds a bitmap where each bit represents a row in the table. Bits set to 1 indicate rows that match the index condition. This bitmap helps combine multiple index scans and reduces random disk access.

intermediate

Why is Bitmap Index Scan more efficient than a normal Index Scan for multiple conditions?

Because it can combine multiple bitmaps from different indexes using bitwise operations, reducing the number of rows to fetch and minimizing random disk reads.

beginner

What happens after PostgreSQL creates the bitmap in a Bitmap Index Scan?

PostgreSQL performs a Bitmap Heap Scan, which uses the bitmap to fetch the actual rows from the table in an efficient order, reducing disk seeks.

intermediate

When might PostgreSQL choose a Bitmap Index Scan over a Sequential Scan?

When the query filters on indexed columns and the number of matching rows is moderate, so using the bitmap reduces the cost of fetching rows compared to scanning the whole table.

What does a Bitmap Index Scan create to represent matching rows?

A A hash table of values

B A list of row IDs

C A bitmap with bits for matching rows

D A sorted array of indexes

A Bitmap Index Scan creates a bitmap where each bit corresponds to a row, set to 1 if the row matches the condition.

Which step follows after building the bitmap in a Bitmap Index Scan?

A Sequential Scan

B Bitmap Heap Scan

C Index Only Scan

D Hash Join

After building the bitmap, PostgreSQL performs a Bitmap Heap Scan to fetch the actual rows efficiently.

Why is Bitmap Index Scan useful for queries with multiple conditions on different indexes?

A It combines bitmaps using bitwise operations

B It avoids using indexes

C It sorts the results automatically

D It caches all rows in memory

Bitmap Index Scan can combine multiple bitmaps from different indexes using bitwise AND/OR to find matching rows efficiently.

When is Bitmap Index Scan less efficient than a Sequential Scan?

A When very few rows match

B When the table is empty

C When no indexes exist

D When most rows match

If most rows match, scanning the whole table sequentially is often faster than building and using a bitmap.

What does each bit in the bitmap represent in a Bitmap Index Scan?

A A table row

B A query plan step

C An index page

D A column value

Each bit corresponds to a specific row in the table, indicating if it matches the index condition.

Explain how PostgreSQL uses a Bitmap Index Scan to improve query performance.

Think about how bits represent rows and how combining them helps.

Describe when PostgreSQL might choose a Bitmap Index Scan instead of a Sequential Scan or a normal Index Scan.

Consider query filtering and index availability.