Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Won't Fix Help for as Won't Fix


60
0
Sign in
to vote
Type: Bug
ID: 767395
Opened: 10/14/2012 11:20:31 PM
Access Restriction: Public
0
Workaround(s)
view
8
User(s) can reproduce this bug

Description

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
SELECT
    th.ProductID,
    th.TransactionID,
    th.TransactionDate
FROM Production.TransactionHistory AS th
WHERE
    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?
Details
Sign in to post a comment.
Posted by Microsoft 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
Sign in to post a workaround.