Re: [Firebird-devel] Possible optimizer oddity

[Dmitry Y. <di...@us...>]

"Eyal" <ey...@re...> wrote:
>
> Query 1:
>
> SELECT
>    entry.id, acct.id, trans.id
> FROM
>    entry
> JOIN
>    acct  ON acct.id=entry.a1_id
> LEFT OUTER JOIN
>    trans ON trans.id=entry.t_id
> WHERE
>    entry.id=1
>
> PLAN JOIN (JOIN (ACCT NATURAL,ENTRY INDEX (PK_ENTRY)),TRANS INDEX
> (PK_TRANS))
>
> Why scan ACCT when clearly there can't be more than 1 row in ENRTY that
> satisfies the condition?

Known issue, fixed in FB2. You need to merge your WHERE predicate with the
ON clause of the inner join to have good performance in v1.5.

> Query 2:
>
> SELECT
>    entry.id, acct.id, trans.id
> FROM
>    entry
> JOIN
>    acct  ON acct.id=entry.a1_id
> LEFT OUTER JOIN
>    trans ON trans.id=entry.t_id
> WHERE
>    trans.id=1
>
> PLAN JOIN (JOIN (ACCT NATURAL,ENTRY INDEX (FK_ENTRY_ACCT1)),TRANS INDEX
> (PK_TRANS))
>
> Same as above, but for table TRANS.

This is correct. Left join dictates a definite join order, table TRANS can
be only last joined table in your example. It doesn't make any difference in
this case, whether index PK_TRANS is used to check ID=1 for every row or to
evaluate the join condition.

> Query 3:
>
> SELECT
>    entry.id, acct.id, trans.id
> FROM
>    entry
> JOIN
>    acct  ON acct.id=entry.a1_id
> LEFT OUTER JOIN
>    trans ON trans.id=entry.t_id
> WHERE
>    entry.e1=3.14
>
> PLAN JOIN (JOIN (ACCT NATURAL,ENTRY INDEX
> (FK_ENTRY_ACCT1,I_ENTRY_E1)),TRANS INDEX (PK_TRANS))
>
> Why scan ACCT when there's an index on ENTRY.E1?

Same as query 1.


Dmitry