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?

Are the statistcs up to date ?

The two tables you mentioned has the same amount of records ? The statistics are quite diferent, so, or the number of rows are bery diferent or the index statistics are out of date.

Try this one:

select sp.STUDENTSEQ, a.User_ID
from supprog sp
join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code+0
where a.USER_ID=37

see you !