Common Query Plan Patterns For Joins: Index Intersection

Incomplete

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.

Intervals

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.

Other Joins

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.

Equality Predicates

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.

You’re welcome.

Plus Lookups

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;

Hooray.

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!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.