Bad performance for NULLs filtering inside a navigational index scan
--------------------------------------------------------------------
Key: CORE-5489
URL: http://tracker.firebirdsql.org/browse/CORE-5489
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.1, 3.0.0
Reporter: Paquito Ines
Attachments: Databases.zip
When filtering and ordering a query by the same field, Firebird 3 is capable of
doing the filtering directly inside the navigational index, but this seems to
not be working well if the filtering condition is "is null". In this case, the
performance is similar to not using the index. However, the performance is the
expected if the filtering condition is "=0", though both queries have the same
plan.
This must be tested with Firebird-3.0.2.32664 (nightly build), after correction
of CORE-5435. It can also be tested with Firebird 3.0.1 eliminating the
"FIELD2=0" condition.
In this example (we have attached in databases.zip the databases for Firebird
2.5 and Firebird 3):
-Table1 has around 300000 records.
-Field1 has 15000 different values with 20 records each one.
-Field2 has 2 different values (0 with 200000 records, and 1 with 100000
records).
-Both databases were tested immediately after a backup/restore cycle and in
the same computer.
-The times are measured in the second execution of each query (though the
first execution follow the same pattern).
Query:
select *
from TABLE1 where FIELD1 is null and FIELD2=0
order by FIELD1, ID
Firebird-3.0.2.32664-0_x64 (nightly build):
select *
from TABLE1 where FIELD1 is null and FIELD2=0
order by FIELD1, ID
PLAN (TABLE1 ORDER TABLE1_F1_ID)
Select Expression
-> Filter
-> Table "TABLE1" Access By ID
-> Index "TABLE1_F1_ID" Range Scan (partial match: 1/2)
0.290 seconds
Firebird-3.0.2.32664-0_x64 (nightly build):
select *
from TABLE1 where FIELD1=0 and FIELD2=0
order by FIELD1, ID;
PLAN (TABLE1 ORDER TABLE1_F1_ID)
Select Expression
-> Filter
-> Table "TABLE1" Access By ID
-> Index "TABLE1_F1_ID" Range Scan (partial match: 1/2)
0.002 s
However, in Firebird 2.5, both "FIELD1 is null" and "FIELD1=0" queries have
same plan and same timing:
PLAN (TABLE1 ORDER TABLE1_F1_ID INDEX (TABLE1_F1_ID))
0.002 s
-
CREATE TABLE "TABLE1"
(
"ID" INTEGER NOT NULL,
"FIELD1" INTEGER,
"FIELD2" INTEGER,
CONSTRAINT "TABLE1_PK" PRIMARY KEY ("ID")
);
CREATE INDEX "TABLE1_F1_ID" ON "TABLE1"("FIELD1", "ID");
CREATE INDEX "TABLE1_F2" ON "TABLE1"("FIELD2");
--
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
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
