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:

harsh join

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);
murders and executions

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?

no arrows here

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.

arrows are back

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!



One thought on “Common Query Plan Patterns For Joins: Index Intersection

Leave a Reply

Your email address will not be published. Required fields are marked *