Helen and Milan helped you get rid of the irrelevant IDX_DESTINATION2
index, but didn't answer your WHY question.

Let's pretend we're 'optimizers' who should find a plan for the main
part of your query.

First, how do we limit the rows? Ah, EVENTS.ROWID does that and that
field has an index! What should it be? Hmm, it should have one of the
values in a subselect... Well, I'm not clever enough to know if the
subselect is constant or varies for each row, hence I cannot use an
index for that requirement. No further criteria for EVENTS? OK, I'll go
NATURAL then. (I skipped the bit with why it doesn't use the index for
the DISTINCT - I think the reason for the subselect, but not the main
select using an INDEX for the DISTINCT, is that the subselect can create
a bitmap of the EVENTID and IP indexes, whereas combining NATURAL with
any index is nonsense.)

Now, I don't know how clever the optimizer has to be to meet the 'clever
enough' criteria. Clearly, in your case it could find the
DESTINATION.EVENTID "subresult set" only once, but (sub)queries can be
much more complicated and Firebird has to handle every possible
situation. Currently it doesn't recognize whether subselects are
completely independent from the main select (and could be converted into
something like a join), I have no idea of whether it will be able to do
this in the future.

Set
-friend of the optimizer

yoav112003 wrote:

> I am trying to run the following query (FB 2 RC3):
>
> SELECT distinct ROWID FROM events
> WHERE events.ROWID IN (
>
> SELECT DISTINCT DESTINATION.EVENTID FROM DESTINATION
> WHERE ( (DESTINATION.IP ='158.125.73.220'))
> )
>
> The default plan that is used is:
> PLAN SORT ((DESTINATION INDEX (IDX_DESTINATION2, IDX_DESTINATION1)))
> PLAN SORT ((SEAM_EVENT NATURAL))
>
> where IDX_DESTINATION2, IDX_DESTINATION1 are indexes on
> DESTINATION.EVENTID and DESTINATION.IP respectively.
>
> This plan is very ineffecient since the plan does not rely on the
> index for events.ROWID. When I try to use a customized plan with this
> index, FB replies that the index cannot be used in this query.
>
> Is there anything else that can be done (except for using INNER JOIN)?
> Why can't FB use the indexes on the events table?
>
> Thanks.