Hi Rob!

There are at least two plans the optimizer may decide to use with

SELECT *
FROM SupplyLine sl
JOIN SupplyOrder so ON so.SupOrdrRef = sl.SupOrdrRef
WHERE so.EntityRef = 4

(I'll write field names rather than index names this time)

This is good:
PLAN JOIN (SupplyOrder(EntityRef), SupplyLine(SupOrdrRef))
whereas this is potentially bad:
PLAN JOIN (SupplyLine(NATURAL), SupplyOrder(SupOrdrRef, EntityRef))

(not only due to NATURAL, also because of excessive use of indexes for
SupplyOrder)

Now, the query you ended up with is your work, not mine. What I
suggested was the opposite of what you did! I assumed it was
SupplyLine.EntityRef and not SupplyOrder.EntityRef. I'm pretty certain
this would have been quicker than your solution if that had been the
whole picture, but in your final solution you introduced a few more
indexes (for checking NULL) that requires quite a bit of knowledge of
your data to tell whether they are useful or not.

I added the +0 in order to give the optimizer a hint as to which table
to put first in the plan. The plans used with or without the +0 may be
very different, so don't blame it exclusively on 'low selectivity' for
SupOrdrRef - even though low selectivity is one of the things to try
to avoid.

For the fun of it, does any of these two execute even faster?

SELECT *
FROM SupplyLine sl
JOIN SupplyOrder so ON so.SupOrdrRef = sl.SupOrdrRef + 0
WHERE so.EntityRef + 0 = 4 //avoid the EntityRef index
AND sl.SupTranRef IS NULL
AND sl.PackSlpRef IS NULL
AND sl.CancelFlag <> 'T'

and

SELECT *
FROM SupplyLine sl
JOIN SupplyOrder so ON so.SupOrdrRef + 0 = sl.SupOrdrRef
WHERE so.EntityRef = 4
AND (2=0 //avoid all these indexes and change order of tables
OR (sl.SupTranRef IS NULL
AND sl.PackSlpRef IS NULL
AND sl.CancelFlag <> 'T'))

Set

--- In firebird-support@yahoogroups.com, Robert martin wrote:
> Wow !!!!!
>
> You are 100% right Svein. Changing my query to
>
> SELECT *
> FROM SupplyLine sl
> JOIN SupplyOrder so ON so.SupOrdrRef = sl.SupOrdrRef + 0 <= Note
the + 0
> WHERE so.EntityRef = 4
> AND sl.SupTranRef IS NULL
> AND sl.PackSlpRef IS NULL
> AND sl.CancelFlag <> 'T'
>
> Speeds it up massively. I now get results faster than all my other
> tests (even the fast ones !). I assume this means the index on
> SupOrdrRef has too lower selectivity in this case? Can someone
> explain whats going on here.