Hi Arno

> > select identityname.idref
> > from identityname
> > inner join InvHead on identityname.idref = invhead.idref
> > left join acctname on invhead.invheadref = acctname.invheadref
> > where invhead.invheadref = 25809
>
> I don't know if i understand you're problem exactly, but above PLAN

can only

> be :
>
> PLAN JOIN(JOIN(identityname NATURAL, InvHead INDEX (...)), acctname

INDEX

> (...))
> or
> PLAN JOIN(JOIN(InvHead NATURAL, identityname INDEX (...)), acctname

INDEX

> (...))
> or
> PLAN JOIN(JOIN(InvHead INDEX (...), identityname INDEX (...)), acctname
> INDEX (...))
>
> Is the last PLAN a option, but the optimizer doesn't choose it?
> The optimizer can decide the order between InvHead and identityname thus
> that shouldn't be a problem. Are your statistics up-to-date?
>

The last plan is an option and is used if acctname is an inner join
but not if it is an outer join. I always thought that this shouldn't
matter to how it chooses between the first two tables but it seems to.
Statistics are completely up to date. I also did a restore last
night so the indexes should be as balanced as possible. I am using
firebird 1.5 on a linux box. Our index fields are mostly 10 character
strings not intergers due to historical reasons if that makes and
difference. The database is about 4gig. The following sqls do choose
the last plan.

select identityname.idref
from identityname
inner join InvHead on identityname.idref = invhead.idref
inner join acctname on invhead.invheadref = acctname.invheadref
where invhead.invheadref = 25809

select identityname.idref
from identityname
inner join InvHead on identityname.idref = invhead.idref || ''
left join acctname on invhead.invheadref = acctname.invheadref
where invhead.invheadref = 25809

IdentityName has 42000 records
InvHead has 45000 records
AcctName has 7000 records

Thanks

Bernard