If i generate a calendar table, and I want to count all the weekend days in 2013 i can:
This estimates at ~104 rows, baring rounding the actual row count.
This is not fast enough for my purposes so i add a filtered index.
And row repeat the count
The index is used in the seek but the estimate is now 365, 'worse' for want of a better word than without the index.
What gives ?
By Dave Ballantyne 263 asked Feb 23, 2013 at 09:37 AM
This is a great question, Dave, thanks for sharing it. The filtered index execution plan does indeed show an estimate of 365 rows, and 104 actual:
The answer to these sorts of questions is often found in the details of the execution plan, or in the underlying statistics, but that is not the case here.
At a very early stage, the optimizer's logical view of your query is (simplified):
At this point, the cardinality estimates derived from statistics are:
The filter (year = 2013) is estimated to produce 365 rows (one row per day of the year). The second filter gets its 104 estimate from the statistics associated with your filtered index.
Part of the optimizer's work is to look at matching filters and scans to indexes. After this, the tree is:
The way the index-matching code works, only index keys are matched to a Filter + Scan combination, so at this point only the
Later on, the optimizer sees that the second Filter (isWeekday = 1) is redundant, since it matches the
We can see the final optimizer output with cardinality information using undocumented trace flags 8607, 8612 and the (well-known) 3604:
Optimization has finished at this point, but this structure is not yet in a form the execution engine can handle.
For one thing, the execution engine does not have a physical operator that does nothing. The NOP group is removed from the tree during final conversion to an executable plan, leaving just the Stream Aggregate and Range Seek (on y = 2013). This form is very close to what we see in XML and graphical plans.
An unfortunate side-effect of removing the PhyOp_NOP group is that the 104 cardinality estimate is lost; we see only the 365 estimate on the Range Seek. Arguably, the NOP estimate could be copied to the operator below it, but that is not what happens right now.
Looks odd - but is it a problem?
The final plan looks odd to us, but remember the NOP group with the correct 104 cardinality estimate does exist within the optimizer's framework - it isn't removed until the optimizer has finished and the output is being translated to execution engine form.
If the query were more complex, the 104 estimate would still be available to operators further up the tree during optimization - they would not, generally speaking, be based on the 365 figure. For example:
The execution plan for that query is:
Notice the Seek still estimates 365 rows, but you have to imagine the removed NOP operator between the Seek and the next operator, the Segment. The Segment (and everything else above it) is based on the correct 104 row estimate from the 'invisible' NOP.
For many reasons, it is often useful to include columns from a filtered index's
With this change in place, there is no NOP group, and no weird-looking estimate:
By SQLkiwi ♦ 6.6k answered Feb 23, 2013 at 01:51 PM