Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Index not used when search criteria is NULL in FB 3.0 RC 2 [CORE5196] #5477

Closed
firebird-automations opened this issue Apr 14, 2016 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Karel Rys (vandrovnik)

SELECT a.Pobocka, a.Kod, a.Radek FROM Objednavky2 a WHERE (a.Pobocka=:Pobocka)and(a.Kod=:Kod)

In table Objednavky2, there are +- 11 milions of records.
When parameters :Pobocka and :Kod are both supplied, query runs fast (takes a few miliseconds).
When parameter :Pobocka is supplied, but :Kod is NULL, query runs extremly slowly (takes more than 10 minutes!) with lots of disc activity.

Reported plan (:Pobocka=1, :Kod=NULL):
PLAN (A ORDER OBJEDNAVKY2_KLIC)
Select Expression
-> Filter
-> Table "OBJEDNAVKY2" as "A" Access By ID
-> Index "OBJEDNAVKY2_KLIC" Range Scan (partial match: 2/3)

Reported plan (:Pobocka=1, :Kod=1):
PLAN (A ORDER OBJEDNAVKY2_KLIC)
Select Expression
-> Filter
-> Table "OBJEDNAVKY2" as "A" Access By ID
-> Index "OBJEDNAVKY2_KLIC" Range Scan (partial match: 2/3)

The same query with Firebird 2.5.5 (:Pobocka=1, :Kod=NULL):
PLAN (A ORDER OBJEDNAVKY2_KLIC INDEX (OBJEDNAVKY2_OBJEDNAVKY1))

CREATE TABLE Objednavky2
(
Pobocka smallint NOT NULL,
Kod integer NOT NULL,
Radek smallint NOT NULL,
......
CONSTRAINT Objednavky2_Klic PRIMARY KEY (Pobocka,Kod,Radek),
CONSTRAINT Objednavky2_Objednavky1 FOREIGN KEY (Pobocka,Kod) REFERENCES Objednavky1 (Pobocka,Kod) ON DELETE CASCADE,
......
);

@firebird-automations
Copy link
Collaborator Author

Commented by: Omacht András (aomacht)

Hi Karel,

have you tried
SELECT a.Pobocka, a.Kod, a.Radek FROM Objednavky2 a WHERE (a.Pobocka is not distinct from :Pobocka) and (a.Kod is not distinct from :Kod)
?

@firebird-automations
Copy link
Collaborator Author

Commented by: Karel Rys (vandrovnik)

Yes, I have - it is also very slow, there is no difference.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

What is the *actual* query? Select that you provided cannot lead to the ORDER plan. There must be GROUP BY or ORDER BY clause that you're hiding from us.

@firebird-automations
Copy link
Collaborator Author

Commented by: Karel Rys (vandrovnik)

Another piece of information: when I use this:

SELECT a.Pobocka, a.Kod, a.Radek FROM Objednavky2 a WHERE (a.Pobocka=:Pobocka)and(a.Kod=:Kod)and(a.Radek=:Radek)

it works fast, even for :Pobocka=1, :Kod=NULL, :Radek=NULL

PLAN (A INDEX (OBJEDNAVKY2_KLIC))

Select Expression
-> Filter
-> Table "OBJEDNAVKY2" as "A" Access By ID
-> Bitmap
-> Index "OBJEDNAVKY2_KLIC" Unique Scan

It seems that only the "partial mach" is problematic.

@firebird-automations
Copy link
Collaborator Author

Commented by: Karel Rys (vandrovnik)

Problem really manifested itself with:

SELECT a.Pobocka, a.Kod, a.Radek FROM Objednavky2 a WHERE (a.Pobocka=:Pobocka)and(a.Kod=:Kod)

Nothing else.

