Hi,

I have been trying to get the following reasonably simple SQL to
optimize but cannot. Every time it's run it does a natural scan on
IDRelate rather than using the index, which makes it much slower.

select
InvHead.IDRef,
InvHead.InvHeadRef,
InvHead.IHStatus,
InvHead.FeePlanRef, idrelate.idlinkref
from
InvHead
inner join IDRelate on
idrelate.IDRef = InvHEad.IDRef and IDRelate.Capacity = 'ADVISOR'
left outer join acctname on invhead.invheadref = acctname.invheadref
where invhead.invheadref = 'IH00025809'

Some Info:

InvHead.InvHeadRef is unique and has an index
AcctName.InvHeadRef is unique and has an index
InvHead.IDRef has an index with a selectivity of about 2
IDRelate has an index on IDREf, capacity which is nearly unique.
InvHead has 37,000 records
AcctName has 6,000 records
IDRElate has 57,000 records

The above works ok in PostgreSQL and SQL-Server.

Thanks.