Given the following simple query on the StackOverflow2010 database:
SELECT u.DisplayName, u.Reputation FROM Users u JOIN Posts p ON u.id = p.OwnerUserId WHERE u.DisplayName = 'alex' AND p.CreationDate >= '2010-01-01' AND p.CreationDate <= '2010-03-01'
I was trying to understand why creating an index
CREATE INDEX IX_CreationDate ON Posts ( CreationDate ) INCLUDE (OwnerUserId)
yields a better estimate on
When I run the query with no indexes, I get Plan 1. In this plan, SQL Server estimates 298,910 rows coming out of the CI scan on Posts and actually 552 come back - this estimate is way off.
Once I add the index, I get Plan 2, which causes an index seek and much more accurate estimate.
I was curious as to why adding an index would cause a better estimate, since statistics are created when a column is used in a
WHERE predicate, regardless of whether it is indexed or not.
On further inspection, I can see the predicate on
Posts.CreationDate is different on plan 1 vs plan 2:
Plan 1 predicate
[StackOverflow2010].[dbo].[Posts].[CreationDate] as [p].[CreationDate]>='2010-01-01 00:00:00.000' AND [StackOverflow2010].[dbo].[Posts].[CreationDate] as [p].[CreationDate]<='2010-03-01 00:00:00.000' AND PROBE([Bitmap1002],[StackOverflow2010].[dbo].[Posts].[OwnerUserId] as [p].[OwnerUserId],N'[IN ROW]')
Plan 2 predicate
Seek Keys: Start: [StackOverflow2010].[dbo].[Posts].CreationDate >= Scalar Operator('2010-01-01 00:00:00.000'), End: [StackOverflow2010].[dbo].[Posts].CreationDate <= Scalar Operator('2010-03-01 00:00:00.000')
So i can see Plan 2 is just going to use the histogram to find the number of rows between the two dates but Plan 1 has a slightly more complicated predicate involving a bitmap probe.
That (I think) explains why the estimate on the seek is more accurate but I am now wondering what is the bitmap probe? I can see in the plan that there is a bitmap created of the user Ids that match the Alex predicate and that is what is being probed.
I wondered "without the index, why wouldn't Plan 1 be the same as Plan 2, the only difference being a CI scan instead of an index seek on CreationDate?"
I did some further testing and found that if I run the query without the index but force the plan to go serial, using
OPTION (MAXDOP 1) I get Plan 3 which has a better estimate on CreationDate despite now doing a CI Scan on Posts. If I look at the predicate, I can see that the probe is now gone and the bitmap is no longer in the plan so this leads me to believe the bitmap is something to do with the plan going parallel.
So my question is - why is a bitmap created when the plan goes parallel and why does it cause such a bad estimate on