"Estimated Number of Executions" is inflated

Question

I have a situation where a slower query plan is being chosen instead of a faster one due to, I believe, some incorrect estimates. However, I can't figure out where the incorrect estimates are coming from. Shown below is the faster plan which is not chosen because of the estimated cost of 123 for the Index Seek. In reality the cost isn't nearly so high as you can see from the difference between Actual and Estimated Number of Executions. My understanding is that the number of executions is driven by the number of rows from the top side of the nested loop. As you can see, the estimated number of rows there is 4878, which is pretty close to actual. But the estimated number of executions for the bottom input is 61110, which is way off. FWIW, I've updated statistics on all tables with full scan, and the 1.22 Estimated Rows is correct (for each execution).

Where is that 61110 number coming from and is there any way to fix it?

The query looks like this:

    SELECT
        Top.Pk
    FROM
        Top
        LEFT JOIN Bottom ON Bottom.Fk = Top.Pk
    WHERE
        Top.Date < GETUTCDATE()
        AND Bottom.Fk IS NULL

Answer 1

Although I still don't fully understand what the execution plan is showing us here, as it turns out this particular instance of the problem can be fixed by specifying WITH (NOEXPAND) on the indexed view, forcing the optimizer to consider the index on the indexed view (which I thought it was doing already from the execution plan, but apparently not).