Original query was:
SELECT
a.Pobocka,
a.Kod,
a.Radek,
a.Zapsano,
a.UzivatelPobocka,
a.UzivatelKod,
a.SystemovaPolozka,
a.ZboziObjednane,
a.ZboziNemusiBytDodano,
a.Mnozstvi,
a.Zdarma,
a.CenaZadana,
a.CenaStandardniZadana,
a.CenaStandardni,
a.Medium,
h.Ikona as MediumIkona,
a.ZboziNahradni,
a.NahradyPovoleny,
a.Cena,
c.CenaZakladni,
a.Cena*a.Mnozstvi as CenaCelkem,
a.Poznamka,
a.BalicekEshop,
CAST(:PotlacitVypocty as dBoolean) as PotlacitVypocty,
COALESCE(c.NazevNarodni, b.NazevBotanicky) as Nazev,
b.CisloZbozi as ZboziObjednaneCislo,
b.Sada,
c.Velikost,
COALESCE(p2.KontejnerZakaznik, p.KontejnerZakaznik) as Kontejner,
COALESCE(r2.Specifikace, r.Specifikace) as Specifikace1,
e.CisloZbozi as ZboziNahradniCislo,
b.IdZbozi as ZboziObjednaneId,
e.IdZbozi as ZboziNahradniId,
CASE WHEN (n.HledatZboziPodleId='y') THEN COALESCE(b.IdZbozi, q.CisloZbozi) ELSE q.CisloZbozi END as ZboziObjednaneIdNeboCislo,
CASE WHEN (n.HledatZboziPodleId='y') THEN COALESCE(e.IdZbozi, e.CisloZbozi) ELSE e.CisloZbozi END as ZboziNahradniIdNeboCislo,
c.DoplnekNazvu,
c.DoplnujiciText,
c.NeskladnaZasilka,
c.PilnaZasilka,
COALESCE(d2.Zkratka, d.Zkratka) as JednotkaZakaznik,
COALESCE(f.NazevNarodni, e.NazevBotanicky) as NazevNahradni,
c.DodavaSe,
c.Vyprodano,
c.PredbezneVyprodano,
c.ExpedicePotlacena,
c.ExpediceOd,
g.Zbozi as StdNahradniZbozi,
i.ZasobaExistuje,
j.DodObjednavkyExistuji,
w.Cislo as ProJazyk,
u.LoginName as Uzivatel2,
CASE WHEN (EXISTS (SELECT * FROM Reklamace2 x WHERE (x.ObjednavkaPobocka=a.Pobocka)and(x.ObjednavkaKod=a.Kod)and(x.ObjednavkaRadek=a.Radek)))
or (EXISTS (SELECT * FROM Reklamace3 x WHERE (x.ObjednavkaPobocka=a.Pobocka)and(x.ObjednavkaKod=a.Kod)and(x.ObjednavkaRadek=a.Radek))) THEN 'y' ELSE 'n' END as ZReklamace,
COALESCE(c.TerminDodani, b.TerminDodani) as TerminDodani,
m.Barva as TerminDodaniBarva,
COALESCE (m2.TerminDodani, m.TerminDodani) as TerminDodani2,
o.BudeKDispozici,
o.Rezervovat,
o.ObjednaneSamostatne+
COALESCE(CASE WHEN b.Sada='y'
THEN NULL
ELSE (SELECT
SUM(CASE WHEN x.MnZbyvaDodat>=0 THEN x.MnZbyvaDodat ELSE 0 END)
FROM VObjednavky2 x
LEFT OUTER JOIN VObjednavky1 y on (y.Pobocka=x.Pobocka)and(y.Kod=x.Kod)
WHERE (x.Zbozi=a.ZboziObjednane)and(x.Sklad=k.Sklad)and(y.Sezona=k.Sezona)
)
END, 0) as ObjednaneSamostatne,
o.ObjednaneVSadach,
o.ZasobaKDispozici,
o.RadekSady
FROM Objednavky2 a
LEFT OUTER JOIN Zbozi b on (b.Kod=a.ZboziObjednane)
LEFT OUTER JOIN Zbozi2 c on (c.Kod=a.ZboziObjednane)and(c.Pobocka=a.Pobocka)
LEFT OUTER JOIN Jednotky d on (d.Kod=c.JednotkaZakaznik)
LEFT OUTER JOIN JednotkyTrn d2 on (d2.Kod=c.JednotkaZakaznik)and(d2.ProJazyk=:ProJazyk)
LEFT OUTER JOIN Zbozi e on (e.Kod=a.ZboziNahradni)
LEFT OUTER JOIN Zbozi2 f on (f.Kod=a.ZboziNahradni)and(f.Pobocka=a.Pobocka)
LEFT OUTER JOIN NahradniZbozi g on (g.Kod=a.ZboziObjednane)and(g.Pobocka=a.Pobocka)
LEFT OUTER JOIN Media h on (h.Kod=a.Medium)
LEFT OUTER JOIN Objednavky1 k on (k.Pobocka=a.Pobocka)and(k.Kod=a.Kod)
LEFT OUTER JOIN ZasobaExistuje (a.ZboziObjednane, k.Sklad, k.Sezona) i on (1=1)
LEFT OUTER JOIN DodObjednavkyExistuji (a.ZboziObjednane, k.Sklad) j on (1=1)
LEFT OUTER JOIN TerminyDodani m on (m.Kod=COALESCE(c.TerminDodani, b.TerminDodani))
LEFT OUTER JOIN TerminyDodaniTrn m2 on (m2.Kod=COALESCE(c.TerminDodani, b.TerminDodani))and(m2.ProJazyk=:ProJazyk)
LEFT OUTER JOIN PobockyParam n on (n.Kod=a.Pobocka)
LEFT OUTER JOIN Objednavky2_ZboziBudeKDispozici (a.Pobocka, a.Kod, a.Radek, :PotlacitVypocty) o on (1=1)
LEFT OUTER JOIN Kontejnery p on (p.Kod=c.Kontejner)
LEFT OUTER JOIN KontejneryTrn p2 on (p2.Kod=c.Kontejner)and(p2.ProJazyk=:ProJazyk)
LEFT OUTER JOIN DejCisloZbozi (a.Pobocka, b.CisloZbozi) q on (1=1)
LEFT OUTER JOIN SpecifikaceNarodni1 r on (r.Kod=c.Specifikace1)
LEFT OUTER JOIN SpecifikaceNarodni1Trn r2 on (r2.Kod=c.Specifikace1)and(r2.ProJazyk=:ProJazyk)
LEFT OUTER JOIN Uzivatele u on (u.Pobocka=a.UzivatelPobocka)and(u.Kod=a.UzivatelKod)
LEFT OUTER JOIN DejSmallint (:ProJazyk) w on (1=1)
WHERE (a.Pobocka=:Pobocka)and(a.Kod=:Kod)
ORDER BY a.Pobocka, a.Kod, a.Radek

