Ok, I'll modify my original question.

Why does this first query perform so much worse than the second one?

First query:

with doc_index As

(Select ROW_NUMBER() Over (Order By docdate desc, docid asc) As RowNum

, a.*

from doclist a

inner join DocList_Filters b

on b.colName = 'STATUS'

and a.Status in ( select distinct filterValue

from DocList_Filters

where colName = 'STATUS'

and comparitor = 'and')

where doccode not in ('D')

and (statuscode is null or statuscode in ('A','I','D'))

and doctype not in ('type1','type2')

and docchecked = 1

and ((doctray = 1) or (doctray = 2))

)

select *

from doc_index

where RowNum between 1 and 1000

order by RowNum asc;

Second query:

with doc_index As

(Select ROW_NUMBER() Over (Order By docdate desc, docid asc) As RowNum

, a.*

from doclist a

where doccode not in ('D')

and (statuscode is null or statuscode in ('A','I','D'))

and doctype not in ('type1','type2')

and docchecked = 1

and ((doctray = 1) or (doctray = 2))

and a.status in ('active','inactive')

)

select *

from doc_index

where RowNum between 1 and 1000

order by RowNum asc;

The filters table only contains the 2 records that are manually used in the second query.

The doclist table contains 66 columns and about a million rows.

Is that enough info?