Sql-server – When can SARGable predicates be pushed into a CTE or derived table

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. 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.

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;

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

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, too, and runs much faster, with no spill to disk. They both produce the same results, with the predicate at the nonclustered index scan.

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.

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
• ASC