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
Comments
Commented by: Omacht András (aomacht) Hi Karel, have you tried |
Commented by: Karel Rys (vandrovnik) Yes, I have - it is also very slow, there is no difference. |
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. |
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 It seems that only the "partial mach" is problematic. |
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: |
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 |
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) 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... |
Commented by: Karel Rys (vandrovnik) And this: with :Pobocka=1, :Kod=NULL, :Radek=NULL works fast Plan Select Expression |
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. |
Commented by: Karel Rys (vandrovnik) SELECT a.Pobocka, a.Kod, a.Radek FROM Objednavky2 a WHERE (a.Pobocka=:Pobocka)and(a.Kod=:Kod) Plan is the same Select Expression for both 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. |
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,
......
);
The text was updated successfully, but these errors were encountered: