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.