Common Query Plan Patterns For Joins: Index Union

I’m On My Smoke Break

Index union is a little bit different from index intersection. Rather than joining two indexes together, their result sets are concatenated together.

Just like you’d see if you wrote a query with union or union all. Crazy, huh?

As with index intersection, the optimizer has a choice between concatenation and merge join concatenation, and lookups back to the clustered index are possible.

Here are the indexes we’ll be working with:

CREATE INDEX po ON dbo.Posts
    (OwnerUserId);

CREATE INDEX ps ON dbo.Posts
    (Score);

Let’s jump right in!

Big Run

The main difference between when you’re likely to get index union vs index intersection is if your predicates use AND or OR. Index intersection is typically from AND predicates, and index union is typically from OR predicates. So let’s write some OR predicates, shall we?

SELECT
    COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 0
OR    p.Score > 10;

First, using one equality and one inequality predicate:

Since we lose the ordering on the inequality predicate, this is our plan. Note he hash match aggregate after the concatenation, too.

No arrow for that, though.

More Wedding

If we use two equality predicates, we’ll get more orderly operations, like merge join concatenation and a stream aggregate.

What a nice plan.

Wafting

If we add in a column that’s not in either index, we’ll see a similar lookup plan as yesterday’s query.

SELECT
    p.PostTypeId,
    COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
OR    p.Score > 10
GROUP BY p.PostTypeId;

Woah baby look at all that sweet parallelism.

Big Time

Both index intersection and index union are underappreciated query plan shapes, and represent some pretty cool optimizer strategies to make your queries not stink.

Of course, there are cases where it might make more sense to have one index that covers all necessary key columns, but it’s not always possible to have every index we’d like.

Tomorrow we’ll look at lookups, and some of the finer details of them.

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.