Bitmap index scan behavior in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Bitmap Index Scan Output Rows

Given a table employees with 100,000 rows and an index on the department_id column, what is the expected number of rows returned by this query?

SELECT * FROM employees WHERE department_id = 5;

Assuming department 5 has 5000 employees.

PostgreSQL

SELECT * FROM employees WHERE department_id = 5;

A 500 rows

B 5000 rows

C 0 rows

D 100,000 rows

💡 Hint

Think about how many employees belong to department 5.

The query filters employees by department_id = 5. Since 5000 employees belong to that department, the query returns exactly 5000 rows.

🧠 Conceptual

intermediate

Purpose of Bitmap Index Scan

What is the main advantage of using a bitmap index scan in PostgreSQL compared to a regular index scan?

A It locks the table to prevent concurrent writes.

B It always returns rows faster than a sequential scan.

C It can combine multiple index conditions efficiently before fetching table rows.

D It stores data in a compressed format on disk.

💡 Hint

Think about how bitmap index scans handle multiple conditions.

Bitmap index scans build a bitmap of matching row locations from one or more indexes, then fetch the rows in a single step. This is efficient when combining multiple conditions.

📝 Syntax

advanced

Identify Bitmap Index Scan Usage in EXPLAIN Output

Which EXPLAIN output snippet indicates that PostgreSQL is using a bitmap index scan?

A Aggregate (cost=12.50..12.51 rows=1 width=8)

B Seq Scan on employees (cost=0.00..1000.00 rows=100000 width=100)

C Index Scan using idx_department_id on employees (cost=0.29..8.50 rows=500 width=100)

D Bitmap Index Scan on idx_department_id (cost=4.29..12.50 rows=500 width=100)

💡 Hint

Look for the phrase 'Bitmap Index Scan' in the output.

The phrase 'Bitmap Index Scan' in the EXPLAIN output shows that PostgreSQL is using a bitmap index scan.

❓ optimization

advanced

When to Prefer Bitmap Index Scan Over Index Scan

In which scenario is a bitmap index scan generally more efficient than a regular index scan?

A When the query filters on multiple indexed columns with moderate selectivity.

B When the table is very small and fits in memory.

C When the query returns only a single row by primary key.

D When the query requires sorting by a non-indexed column.

💡 Hint

Think about combining multiple conditions and how bitmap scans work.

Bitmap index scans efficiently combine multiple index conditions and are better when filtering on several columns with moderate selectivity.

🔧 Debug

expert

Diagnosing Unexpected Bitmap Index Scan Behavior

A query uses a bitmap index scan but runs slower than expected. Which of the following is the most likely cause?

A The bitmap index scan fetches many rows scattered across the table causing random I/O.

B The query planner chose bitmap index scan because the table is empty.

C The bitmap index scan locks the entire table preventing parallelism.

D The bitmap index scan compresses data causing CPU overhead.

💡 Hint

Consider how physical data layout affects performance.

Bitmap index scans can be slower if many rows are fetched but scattered, causing many random disk reads.