Bitmap Creation in Execution Plan Causes bad Estimate on Clustered Index Scan
6
votes
1
answer
3657
views
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-01-01 00:00:00.000' AND [StackOverflow2010].[dbo].[Posts].[CreationDate] as [p].[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 Posts.CreationDate
?
Asked by SE1986
(2182 rep)
Jan 27, 2022, 11:11 PM
Last activity: Mar 26, 2024, 01:14 PM
Last activity: Mar 26, 2024, 01:14 PM