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!
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.
If we use two equality predicates, we’ll get more orderly operations, like merge join concatenation and a stream aggregate.
What a nice plan.
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.
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!