What about trying to put parentheses around the joins?


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


Does that change the plan at all?

-steve

Svein Erling Tysvær wrote:

> Well, Adomas, I tried both
>
> FROM TableA A
> JOIN TableB B on A.PK = B.Apk
> LEFT JOIN TableC C on B.PK = C.Bpk
> WHERE B.PK = 1
>
> and
>
> FROM TableB B
> JOIN TableA A on A.PK = B.Apk
> LEFT JOIN TableC C on B.PK = C.Bpk
> WHERE B.PK = 1
>
> Same plan suggested in both cases. I think it has to do with me LEFT
> JOINing B to C and not A to C.
>
> Set
>
> --- In firebird-support@yahoogroups.com, Adomas Urbanavicius wrote:
>
>> 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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>
>
>
>
>
>
>
>
>