Query Exercise Answers: Why Are These 3 Estimates So Wrong?

This week’s Query Exercise challenged you to figure out why these 3 estimates went so badly:

SELECT TOP 250 p.Id, p.Title, COUNT(*) AS VotesCast FROM dbo.Users u INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId INNER JOIN dbo.Votes v ON p.Id = v.PostId WHERE u.Location = ‘Reading, United Kingdom’ GROUP BY p.Id, p.Title ORDER BY COUNT(*) DESC;

Even though I’ve got indexes on Users.Location, Posts.OwnerUserId, and Votes.PostId, the estimates were terribad:

I challenged you to explain why each of the estimates went badly, in order.

1. Why was the Users.Location estimate off?

SQL Server opened the Users.Location statistics histogram