We have an interesting problem that I was hoping that someone could help shed some light. At a high level, the problem is as follows:
The following query executes quickly (1 second):
SELECT SA.*
FROM cg.SEARCHSERVER_ACTYS AS SA
JOIN CONTAINSTABLE(CG.SEARCHSERVER_ACTYS, NOTE, 'reports') AS T1 ON T1.[Key]=SA.UNIQUE_ID
but if we add a filter to the request, then it will take about 2 minutes to return:
SELECT SA.*
FROM cg.SEARCHSERVER_ACTYS AS SA
JOIN CONTAINSTABLE(CG.SEARCHSERVER_ACTYS, NOTE, 'reports') AS T1 ON T1.[Key]=SA.UNIQUE_ID
WHERE SA.CHG_DATE>'19 Feb 2010'
Looking at the execution plan for the two queries, I see that in the second case there are two places where there is a huge difference between the actual and the estimated number of rows, this:
1) For the FulltextMatch table function, where the estimate is about 22,000 rows, and the actual one is 29 million rows (which are then filtered to 1670 rows before merging) and 2) To search for the index by the full text index, where the estimate is 1 row and the actual - 13,000 lines
( ), , .
, : () OPTION (OPTIMIZE FOR UNKNOWN) (b), HASH JOIN. 1 , .
, " "?
, .
.