Bridge Table Problem in T-SQL

I have a T-SQL statement that is giving me problems.
If the last part of the where clause is not included, runs in about 17 seconds against a 120GB DW/Star Schema SQL 2005 Enterprise
Returns about 255000 rows out of about 25-30 million rows.

If I include the last part of the where clause, it slows down to over 5 minutes (killed before it finishes) Indexes exist on all joined columns and
a covering index exists on DimDiagnosis that includes DiagnosisID, Diagnosis_Aggregate, and Diagnosis_Aggregate_Label.
Query Analyzer shows nothing.
The execution plan for the second statement adds a costly hash match compared to the first.

I realize that I can restructure the T-SQL into parts, but that is not the point for me.
I am trying to understand if this is the nature of BridgeTables, am I missing something or something else.


DimDiagnosis has 700 rows, DimDiagnosisBridge has about 126000

SELECT DISTINCT
FP.client_id ,
DD.Diagnosis_Aggregate_Label
FROM factpaid FP
INNER JOIN dbo.DimDiagnosisBridge DB ON FP.DiagnosisGroup_ID = DB.DiagnosisGroup_ID
INNER JOIN dbo.DimTime DT ON DT.Date_ID = FP.ServiceBegin
INNER JOIN DimDiagnosis DD ON DD.Diagnosis_ID = DB.Diagnosis_ID
WHERE DT.Year_Value BETWEEN 2006 AND 2010
AND FP.DataType_ID <= 3
AND FP.DataSource_ID = 2
--AND DD.Diagnosis_Aggregate = 1

You might see if the execution plan is any different if you move the last part of the WHERE clause into the INNER JOIN on your DimDiagnosis dimension:

SELECT DISTINCT
FP.client_id ,
DD.Diagnosis_Aggregate_Label
FROM factpaid FP
INNER JOIN dbo.DimDiagnosisBridge DB ON FP.DiagnosisGroup_ID = DB.DiagnosisGroup_ID
INNER JOIN dbo.DimTime DT ON DT.Date_ID = FP.ServiceBegin
INNER JOIN DimDiagnosis DD ON DD.Diagnosis_ID = DB.Diagnosis_ID AND DD.Diagnosis_Aggregate = 1
WHERE DT.Year_Value BETWEEN 2006 AND 2010
AND FP.DataType_ID <= 3
AND FP.DataSource_ID = 2