The optimizer has a few different strategies for using multiple indexes on the same table:
- Lookups (Key or Bookmark)
- Index Intersection (Join)
- Index Union (Concatenation)
Lookups are between a nonclustered index and either the clustered index or heap, and index intersection and union are between two nonclustered indexes.
Some plans may even have both! How exciting. How very exciting.
Today we’re going to look at index intersection.
First, we need a couple useful-ish indexes.
CREATE INDEX pc ON dbo.Posts (CreationDate); CREATE INDEX pl ON dbo.Posts (LastActivityDate);
Next, we’ll need a query with AND predicates on both of those columns:
SELECT COUNT_BIG(*) AS records FROM dbo.Posts AS p WHERE p.CreationDate >= '20130101' AND p.LastActivityDate <= '20180101';
The query plan looks like this:
Notice! Both of our nonclustered indexes get used, and without a lookup are joined together.
Because the predicates are of the inequality variety, the join columns are not ordered after each seek. That makes a hash join the most likely join type.
You are unlikely to see a merge join here, because it would require sorting both inputs. You can force one to see for yourself:
SELECT COUNT_BIG(*) AS records FROM dbo.Posts AS p WHERE p.CreationDate >= '20130101' AND p.LastActivityDate <= '20180101' OPTION(MERGE JOIN);
If you attempt to hint a loop join without hinting to use the nonclustered indexes, you will just get one scan of the clustered index.
If you attempt a loop join hint with both indexes hinted, you will get a query processor error.
Serves you right.
If you have equality predicates, you may see a merge join naturally.
SELECT COUNT_BIG(*) AS records FROM dbo.Posts AS p WITH(INDEX (pc, pl)) WHERE p.CreationDate = '2010-08-05 10:04:10.137' AND p.LastActivityDate = '2008-12-25 02:01:25.533';
Look at that sweet double index hint, eh?
Since equality predicates preserve ordering, we don’t need to sort anything here.
If we request a column that is not present in either index, we’ll get a likely more familiar query plan element.
SELECT p.PostTypeId, COUNT_BIG(*) AS records FROM dbo.Posts AS p WHERE p.CreationDate >= '20131201' AND p.LastActivityDate <= '20090101' GROUP BY p.PostTypeId;
We’re able to use a lookup to retrieve the PostTypeId column from the clustered index. Pretty neat.
For more information on this type of plan, check out Paul White’s post here.
Thanks for reading!