Cardinality Estimation Error With Pushed Predicate on a Lookup - by Paul White NZ

Status : 


Sign in
to vote
ID 767395 Comments
Status Active Workarounds
Type Bug Repros 10
Opened 10/14/2012 11:20:31 PM
Access Restriction Public


In SQL Server 2005, a filtering condition on a column retrieved from a Key Lookup is applied in an explicit Filter operator after an apply join. 
In SQL Server 2008 onward, this filter can be pushed down to the lookup during copy out, but cardinality estimates are not updated correctly. 
The post-filter cardinality estimate is applied to the inner side of the lookup join, rather than the result of the join as a whole.

-- SQL Server 2005 AdventureWorks
FROM Production.TransactionHistory AS th
    th.ProductID = 1 
    AND th.TransactionDate BETWEEN '20030901' AND '20031231';

Estimate from index seek: 45 rows
Estimate from clustered index lookup: 1 row per 45 executions
Estimate from join: 45 rows
Estimate from filter: 16.9951 rows

On SQL Server 2008+ the filter is pushed to the key lookup after optimization:

Estimate from index seek: 45 rows
Estimate from lookup: 16.9951 rows per 45 executions (transferred from the filter)
Estimate from join: 45 rows (should be 16.9951)
Filter removed from query plan

Any predicate pushed to a lookup causes this apparent cardinality estimation problem.
Filters pushed to regular scans and seeks appear to result in correct cardinality adjustments. 
(We used trace flag 9130 to explore the copy out rewrite behaviour.)

I realise small changes to the optimizer are generally risky, but the current behaviour results in very strange-looking plans that are tricky to interpret. 
The behaviour also seems to be unintentional given the behaviour of pushed predicates with non-lookup seeks and scans. 
Finally, the issue does not affect plan selection, just the estimates we see when analysing plans, so this might be a low-risk fix?
Sign in to post a comment.
Posted by Pedro [MSFT] on 12/2/2015 at 2:45 AM
This is something we are investigating, and needs thorough testing. This is so we do not introduce wide-spread regressions, like in cases when we had the CE underestimate, and then we would not have had not enough memory granted, leading to spills otherwise.
Posted by Manbeen [MSFT] on 9/17/2013 at 2:44 PM
Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. This is because the fix is risky to implement.
Thanks again for reporting the product issue and continued support in improving our product.
Posted by Microsoft on 10/26/2012 at 11:30 AM
Thanks for the feedback, we are looking into this request