Badly selective index could be used for extra filtering even if selective index
is used for sorting
---------------------------------------------------------------------------------------------------
Key: CORE-5435
URL: http://tracker.firebirdsql.org/browse/CORE-5435
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.1, 3.0.0, 4.0 Initial
Reporter: Dmitry Yemanov
It seems that Firebird 3 is sometimes choosing the index with less selectivity,
which can have a serious
effect on performance. We have seen this behaviour in several queries.
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 2.1/2.5:
PLAN (TABLE1 ORDER TABLE1_F1_ID INDEX (TABLE1_F1_ID))
0.002 seconds
Firebird3:
PLAN (TABLE1 ORDER TABLE1_F1_ID INDEX (TABLE1_F2))
Select Expression
-> Filter
-> Table "TABLE1" Access By ID
-> Index "TABLE1_F1_ID" Range Scan (partial match: 1/2)
-> Bitmap
-> Index "TABLE1_F2" Range Scan (full match)
0.240 seconds
-
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
