Scalar function alters estimate for preceding operator

Question

Please could someone help me understand why the presence of a scalar function is changing the estimate of a preceding index scan?

I'm using a copy of the StackOverflow2013 database. SQL Server 2019 (Compatability Mode 100)

To demonstrate the problem, I've created a simple function, which checks if User.DisplayName has an 's' in it:

CREATE FUNCTION dbo.IsUserS (@DisplayName nvarchar(80))

RETURNS bit
AS
BEGIN
    RETURN CHARINDEX('S', @DisplayName)
END
 

Here's the query that uses the function:

SELECT  U.ID,
    U.[DisplayName],
    U.[Reputation]
FROM    [dbo].[Users] AS [U]
WHERE   [U].[CreationDate] > '20100101'
AND [U].[Reputation] > 100
AND dbo.[IsUserS](u.[DisplayName]) = 1

I'm comparing the plan for this query to the exact same query, but without the function call. Here are the plans:

https://www.brentozar.com/pastetheplan/?id=ByLy1LlNi

In both plans, we start with a clustered index scan. For the bottom plan (the one without the filter), the estimate vs actual values for the Scan operator are close. For the plan with the filter, the estimate is way off (36% of the actual number rows).

My question is, why does the presence of the UDF alter the estimate for the preceding index scan?

Note that I'm running in Compat' mode 100. If I flip to 150, the estimate vs actual is actually worse, unless I turn on the legacy cardinality estimator on (in which case, it is the same as 100, so still bad).

Answer 1

It's a consequence of a post-optimization rewrite that pushes suitable predicates down into a seek or scan operator as a residual predicate.

The optimizer's estimates are based on a tree before this rewrite, where all the predicates appear as a separate filter. You can see this plan by enabling undocumented trace flag 9130, which prevents the pushdown rewrite.

When the rewrite occurs, it copies properties from the filter down to the seek or scan as part of the tree adjustment. This accounts for the different estimates you see.

The scalar function cannot be pushed down, but the predicates on CreationDate and Reputation can be. The rewrite is not clever enough to redo the estimates to reflect the fact only some predicates could be successfully pushed down.

It looks weird, but it didn't affect the optimizer's choices, since those were all made before the post-optimization work was done. Pushing the predicates down is a physical optimization. It is cheaper to evaluate the conditions during the seek or scan rather than passing rows up to a separate Filter operator.

---

Estimated plan with the scalar function, predicates not pushed down due to trace flag 9130:

Estimated plan with the scalar function and pushed-down predicates:

The full effect of the Filter has been copied down to the scan because two of the three predicates are evaluated there. This gives the misleading impression that the Filter will eliminate no rows, which is not the case, and not what the optimizer thought either.

---

Scalar functions aren't the only things that prevent predicate pushdown. Large data types and sql_variant expressions can't be evaluated in a seek or scan either. For example:

SELECT
    U.ID,
    U.[DisplayName],
    U.[Reputation]
FROM dbo.Users AS U
WHERE 
    U.CreationDate > '20100101'
    AND U.Reputation > 100
    AND CHARINDEX(N'S', CONVERT(nvarchar(max), U.DisplayName)) = 1;

The predicate on the LOB type can't be pushed down, so you'll see the same apparently inconsistent estimates there too.