@firebird-automations
Copy link
Collaborator Author

Commented by: Karel Rys (vandrovnik)

Well, and when I try it now once again, FB 3.0 uses another plan (the same as Firebird 2.5.5 uses):

SELECT a.Pobocka, a.Kod, a.Radek FROM Objednavky2 a WHERE (a.Pobocka=:Pobocka)and(a.Kod=:Kod)

PLAN (A INDEX (OBJEDNAVKY2_OBJEDNAVKY1)

Select Expression
-> Filter
-> Table "OBJEDNAVKY2" as "A" Access By ID
-> Bitmap
-> Index

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

At the first glance, it looks like a duplicate for CORE5070.

@firebird-automations
Copy link
Collaborator Author

Commented by: Karel Rys (vandrovnik)

I am sorry, my mistake, you are right - there was an "ORDER BY" a few empty lines down, I had to scroll.

Problematic query is:

SELECT a.Pobocka, a.Kod, a.Radek FROM Objednavky2 a WHERE (a.Pobocka=:Pobocka)and(a.Kod=:Kod)
ORDER BY a.Pobocka, a.Kod, a.Radek

Is there any chance that optimizer will work as it used in 2.5.5? In this case it means to use index also for filtering, not only for sorting...

@firebird-automations
Copy link
Collaborator Author

Commented by: Karel Rys (vandrovnik)

And this:
SELECT a.Pobocka, a.Kod, a.Radek FROM Objednavky2 a WHERE (a.Pobocka=:Pobocka)and(a.Kod=:Kod)and(a.Radek=:Radek)
ORDER BY a.Pobocka, a.Kod, a.Radek

with :Pobocka=1, :Kod=NULL, :Radek=NULL works fast

Plan
PLAN (A ORDER OBJEDNAVKY2_KLIC)

Select Expression
-> Filter
-> Table "OBJEDNAVKY2" as "A" Access By ID
-> Index "OBJEDNAVKY2_KLIC" Unique Scan

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Plan shows that both "Pobocka" and "Kod" are actually used for filtering. We'll likely find some workaround, but only for some v3.0.x post-release.

@firebird-automations
Copy link
Collaborator Author

Commented by: Karel Rys (vandrovnik)

SELECT a.Pobocka, a.Kod, a.Radek FROM Objednavky2 a WHERE (a.Pobocka=:Pobocka)and(a.Kod=:Kod)
ORDER BY a.Pobocka, a.Kod, a.Radek

Plan is the same
PLAN (A ORDER OBJEDNAVKY2_KLIC)

Select Expression
-> Filter
-> Table "OBJEDNAVKY2" as "A" Access By ID
-> Index "OBJEDNAVKY2_KLIC" Range Scan (partial match: 2/3)

for both
:Pobocka=1, :Kod=NULL
and
:Pobocka=1, :Kod=-1

But with :Kod=NULL, query runs more then 10 minutes, while with :Kod=-1, query runs just a few miliseconds.

Of course I can use a workaround here: WHERE (a.Pobocka=:Pobocka)and(a.Kod=COALESCE(:Kod, -1)), but in existing applications it might not be possible.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants