> The SYSCTBL table usually only has 1 record in it. At most, 4 or 5.
> ALL of my join fields are indexed.

OK, with a handful of records, NATURAL is a good choice.

>> As long as the fields are comparable - meaning your not joining a
>> floating point number to a date - the optimizer should work on them.
>> It may be that the order of the left joins leaves no opportunity to
>> use the indexed terms. As you said, inner joins are much easier for
>> the optimizer to handle.
>
> Which is why I pared down my query to no joins at all in my original post.
> A simple query that just lists the complete table. It still uses un-indexed
> reads.

To understand your problem, you actually needs two tables in your query.

This plan is fine:

PLAN SORT(LOCATIONITEMS NATURAL)

This isn't:

PLAN SORT(JOIN(LOCATIONITEMS NATURAL, ITEMTBL NATURAL))

So why doesn't

FROM LOCATIONITEMS
JOIN ITEMTBL ON LOCATIONITEMS.ITEMID = ITEMTBL.ITEM_NUMBER

use an index for the last of the tables in the plan (I assume you tried turning it into an inner join)? Ann's answer doesn't indicate that explicit typecasting should be necessary for indexes to be used. What's the selectivity of ITEMID and ITEM_NUMBER? The field names indicate they should be pretty selective and I guess both tables contain a substantial amount of records... I'm puzzled, it's easy to see that your problem is in this join, I've just no idea why Firebird goes natural for both tables rather than just one of them. I expect you to still get the same plan if you alter the type of either column so they are of the same type (alternatively, add another column, index etc. and copy values). Can you show us all index definitions for the two fields (i.e. both keys, unique constraints and normal indexes) as well as some information about their selectivity?

Sorry for not being of much help,
Set