Database Animations: What’s a Residual Predicate and Why Is It Bad?

Normally when you’re looking at an execution plan, and you see an index seek followed by a key lookup, that means it’s running relatively quickly.

To explain, let’s take the Users table in the Stack Overflow database and run this query that we explained in this post:

As long as we have an index on Location, we’re able to dive directly to the people who live in Helsinki thanks to the structure of the B-tree index:

If we modify our query a little by selecting all of the columns instead of just Id and Location, then we have to do a Key Lookup, like we talked about in the How to Think Like the Engine class. For each person who lives in Helsinki, we have to look up their row in the clustered index in order to fetch all the columns we need. That’s not really a big deal, though, as long as a relatively limited number of people live in Helsinki. Like I wrote in that post, the index seek + key lookup is essentially two index seeks: one into Helsinki, and then one seek (for each Helsinki resident) on the clustered index, by their Id.

However, let’s add a little more complexity to the query:

Now, I’m only looking for the high-reputation people who live in Helsinki. The execution plans for both of the queries, with and without the Reputation filter, both look the same:

But there’s something a little tricky about that. Because Reputation isn’t in our index on Location, we’re doing a Key Lookup on every person who lives in Helsinki, even when they don’t meet our Reputation filter. We end up doing a lot more logical reads than necessary in order to check their locations, as this animation illustrates:

The solution: add Reputation to the index, but here’s the fun part: Reputation doesn’t even need to be in the key! It can even be in the includes of the index. Just simply being in the includes means that we don’t have to do the additional logical reads to do the key lookup from the clustered index.

To understand if this is happening to you, hover your mouse over the key lookup operator in your query plan and look for the term “Predicate” without any prefix, like “Seek Predicate” – you’re looking for just plain old “Predicate”, like this:

The “1 of 1” numbers on the Key Lookup sound nicely small, like those guys at the car show who say their Corvette is 1 of 1, when in reality they mean it was just 1 of 1 cars that were done with purple and a yellow stripe, with brown suede interiors, and contrasting carbon fiber floor mats, built on a Thursday, by guys named Mo. That key lookup was done once for every one of the 122 rows found in Helsinki – when there are way fewer rows that actually come out of the key lookup with a >10,000 reputation score.

When Is This Bad, and How Do You Fix It?

Residual predicates are bad IF they’re selective.

In this case, Reputation > 10000 is indeed very selective, so if we could fix them at the index level, we’d do way less logical reads. To fix it, I could promote the Reputation column up into the Location index. I’m less concerned about whether columns like this are in the key of the index, or where they’re at in the key, or in the includes, as long as they’re at least somewhere in the index. If they’re not in the index at all, you’re gonna be way more likely to hit problems with key-lookup-versus-table-scan decisions in the optimizer, leading to parameter sniffing problems, leading to falling-off-the-cliff performance emergencies.

If the filter is NOT selective – like if the filter was like User.Alive = 1 – then I don’t really care about fixing it. In fact, I’d be fine with leaving that predicate in place, because I’d rather store that value just once (on the clustered index) instead of duplicating it on every nonclustered index simply because we filter on it a lot.

This is why, in my Fundamentals of Index Tuning class, I emphasize that your big job in index tuning is just to get the right columns on the leaf pages of the index.  Then, in my Mastering Index Tuning class, we dig into these kinds of edge cases where you have to decide which columns need to be in the key, versus which can ride along inexpensively in the included columns just to reduce residual predicate key lookups. See you in class!

Note: I generated the animations in this post with Claude Code, but all of the text & demos are completely written by me.