or
Erik Darling
sql-server
## Sandbag

While working on Top Quality Blog Posts®, I came across some optimizer behavior I found really ~~infuriating~~ interesting. I don't immediately have an explanation, at least not one I'm happy with, so I'm putting it here in case someone smart shows up.

If you want to follow along, you can grab the 2013 version of the [Stack Overflow data dump here][1]. I'm using the Comments table, with one additional index on it.

    CREATE INDEX [ix_ennui] ON [dbo].[Comments] ( [UserId], [Score] DESC );

## Query One

When I query the table like so, I get an [odd query plan][2].

    WITH x
        AS
         (
             SELECT   TOP 101
                      c.UserId, c.Text, c.Score
             FROM     dbo.Comments AS c
             ORDER BY c.Score DESC
         )
    SELECT *
    FROM   x
    WHERE  x.Score >= 500;

[![NUTS][3]][3]

The SARGable predicate on Score isn't pushed inside the CTE. It's in a filter operator much later in the plan.

[![NUTS][4]][4]

Which I find odd, since the `ORDER BY` is on the same column as the filter. 

## Query Two

If I change the query, it does get pushed.

    WITH x
        AS
         (
             SELECT   c.UserId, c.Text, c.Score
             FROM     dbo.Comments AS c
         )
    SELECT TOP 101 *
    FROM   x
    WHERE  x.Score >= 500
    ORDER BY x.Score DESC;

The [query plan changes][5], too, and runs much faster, with no spill to disk. They both produce the same results, with the predicate at the nonclustered index scan.

[![NUTS][6]][6]

[![NUTS][7]][7]

## Query Three

This is the equivalent of writing the query like so:

    SELECT   TOP 101
             c.UserId, c.Text, c.Score
    FROM     dbo.Comments AS c
    WHERE c.Score >= 500
    ORDER BY c.Score DESC;

## Query Four

Using a derived table gets the same "bad" query plan as the initial CTE query

    SELECT *
    FROM   (   SELECT   TOP 101
                        c.UserId, c.Text, c.Score
               FROM     dbo.Comments AS c
               ORDER BY c.Score DESC ) AS x
    WHERE x.Score >= 500;


### Things get even weirder when...

I change the query to order the data ascending, and the filter to `<=`.

To keep from making this question overlong, I'm going to put everything together.

## Queries

    --Derived table
    SELECT *
    FROM   (   SELECT   TOP 101
                        c.UserId, c.Text, c.Score
               FROM     dbo.Comments AS c
               ORDER BY c.Score ASC ) AS x
    WHERE x.Score <= 500;
    
    
    --TOP inside CTE
    WITH x
        AS
         (
             SELECT   TOP 101
                      c.UserId, c.Text, c.Score
             FROM     dbo.Comments AS c
             ORDER BY c.Score ASC
         )
    SELECT *
    FROM   x
    WHERE  x.Score <= 500;
    
    
    --Written normally
    SELECT   TOP 101
             c.UserId, c.Text, c.Score
    FROM     dbo.Comments AS c
    WHERE c.Score <= 500
    ORDER BY c.Score ASC;
    
    --TOP outside CTE
    WITH x
        AS
         (
             SELECT   c.UserId, c.Text, c.Score
             FROM     dbo.Comments AS c
         )
    SELECT TOP 101 *
    FROM   x
    WHERE  x.Score <= 500
    ORDER BY x.Score ASC;

Plans
-----
[Plan link][8].

[![NUTS][9]][9]

Note that none of these queries take advantage of the nonclustered index -- the only thing that changes here is the position of the filter operator. In no case is the predicate pushed to the index access.

## A Question Appears!

Is there a reason that a SARGable predicate can be pushed in some scenarios and not in others? The differences within the queries sorted in descending order are interesting, but the differences between those and the ones that are ascending bizarre.

