Very fast LEFT JOIN, very slow INNER JOIN

[Tomasz Dubiel]

Hello.

Can you explain to me why in this situation LEFT JOIN is MUCH, MUCH faster and reads MUCH, MUCH less data than INNER JOIN? Firebird 3.0.10.

If its about the rules of the optimizer, I need to understand them, or when it is the bug, I would need a confirmation.

select BI.ID_BOM, BI.ID_BOMITEM_MAIN, BI.ID_BOMITEM, BI.ID_KARTOTEKA, BI.ID_FANTOM, BI.LP, BI.INDEKS, BI.NAZWA,
       BI.RODZAJMAT, BI.ILOSC, BI.WARTOSC, BI.NARZUT, BI.RODZAJCENY, BI.AKTYWNY, BI.ID_MAGAZYN, BI.NAZWAMAG, BI.RODZAJ,
       BI.ROZLICZBEZDOK, BI.KARTOTEKA, BI.POZIOM, BI.CENA, BI.NAZWASKR, BI.WARIANTOWY, BI.OPCJONALNY, BI.OPCJAWYBRANA,
       BI.ID_WYBRANYWARIANT, BI.WYBRANYWARIANT, BI.NIEWIDOCZNY, OT.ID_OPERACJATECH, OT.ID_TECHNOLOGIA, OT.LP as LPOPER,
       OSL.ID_OPERACJASL, OSL.KOD as KODOPER, OSL.NAZWA as NAZWAOPER
from OPERACJATECH OT
inner join OPERACJASL OSL on (OT.ID_OPERACJASL = OSL.ID_OPERACJASL)
left join BOMITEMOPERACJATECH_VIEW BI on (BI.ID_OPERACJATECH = OT.ID_OPERACJATECH)
where ((OT.ID_TECHNOLOGIA = 17373) and
      (BI.RODZAJ = 0) and
      (BI.NIEWIDOCZNY = 0))
order by OT.LP, BI.LP, BI.ID_BOM

PLAN (BI NATURAL)
PLAN (BI NATURAL)
PLAN SORT (JOIN (JOIN (OT INDEX (FK_OPERACJATECH_TECHNOLOGIA), OSL INDEX (PK_OPERACJASL)), JOIN (JOIN (JOIN (BI OTB INDEX (FK_OPERACJATECHBOM_OPERACJATECH), BI BI INDEX (FK_BOMITEM_BOM)), BI K INDEX (PK_KARTOTEKA)), BI M INDEX (PK_MAGAZYN)), JOIN (JOIN (JOIN (BI OTB INDEX (FK_OPERACJATECHBOM_OPERACJATECH), BI BI INDEX (FK_BOMITEM_BOM)), BI F INDEX (PK_FANTOM)), BI M INDEX (PK_MAGAZYN))))

Just a couple of indexed reads from 6 tables!!! and 0 ms SQL execution!!!

And then with INNER JOIN (BOMITEMOPERACJATECH_VIEW):

Completely different plan:
PLAN (BI NATURAL)
PLAN (BI NATURAL)
PLAN SORT (JOIN (JOIN (JOIN (JOIN (BI OTB NATURAL, BI BI INDEX (FK_BOMITEM_BOM), BI K INDEX (PK_KARTOTEKA)), BI M INDEX (PK_MAGAZYN)), JOIN (JOIN (BI OTB NATURAL, BI BI INDEX (FK_BOMITEM_BOM), BI F INDEX (PK_FANTOM)), BI M INDEX (PK_MAGAZYN)), OT INDEX (PK_OPERACJATECH)), OSL INDEX (PK_OPERACJASL)))

and read data(!!!!!!!!):

SQL execution is 18 seconds!!!

DDL of the view:

select
  OTB.Id_OperacjaTech, BI.ID_BOMITEM, BI.ID_BOM, BI.ID_BOMITEM_MAIN, K.ID_KARTOTEKA, null, BI.Rodzaj, BI.Lptree,
  K.INDEKS, K.NAZWADL, K.NAZWASKR, BI.RODZAJMAT, BI.ILOSC, BI.WARTOSC, BI.NARZUT, BI.RODZAJCENY,
  BI.AKTYWNY, BI.WARIANTOWY, BI.OPCJONALNY, BI.OPCJAWYBRANA, BI.ROZLICZBEZDOK, BI.KARTOTEKA,
  M.ID_MAGAZYN, M.NAZWAMAG, BI.POZIOM, BI.ID_BOMITEMWYBRANY, BI.WYBRANYWARIANT, BI.NIEWIDOCZNY, BI.UZGODNIONY, BI.CENA
from OPERACJATECHBOM OTB
  join BOMITEM BI  on (BI.ID_BOM = OTB.ID_BOM)
  join KARTOTEKA K on (K.ID_KARTOTEKA = BI.ID_KARTOTEKA)
  left outer join MAGAZYN M on (M.ID_MAGAZYN = BI.ID_MAGAZYN)
union all
select
  OTB.Id_OperacjaTech, BI.ID_BOMITEM, BI.ID_BOM, BI.ID_BOMITEM_MAIN, null, F.Id_Fantom, BI.Rodzaj, BI.Lptree,
  F.INDEKS, F.Nazwa, F.Nazwa, BI.RODZAJMAT, BI.ILOSC, BI.WARTOSC, BI.NARZUT, BI.RODZAJCENY,
  BI.AKTYWNY, BI.WARIANTOWY, BI.OPCJONALNY, BI.OPCJAWYBRANA, BI.ROZLICZBEZDOK, BI.KARTOTEKA,
  M.ID_MAGAZYN, M.NAZWAMAG, BI.POZIOM, BI.ID_BOMITEMWYBRANY, BI.WYBRANYWARIANT, BI.NIEWIDOCZNY, BI.UZGODNIONY, BI.CENA
from OPERACJATECHBOM OTB
  join BOMITEM BI  on (BI.ID_BOM = OTB.ID_BOM)
  join FANTOM F on (F.Id_Fantom = BI.Id_Fantom)
  left outer join MAGAZYN M on (M.ID_MAGAZYN = BI.ID_MAGAZYN)

For now it is a strange thing for me.

Thanks.

[Dmitry Yemanov]

14.11.2022 15:24, Tomasz Dubiel wrote:
>
> Can you explain to me why in this situation LEFT JOIN is MUCH, MUCH
> faster and reads MUCH, MUCH less data than INNER JOIN? Firebird 3.0.10.
> If its about the rules of the optimizer, I need to understand them, or
> when it is the bug, I would need a confirmation.

It's a limitation of the optimizer when (non-trivial) views are involved
in the join.

With INNER JOIN, the join order is always started with the view, other
options are not considered. With LEFT JOIN, you define the join order
yourself and BI is placed at the end. For this particular query, this
order is obviously faster.


Dmitry

[Tomasz Dubiel]

Thank you.