***Answering several mails in one go***

>Actually, the system is working "fast" because of the left joins (I
>don't have not even one inner, or right join), the issue was the
>optimization that FB does.

No, it is working "fast" despite the left join, not because of them. It
accidentally fixed one problem you had, but in general your solution is not a
good solution.

>How can I expand this same query to join another table (FK) from A
>or B and visit it ONLY 237 times?????

I don't know whether 237 times is possible or not, but the query is cluttered
with too many left joins. If possible, try making your query with only inner
joins and no left joins whatsoever. Then report the plan back to this list. I
guess one of the reasons for the optimiser to be slow could be the index
RDB$FOREIGN132. Out of 70368 records, 47047 share one value. If the values you
are searching for does not include this value, then the index could be useful,
but how should the optimizer know that at the time of prepare?

>Again, I think the optimizer should go to statistics and determine
>when to use an index and when not.

It does, but unfortunately it does not always end up with the best possible
result. Then we have to give the optimizer hints as to which indexes it should
avoid, but I do not like the rough way you chose by using LEFT JOINs - that is
telling the optimizer to optimize using this order only as well as making the
query more complex for it. Don't blame the optimizer for choosing wrongly when
providing unneccessary complex queries! I once heard (I'm not sure it is true)
that an early version of InterBase (or maybe even before it was InterBase)
analyzed and did make a perfect plan. The only hatch was that even though
retrieving data went quickly, the prepare phase took far too long (hours or days
for complex queries).

Set