Bitmap index scan behavior in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to perform a bitmap index scan on the table 'employees' using the index 'idx_salary'.

PostgreSQL

EXPLAIN SELECT * FROM employees WHERE salary [1] 50000;

Drag options to blanks, or click blank then click option'

A=

B<

C>

DLIKE

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' limits the scan to exact matches, not a range.

Using 'LIKE' is for pattern matching, not numeric comparison.

The query uses a condition 'salary > 50000' which can utilize a bitmap index scan on the 'idx_salary' index.

2fill in blank

medium

Complete the code to explain the query plan that uses a bitmap index scan on 'idx_age' for ages less than 30.

PostgreSQL

EXPLAIN SELECT * FROM users WHERE age [1] 30;

Drag options to blanks, or click blank then click option'

A>

B<

C=

D>=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' or '>=' selects ages greater than or equal to 30, which is incorrect here.

Using '=' only matches age exactly 30, not a range.

The condition 'age < 30' allows PostgreSQL to use a bitmap index scan on 'idx_age' to find all users younger than 30.

3fill in blank

hard

Fix the error in the query to enable bitmap index scan on 'idx_status' for status 'active'.

PostgreSQL

EXPLAIN SELECT * FROM accounts WHERE status [1] 'active';

Drag options to blanks, or click blank then click option'

A=

BLIKE

C!=

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'LIKE' is for pattern matching and may not use the index efficiently.

Using '!=' excludes the value and won't use the index for this condition.

The '=' operator is correct for exact match on 'status' to use bitmap index scan on 'idx_status'.

4fill in blank

hard

Fill both blanks to create a bitmap index scan query filtering 'orders' by 'order_date' after 2023-01-01 and 'status' equals 'shipped'.

PostgreSQL

EXPLAIN SELECT * FROM orders WHERE order_date [1] '2023-01-01' AND status [2] 'shipped';

Drag options to blanks, or click blank then click option'

A>

B=

C<

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' for order_date would select before the date, not after.

Using '!=' for status excludes 'shipped' and won't use the index properly.

The '>' operator filters orders after '2023-01-01' and '=' matches status exactly, both suitable for bitmap index scans.

5fill in blank

hard

Fill all three blanks to write a bitmap index scan query on 'products' filtering by 'category' equals 'books', 'price' less than 20, and 'stock' greater than 0.

PostgreSQL

EXPLAIN SELECT * FROM products WHERE category [1] 'books' AND price [2] 20 AND stock [3] 0;

Drag options to blanks, or click blank then click option'

A=

B<

C>

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' excludes the desired values and prevents index usage.

Mixing up '<' and '>' operators changes the filter logic.

The '=' operator matches category exactly, '<' filters price less than 20, and '>' filters stock greater than 0, all suitable for bitmap index scans.