meme (imported from SE)
## 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