Given a table employees(id SERIAL PRIMARY KEY, salary INT) with a B-tree index on salary, what will be the result of this query?
medium📝 query result Q4 of 15
PostgreSQL - Indexing Strategies
Given a table employees(id SERIAL PRIMARY KEY, salary INT) with a B-tree index on salary, what will be the result of this query?
SELECT * FROM employees WHERE salary > 50000 ORDER BY salary ASC LIMIT 3;
AReturns an error because LIMIT cannot be used with indexed queries
BReturns the 3 employees with the lowest salaries above 50000, using the B-tree index for fast retrieval
CReturns 3 employees with the highest salaries below 50000
DReturns all employees with salary above 50000 without using the index
Step-by-Step Solution
Solution:
Step 1: Understand B-tree index usage for range queries
B-tree indexes efficiently support range queries like salary > 50000 and ORDER BY salary ASC.
Step 2: Analyze query behavior
The query returns the first 3 rows matching the condition, ordered by salary ascending, using the index for speed.
Final Answer:
Returns the 3 employees with the lowest salaries above 50000, using the B-tree index for fast retrieval -> Option B
Quick Check:
Range query + ORDER BY uses B-tree index = Returns the 3 employees with the lowest salaries above 50000, using the B-tree index for fast retrieval [OK]
Quick Trick:B-tree indexes speed up range queries with ORDER BY [OK]
Common Mistakes:
Thinking index is not used for range queries
Confusing LIMIT effect on results
Assuming query returns error with LIMIT
Master "Indexing Strategies" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
