Hi Bradley,

> InvHead.InvHeadRef is unique and has an index
> AcctName.InvHeadRef is unique and has an index
> IDRelate has an index on IDREf, capacity which is nearly unique.

These are good indexes for this query

> InvHead.IDRef has an index with a selectivity of about 2

Whoops, this one is probably the reason why Firebird thinks a natural
is the best it can do. Try changing your query to

select ...
from
InvHead
inner join IDRelate on
idrelate.IDRef = (InvHEad.IDRef + 0)
and IDRelate.Capacity = 'ADVISOR'
left outer join acctname on
invhead.invheadref = acctname.invheadref
where invhead.invheadref = 'IH00025809'

or similar to prevent Firebird from using this index. Report back to
this list if it works or not.

HTH,
Set
- I support Firebird, I am a FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation