I have a query which does not performs well and optimizer scans all 108864 items from table ExternFile uselessly when seeking only 30 rows would be fine. I have simplified the problem to following select from view.
The upper layer query looks like:
SELECT realtyId, ServerPath, OfferState, CategoryId, ParentCategoryId FROM FSTest WHERE OfferState <= 3 AND (CategoryId = 2 OR ParentCategoryId = 2) ORDER BY Ranking DESC OFFSET 2970 ROWS FETCH NEXT 30 ROWS ONLY
The underlaying view this is following:
CREATE View [dbo].[FSTest] AS SELECT realty.Id as realtyId, realty.OfferState, realty.Ranking, Category.Id as CategoryId, Category.ParentCategoryId, ExternFileOuterApply.ServerPath as ServerPath, ExternFileOuterApply.Height as ImageHeight, ExternFileOuterApply.Width as ImageWidth FROM realty JOIN Category ON realty.CategoryId = Category.Id OUTER APPLY (SELECT TOP (1) ServerPath, Height,Width FROM ExternFile WHERE ForeignId = realty.Id AND IsMain = 1 AND ForeignTable = 5) AS ExternFileOuterApply
The problem is the OUTER APPLY. I would expect that the query skips all 2970 rows and execute the OUTER APPLY only for 30 rows which are contained in the result. But the OUTER APPLY is scanning entire table ExternFile (108864 rows). Why?
Is it somehow connected with temporary index in the index spool (see image bellow)? What is wrong with the query?