For anyone interested, here are the plans with only an index on `Score`:

 - [DESC][10]
 - [ASC][11]


  [1]: https://brentozar.com/go/querystack
  [2]: https://www.brentozar.com/pastetheplan/?id=ByaivOq-E
  [3]: https://i.stack.imgur.com/joCG8.jpg
  [4]: https://i.stack.imgur.com/vGPWm.jpg
  [5]: https://www.brentozar.com/pastetheplan/?id=SkxctOc-4
  [6]: https://i.stack.imgur.com/gOh4o.jpg
  [7]: https://i.stack.imgur.com/il4yj.jpg
  [8]: https://www.brentozar.com/pastetheplan/?id=S1KsC_cbV
  [9]: https://i.stack.imgur.com/eyw4w.jpg
  [10]: https://www.brentozar.com/pastetheplan/?id=Hkvg2FqWE
  [11]: https://www.brentozar.com/pastetheplan/?id=HkL42t5-N
Top Answer
Paul White
There are a few issues in play here.

### Pushing predicates past `TOP`

The optimizer cannot currently push a predicate past a `TOP`, even in the limited cases where it would be safe to do so*. This limitation accounts for the behaviour of all the queries in the question where the predicate is in a higher scope than the `TOP`.

The work around is to perform the rewrite manually. The fundamental issue is similar to the case of [pushing predicates past a window function][1], except there is no corresponding specialized rule like `SelOnSeqPrj`.

My personal opinion is that an exploration rule like `SelOnTop` remains unimplemented because people have deliberately written queries with `TOP` in an effort to provide a kind of 'optimization fence'.

<sub>\* Generally this means the predicate should appear in the `ORDER BY` clause associated with the `TOP`, and the direction of any inequality should agree with the direction of the sorting. The transformation would also need to account for the sorting behaviour of NULLs in SQL Server. Overall, the limitations probably mean this transformation would not be generally useful enough in practice to justify the additional exploration efforts.</sub>

### Costing issues

The remaining execution plans in the question can be explained as cost-based choices due to the distribution of values in the `Score` column (many more rows <= 500 than >= 500), and the effect of the [row goal][2] introduced by the `TOP`.

For example, the query:

    --Written normally
    SELECT TOP (101)
        c.UserId, 
        c.[Text],
        c.Score
    FROM dbo.Comments AS c
    WHERE
        c.Score <= 500
    ORDER BY
        c.Score ASC;

...produces a plan with an apparently unpushed predicate in a Filter:

[![late filter due to row goal][3]][3]

Note that the Sort is estimated to produce 101 rows. This is the effect of the row goal added by the Top. This affects the estimated cost of the Sort and the Filter enough to make it seem like this is the cheaper option. The estimated cost of this plan is **2401.39** units.

If we disable row goals with a query hint:

    --Written normally
    SELECT TOP (101)
        c.UserId, 
        c.[Text],
        c.Score
    FROM dbo.Comments AS c
    WHERE
        c.Score <= 500
    ORDER BY
        c.Score ASC
    OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));

...the execution plan produced is:

[![plan without row goal][4]][4]

The predicate has been pushed into the scan as a residual non-sargable predicate, and the cost of the whole plan is **2402.32** units.

Notice that the `<= 500` predicate is not expected to filter out any rows. If you had chosen a smaller number, like `<= 50`, the optimizer would have preferred the pushed-predicate plan regardless of the row goal effect.

For the query with `Score DESC` and a `Score >= 500` predicate:

    --Written normally
    SELECT TOP (101)
        c.UserId, 
        c.[Text],
        c.Score
    FROM dbo.Comments AS c
    WHERE
        c.Score >= 500
    ORDER BY
        c.Score DESC;

Now the predicate is expected to be very selective, so the optimizer chooses to push the predicate *and* use the nonclustered index with lookups:

[![selective predicate][5]][5]

Again, the optimizer considered multiple alternatives and chose this as the apparently cheapest option, as usual.

  [1]: https://stackoverflow.com/a/15304023/440595
  [2]: https://sqlkiwi.blogspot.com/2010/08/inside-the-optimiser-row-goals-in-depth.html
  [3]: https://i.stack.imgur.com/joycE.png
  [4]: https://i.stack.imgur.com/6nFbq.png
  [5]: https://i.stack.imgur.com/DW3Cn.png
When can SARGable predicates be pushed into a CTE or derived table?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.