B-tree index (default) behavior in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of B-tree index on equality search

Given a table employees(id INT PRIMARY KEY, name TEXT, salary INT) with a B-tree index on salary, what will be the output of the query SELECT id FROM employees WHERE salary = 50000; if there are three employees with salary 50000?

A Returns only one employee id with salary 50000

B Returns the ids of all three employees with salary 50000

C Returns no rows because B-tree indexes do not support equality searches

D Returns an error because salary is not unique

💡 Hint

Think about how B-tree indexes handle equality conditions.

B-tree indexes efficiently support equality searches and will return all matching rows. Since three employees have salary 50000, all their ids are returned.

❓ query_result

intermediate

Range query behavior with B-tree index

Consider a table products(id SERIAL PRIMARY KEY, price NUMERIC) with a B-tree index on price. What will the query SELECT id FROM products WHERE price > 100 AND price <= 200; return if there are five products priced between 101 and 200?

A Returns the ids of all five products priced between 101 and 200

B Returns only products priced exactly 100 or 200

C Returns no rows because B-tree indexes do not support range queries

D Returns an error due to invalid range syntax

💡 Hint

Recall how B-tree indexes handle range conditions.

B-tree indexes support range queries efficiently. The query returns all products with price greater than 100 and less than or equal to 200, which includes the five products.

📝 Syntax

advanced

Syntax for creating a B-tree index

Which of the following SQL statements correctly creates a B-tree index on the email column of the users table in PostgreSQL?

A CREATE INDEX idx_email ON users WITH btree (email);

B CREATE BTREE INDEX idx_email ON users (email);

C CREATE INDEX idx_email ON users USING btree (email);

D CREATE INDEX idx_email ON users (email) USING BTREE;

💡 Hint

Check the correct syntax order for USING clause in CREATE INDEX.

The correct syntax is CREATE INDEX index_name ON table_name USING btree (column_name);. Option D follows this syntax exactly.

❓ optimization

advanced

Choosing columns for B-tree index to optimize queries

You have a table orders(order_id INT, customer_id INT, order_date DATE, status TEXT). Which B-tree index will best optimize queries filtering by customer_id and sorting by order_date?

A CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

B CREATE INDEX idx_customer ON orders (customer_id);

C CREATE INDEX idx_status ON orders (status);

D CREATE INDEX idx_order_date_customer ON orders (order_date, customer_id);

💡 Hint

Think about the order of columns in a multi-column B-tree index.

A B-tree index on (customer_id, order_date) supports filtering by customer_id and sorting by order_date efficiently. The order of columns matters for query optimization.

🧠 Conceptual

expert

Behavior of B-tree index with NULL values

In PostgreSQL, how does a B-tree index treat rows where the indexed column contains NULL values?

A B-tree indexes convert NULLs to zero before indexing

B B-tree indexes include NULL values and can be used to search for NULLs efficiently

C B-tree indexes exclude NULL values and cannot be used to search for NULLs

D B-tree indexes treat NULL as the highest possible value and sort accordingly

💡 Hint

Consider how NULLs are handled in B-tree indexes for search operations.

In PostgreSQL, B-tree indexes do not index rows where the indexed column is NULL. Therefore, searching for NULL values cannot use the B-tree index efficiently.

Practice

1. What is the primary purpose of a B-tree index in PostgreSQL?

easy

A. To speed up searching and sorting operations

B. To store large binary objects

C. To manage user permissions

D. To backup the database automatically

Solution

1. Step 1: Understand the role of indexes

   Indexes help databases find data faster without scanning the entire table.

2. Step 2: Identify B-tree index function

   B-tree indexes organize data to speed up searching and sorting efficiently.

3. Final Answer:

   To speed up searching and sorting operations -> Option A

4. Quick Check:

   B-tree index = speed up search/sort [OK]

Hint: B-tree indexes speed up search and sort operations [OK]

Common Mistakes:

• Confusing B-tree with storing large objects
• Thinking indexes manage permissions
• Assuming indexes handle backups

