[Solved] What will be the result of this query?SELECT department, salary FROM employees WHERE department = 'Sales';Assuming PostgreSQL uses an index-only scan, what rows are returned? | PostgreSQL

PostgreSQL - Indexing Strategies

Consider a table employees(id, department, salary) with an index on (department, salary). What will be the result of this query?

SELECT department, salary FROM employees WHERE department = 'Sales';

Assuming PostgreSQL uses an index-only scan, what rows are returned?

AAll employees in Sales with their department and salary

BAll employees with their id and salary

COnly employees with salary above average

DNo rows because id is missing in index

Step-by-Step Solution

Solution:

Step 1: Check index columns and query columns
The index covers 'department' and 'salary'. The query selects these two columns and filters on 'department'.
Step 2: Understand index-only scan output
Since all needed columns are in the index, PostgreSQL can use an index-only scan and return all employees in 'Sales' with their department and salary.
Final Answer:
All employees in Sales with their department and salary -> Option A
Quick Check:
Index-only scan returns filtered rows with indexed columns = C [OK]

Quick Trick: Index-only scans return only indexed columns used in query [OK]

Common Mistakes:

Expecting id column in output without selecting it
Assuming salary filter without condition
Thinking index-only scan fails if id missing

Master "Indexing Strategies" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Consider a table employees(id, department, salary) with an index on (department, salary). What will be the result of this query?

Step 1: Check index columns and query columns

Step 2: Understand index-only scan output

Final Answer:

Quick Check:

Want More Practice?