Understanding SQL Indexes and Query Performance

Clustered vs. Non-Clustered Indexes

• A clustered index determines the physical order rows are stored in on disk. A table can have at most one, because rows can only be physically sorted one way at a time. The primary key is usually the clustered index by default (SQL Server and MySQL/InnoDB both do this automatically; Postgres notably does not — its primary key is a regular unique index, and physical row order is unrelated to it unless you explicitly CLUSTER the table, a one-time reordering that doesn't stay maintained automatically).
• A non-clustered index (sometimes called a secondary index) is a separate structure that stores the indexed column(s) plus a pointer (or, on a table with a clustered index, the clustered key) back to the actual row. A table can have many of these.

Looking up a value through a non-clustered index that doesn't contain every column a query needs requires an extra step — a key lookup (also called a bookmark lookup) back to the actual table row to fetch the remaining columns. On a query touching many rows, that extra round trip per row can dominate the query's total cost even though the index itself was used correctly.

CREATE INDEX idx_dept ON Employees (dept);

SELECT dept, name, salary FROM Employees WHERE dept = 'Sales';
-- The index finds matching rows by dept efficiently, but name and salary
-- aren't in the index, so the engine still needs a key lookup per row
-- to fetch them from the actual table.