2. Which of the following is the correct syntax to create a B-tree index on the column username of table users?

easy

A. CREATE BTREE INDEX idx_username ON users (username);

B. CREATE INDEX idx_username ON users USING bitmap (username);

C. CREATE INDEX idx_username ON users (username) USING hash;

D. CREATE INDEX idx_username ON users USING btree (username);

Solution

1. Step 1: Recall correct CREATE INDEX syntax

   The syntax is CREATE INDEX index_name ON table_name USING index_type (column);

2. Step 2: Identify correct index type and syntax

   B-tree is default and specified as USING btree; CREATE INDEX idx_username ON users USING btree (username); matches this exactly.

3. Final Answer:

   CREATE INDEX idx_username ON users USING btree (username); -> Option D

4. Quick Check:

   Correct syntax uses USING btree [OK]

Hint: Use 'USING btree' in CREATE INDEX for B-tree indexes [OK]

Common Mistakes:

• Using incorrect index type like hash or bitmap
• Wrong keyword order in CREATE INDEX
• Omitting USING clause or misspelling it

3. Given a table products(id SERIAL PRIMARY KEY, price NUMERIC) with a B-tree index on price, what will the query SELECT * FROM products WHERE price > 100 ORDER BY price; most likely use?

medium

A. A sequential scan ignoring the index

B. A B-tree index scan to quickly find rows with price > 100

C. A hash index scan on price

D. A bitmap index scan on price

Solution

1. Step 1: Understand query conditions and index type

   The query filters with price > 100 and orders by price; B-tree indexes support range queries and sorting.

2. Step 2: Identify index usage

   PostgreSQL will use the B-tree index to efficiently find and order matching rows.

3. Final Answer:

   A B-tree index scan to quickly find rows with price > 100 -> Option B

4. Quick Check:

   Range query + order = B-tree index scan [OK]

Hint: Range queries with ORDER BY use B-tree index scans [OK]

Common Mistakes:

• Assuming sequential scan always used
• Confusing hash or bitmap index usage
• Ignoring index benefits for range queries

4. You created a B-tree index on column email but notice queries filtering by LOWER(email) are slow. What is the likely problem?

medium

A. B-tree indexes only work on numeric columns

B. The index was created on the wrong table

C. B-tree indexes do not support functions like LOWER() by default

D. The database needs to be restarted to use the index

Solution

1. Step 1: Understand function usage in WHERE clause

   Using LOWER(email) means the query filters on a transformed value, not the raw column.

2. Step 2: Recognize index limitations

   Regular B-tree indexes do not support functions unless a functional index is created.

3. Final Answer:

   B-tree indexes do not support functions like LOWER() by default -> Option C

4. Quick Check:

   Function in filter needs functional index [OK]

Hint: Use functional indexes for queries with functions like LOWER() [OK]

Common Mistakes:

• Thinking B-tree only works on numbers
• Assuming index applies automatically after restart
• Mistaking wrong table for index issue

5. You want to enforce uniqueness on a column serial_number and speed up queries filtering by it. Which is the best approach using B-tree indexes?

hard

A. Create a UNIQUE B-tree index on serial_number

B. Create a non-unique B-tree index and a separate UNIQUE constraint

C. Create a hash index and a UNIQUE constraint

D. Create a UNIQUE constraint without an index

Solution

1. Step 1: Understand uniqueness enforcement

   PostgreSQL enforces UNIQUE constraints using unique indexes, usually B-tree by default.

2. Step 2: Combine uniqueness and performance

   Creating a UNIQUE B-tree index both enforces uniqueness and speeds up lookups on that column.

3. Final Answer:

   Create a UNIQUE B-tree index on serial_number -> Option A

4. Quick Check:

   Unique B-tree index = uniqueness + speed [OK]

Hint: Use UNIQUE B-tree index to enforce uniqueness and speed queries [OK]

Common Mistakes:

• Creating separate index and constraint unnecessarily
• Using hash index which doesn't enforce uniqueness
• Assuming UNIQUE constraint works without an index