Hi all,

I run a simple query on Firebird 1.5.5:

select *
from
tbl_party party left join tbl_person person on person.party_id =
party.party_id
join tbl_payment p on p.party_id = party.party_id
where
party.party_id in (xxxx, yyyy) // Two parties

All fields are indexed and here is the query plan:
PLAN JOIN (JOIN (PARTY INDEX (PARTY_KEY,PARTY_KEY),PERSON INDEX
(PERSON_KEY)),P INDEX (PAYMENT_PARTY))

Execution is just fine - 68 rows in the result set with the following
details:
tbl_party - 2 indexed reads
tbl_person - 2 indexed reads
tbl_payment - 68 indexed reads

So fas so good. What bothers me is that when I rewrite the query as follows:

select *
from
tbl_party party join tbl_payment p on p.party_id = party.party_id
--> left join tbl_person person on person.party_id = party.party_id
where
party.party_id in (xxxx, yyyy) // Two parties

I get a huge execution penalty while results are the same:

PLAN JOIN (JOIN (PARTY INDEX (PARTY_KEY,PARTY_KEY),P INDEX
(PAYMENT_PARTY)),PERSON INDEX (PERSON_KEY))

tbl_party - 2 indexed reads
tbl_person - 68 indexed reads
tbl_payment - 68 indexed reads

Is result set creation somewhat "position based" depending on the actual
join sequence?
If so, is it true for left/right joins only? Is there any rule of thumb
where to place the left joins?

When I modify the former query as follows:

select *
from
tbl_party party join tbl_payment p on p.party_id = party.party_id
left join tbl_person person on person.party_id = party.party_id
where
p.party_id in (xxxx, yyyy) // Two parties

Results become scary :)

PLAN JOIN (JOIN (PARTY NATURAL,P INDEX
(PAYMENT_PARTY,PAYMENT_PARTY,PAYMENT_PARTY)),PERSON INDEX (PERSON_KEY))

tbl_party - 456'000 indexed reads
tbl_person - 68 indexed reads
tbl_payment - 68 indexed reads

Each of these queries return the same result set and essentially operate
on the same set of data uses one and the same keys, etc.

I would definitely like to here your comments, suggestions, etc.

Best regards and thanks in advance
Alex