Incorrect cardinality estimation in join on trusted foreign keys

Chintak Chhapia 2013-06-03 11:21:09

All three queries refer to same tables and there is a trusted foreign key relationship between two tables, still when joined on foreign key column, sql estimates incorrect cardinality.

Query-1:- Join two tables and also add lookup table filed into select

Query-2:- Join two tables and does not added lookup table filed into select. Just to prove that trusted foreign key exists between two tables.

Query-3:- Selecting all the rows from base table where corrosponding rows does not exists in lookup table. This query returns 0 row, but estimate was showing 2231 rows.

Plan:- link text

Same 2231 rows difference exists for query 1.

Is there any reason why optimizer reducting the estimates in join done on columns involved in foreign key relationship?

Thanks in advance for any insight.


SQLkiwi 2013-06-04 01:23:12
This is a known limitation; at the current time FK joins are estimated the same as if the relationship did not exist.

Despite the "Won't Fix" status of the linked Connect item, it is still possible this issue will be addressed by other work, but for the moment, that's how things stand.

Chintak Chhapia 2013-06-11 12:18:49
Hi Paul,

Many thanks for the comment. In my real life case this is trusted relationship on non nullable column, so changing join to LEFT OUTER was not going to cause any harm to output and it had also resolved the cardinality estimation issue.

Again, thanks for taking time for providing help.