Why does FORCE_LEGACY_CARDINALITY_ESTIMATION not match ('ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES', 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS')?
2
votes
1
answer
91
views
Assume the StackOverflow2010 database under SQL Server 2022 and compatibility level 160. Consider the following two queries:
Why is this? I know that the legacy cardinality estimator used simple containment, so I presumed that
SELECT
COUNT_BIG(*) AS records
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON (p.OwnerUserId = u.Id
AND p.LastEditorUserId = u.Id)
WHERE
u.DownVotes > 3 AND u.UpVotes > 1
OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
SELECT
COUNT_BIG(*) AS records
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON (p.OwnerUserId = u.Id
AND p.LastEditorUserId = u.Id)
WHERE
u.DownVotes > 3 AND u.UpVotes > 1
OPTION(USE HINT('ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES', 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'));
On my machine, I get the same estimated number of rows from the scan of the Users
table (23,277.1) and the Posts
table (372,920). However, the joins get different estimates. The legacy version estimates 178,865 and the double-hinted version estimates 372,920.


OPTION(USE HINT('ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES', 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'));
and OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
would produce identical plans.
It is the first time that I've run either of these queries, so I presume that there is no intelligent optimization occurring in the background.
Asked by J. Mini
(1237 rep)
Jun 8, 2025, 07:41 PM
Last activity: Jun 10, 2025, 10:36 AM
Last activity: Jun 10, 2025, 10:36 AM