Thanks for the explanation Pavel, most of it was as I expected it to be. It surprised me that Fb chose to use an index for the smaller table if it can choose which table to index. Also, I didn't understand why

>SELECT *FROM JOB J JOIN COUNTRY C ON J.JOB_COUNTRY =C.COUNTRY ;

produce a plan that references the tables,

>PLAN JOIN (JOB NATURAL,COUNTRY INDEX (RDB$PRIMARY1))

whereas

>SELECT * FROM DEPARTMENT D JOIN PROJECT P ON D.MNGR_NO =P.TEAM_LEADER ;

uses the alias names as part of the plan.

>PLAN JOIN (D NATURAL,P INDEX (RDB$FOREIGN13))

Since it is basically the same, I just guessed that you used two different tools for preparing the statements.

Still, I don't understand your original answer to the question that was asked when you said (or at least, I read) that Firebird couldn't choose a plan that avoided using natural. Sure, it chose a plan using natural for one table (which may or may not be the best solution), but it ought to have been possible for Fb to have chosen another plan using indexes for all tables all the time there were a limiting where-clause covering indexed fields on the left side of the left join.

>BTW, If you're going to Fulda, don't miss my session about "Understanding
>Firebird optimizer and execution plan" :-)

I might well do, but according to www.firebird-conference.com this session runs in parallell with Thomas Steinmaurers "1101 Stored Procedure and Trigger Language I" and I surely know less about SPs and triggers than I know about execution plans. Hmm, I will miss something regardless of what I choose.

Set
-Fulda coming up in just over two weeks!