SQL Server: Residual Predicates and Key Lookups
Same plan, fewer reads: promote the filter column into the index
SELECT
*
FROM
dbo.Users
WHERE
Location = 'Helsinki'
AND
Reputation > 10000;
PK_Users (Id)
clustered · has every column
Root page 1:100
Id 1 → 1:200
Id 300,000 → 1:201
Id 500,000 → 1:202
Page 1:200
Id 26,837 · Rep 1,073
Id 84,120 · Rep 25,120
Id 152,477 · Rep 61
Page 1:201
Id 306,912 · Rep 9,999
Id 441,708 · Rep 245
Page 1:202
Id 590,442 · Rep 87,406
IX_Location
(Location)
page reads:
—
2
5
20
Root page 1:900
Aachen → 1:931
Madrid → 1:932
Seattle → 1:933
Aachen → 1:931
Leaf page 1:931
Geneva · Id 41,209
Hamburg · Id 8,114
Helsinki · Id 26,837
Helsinki · Id 84,120
Helsinki · Id 152,477
Helsinki · Id 306,912
Helsinki · Id 441,708
Helsinki · Id 590,442
Istanbul · Id 77,351
Rep ?
Rep ?
Rep ?
Rep ?
Rep ?
Rep ?
✗
✗
✗
✗
IX_Location
(Location)
INCLUDE (Reputation)
page reads:
—
2
8
Root page 1:900
Aachen → 1:931
Madrid → 1:932
Seattle → 1:933
Aachen → 1:931
Leaf page 1:931
Geneva · Id 41,209 · Rep 302
Hamburg · Id 8,114 · Rep 88
Helsinki · Id 26,837 · Rep 1,073
Helsinki · Id 84,120 · Rep 25,120
Helsinki · Id 152,477 · Rep 61
Helsinki · Id 306,912 · Rep 9,999
Helsinki · Id 441,708 · Rep 245
Helsinki · Id 590,442 · Rep 87,406
Istanbul · Id 77,351 · Rep 4,410
same plan, fewer lookups: 20 reads → 8 reads