Index not used when search criteria is NULL in FB 3.0 RC 2
----------------------------------------------------------
Key: CORE-5196
URL: http://tracker.firebirdsql.org/browse/CORE-5196
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 RC2
Environment: Windows 7
Reporter: Karel Rys
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,
......
);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial!
https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
