Rob,

This is probably a weakness with the optimiser in FB1.5. I believe it
is addressed in FB2, but you will probably find the following works
better.

SELECT a,b,c
FROM Table1 t1
JOIN Table2 t2 ON t2.pk = t1.FK and t1.PK = 4
where t2.CancelFlag <> 'T'

If not, you could force the join order using for example a left join,
or you could dictate the plan you want it to use using the plan
directive. In this case, left join will be equivalent to join because
you are joining to a primary key (which can not be null).

SELECT a,b,c
FROM Table1 t1
left JOIN Table2 t2 ON t2.pk = t1.FK
where t1.PK = 4
and t2.CancelFlag <> 'T'

In fact there are a number of other options. You could create a
compound index on (t2.cancelflag, t2.pk) which would have very good
selectivity and would help in this case

It's a tricky one. I don't like moving what is essentially a where
condition into a join because I believe it is important for
maintainability to separate the join conditions from the where clause.
Where we have done this sort of thing, we have noted it down so that
when the optimiser issue is addressed we can adjust the necessary
queries.

Are you using any sort of plan analysis tool because if so the reason
for the slowness should be clear (even if the optimisers logic for that
choice of path may not be so clear).

Adam