I faced same behavior.
I think here is the idea :

select xx ...

FROM TableA A
JOIN TableB B on A.PK = B.Apk
LEFT JOIN TableC on B.PK = C.Bpk
WHERE B.PK = 1

Primary table is defined table TableA, which has no condition, and no order by index.As you defined it as primary table, it will be proposed to scan first (plan natural).
(You can experiment with adding order by index_1,index_whatever and you'll see more interesting stuff)
To avoid this, I usually make sure to place tables in correct join order.This makes optimizer to recognize primary table faster.
In other words,IMHO, optimizer has to go through primary table to scan records (then join). And answer to first question "what is primary table? " sometimes makes big difference in perfomance.

Adomas


Svein Erling Tysvær wrote:

>I normally avoid using outer joins, but this time I had a problem that
>I thought was best solved using a LEFT JOIN. Surprised by the
>suggested plan, I decided to research a little - and got even more
>surprised. Here's the simple SQL statement I ended up with to make the
>optimizer look a fool:
>
>SELECT * /*which fields are irrelevant, but I specified fields from
>all tables*/
>FROM TableA A
>JOIN TableB B on A.PK = B.Apk
>LEFT JOIN TableC on B.PK = C.Bpk
>WHERE B.PK = 1
>
>A.PK and B.PK are primary keys and there are very selective indexes
>for B.Apk and C.Bpk (normally each value is repeated between 1 and 10
>times and each table is over 1 million records in total).
>
>To me, the obvious plan (which is suggested if I use a plain JOIN and
>not a LEFT JOIN) is
>PLAN JOIN (JOIN (B INDEX(PK_INDEXB), A INDEX(PK_INDEXA)), C INDEX
>(B_PK_INDEX))
>
>but the optimizer suggests
>PLAN JOIN(JOIN (A NATURAL, B INDEX(PK_INDEXB)), C INDEX (B_PK_INDEX))
>
>In plain English, in my particular case that means the optimizer
>thinks it is better to walk sequentially through a table with 1
>million records rather than to look up one particular record in
>another table that is marginally bigger.
>
>I tested this on Firebird 1.5.2 and one of the release candidates for
>1.5.3. I have not tested it on Firebird 2.0. Needless to say, when
>hardcoding the plan, the obvious plan executes considerably faster
>than the plan the optimizer suggests.
>
>I know LEFT JOINs make it harder for the optimizer, but I never
>thought it was this easy to confuse it. Why does it behave this way?
>
>Set
>
>
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>