Try this:

select * from

(

  select a.User_ID
  from Advocate

  where a.USER_ID=37

) as FILTER1,  supprog sp

Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code

 

 This doesn't quite execute.  I cleaned it up to this:

 

select * from (
select a.User_ID, a.ADVOCATE_CODE
from Advocate a
where a.USER_ID=37
) as FILTER1,supprog sp
Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code

 

It gets the same plan: "PLAN JOIN (SP NATURAL, FILTER1 A INDEX (ADVOCATE_))".  But it has another problem, too:  ultimately I'm wanting to use this as part of a view, so I can't do my filtering inside the query.

 

Why isn't Firebird using the index?  Do the index statistics have anything to do with it?