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:

concaturday

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.

delancey

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.

boing boing boing

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!



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

Leave a Reply

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