Hi all,
I have a query which joins 3 tables and some more criterias. It runs
for more than 5 minutes and still running (I can't wait longer). I
check the execution plan, it showed that table 2 is accessed using 2
indexes, and table 3 is accessed using 2 indexes. In fact I do have 2
indexes on both tables and ib thinks both indexes could satisfy the
query. I'm not on my development PC, so I can't remember exactly the
plan, but it looked like this:
SORT ( JOIN (TABLE1(RDB$PRIMARY1), TABLE2(RDB$PRIMARY2,
RDB$FOREIGN1), TABLE3(RDB$PRIMARY3, RDB$FOREIGN2)...

I write a manual plan:
SORT ( JOIN (TABLE1(RDB$PRIMARY1), TABLE2(RDB$PRIMARY2), TABLE3
(RDB$PRIMARY3) ...

each table is using 1 index only, and now the query runs in only 10
seconds!!

What's wrong here? It looks like ib is performing very poorly when
the execution plans showed 2 indexes could satisfy a query. Anybody
can explain about this?

Thanks & best regards,
Sindu