Sample Header Ad - 728x90

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:
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. legacy join doubled hint join Why is this? I know that the legacy cardinality estimator used simple containment, so I presumed that 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