[Regression] Too many reads FB3 vs FB2.5
----------------------------------------
Key: CORE-5018
URL: http://tracker.firebirdsql.org/browse/CORE-5018
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 RC 1
Reporter: Karol Bieniaszewski
Simplified case
CREATE TABLE ZF
(
ID INTEGER NOT NULL PRIMARY KEY,
KONT_ID INTEGER NOT NULL
);
CREATE TABLE U
(
ID INTEGER NOT NULL PRIMARY KEY,
KONT_ID INTEGER NOT NULL
);
CREATE TABLE K
(
ID INTEGER NOT NULL PRIMARY KEY
);
commit;
INSERT INTO ZF (ID, KONT_ID) VALUES ('1', '1');
INSERT INTO ZF (ID, KONT_ID) VALUES ('2', '7');
INSERT INTO ZF (ID, KONT_ID) VALUES ('3', '3');
INSERT INTO ZF (ID, KONT_ID) VALUES ('4', '5');
INSERT INTO ZF (ID, KONT_ID) VALUES ('5', '5');
INSERT INTO ZF (ID, KONT_ID) VALUES ('6', '1');
INSERT INTO ZF (ID, KONT_ID) VALUES ('7', '4');
INSERT INTO ZF (ID, KONT_ID) VALUES ('8', '2');
INSERT INTO ZF (ID, KONT_ID) VALUES ('9', '9');
INSERT INTO ZF (ID, KONT_ID) VALUES ('10', '1');
INSERT INTO K (ID) VALUES ('1');
INSERT INTO K (ID) VALUES ('2');
INSERT INTO K (ID) VALUES ('3');
INSERT INTO K (ID) VALUES ('4');
INSERT INTO K (ID) VALUES ('5');
INSERT INTO K (ID) VALUES ('6');
INSERT INTO K (ID) VALUES ('7');
INSERT INTO K (ID) VALUES ('8');
INSERT INTO K (ID) VALUES ('9');
INSERT INTO K (ID) VALUES ('10');
INSERT INTO U (ID, KONT_ID) VALUES ('1', '4');
INSERT INTO U (ID, KONT_ID) VALUES ('2', '6');
INSERT INTO U (ID, KONT_ID) VALUES ('3', '3');
INSERT INTO U (ID, KONT_ID) VALUES ('4', '2');
INSERT INTO U (ID, KONT_ID) VALUES ('5', '5');
INSERT INTO U (ID, KONT_ID) VALUES ('6', '2');
INSERT INTO U (ID, KONT_ID) VALUES ('7', '9');
INSERT INTO U (ID, KONT_ID) VALUES ('8', '2');
INSERT INTO U (ID, KONT_ID) VALUES ('9', '10');
INSERT INTO U (ID, KONT_ID) VALUES ('10', '1');
commit;
SELECT
COUNT(*)
FROM
ZF
INNER JOIN U ON ZF.ID=U.ID
LEFT JOIN K KZF ON ZF.KONT_ID=KZF.ID
LEFT JOIN K KUM ON U.KONT_ID=KUM.ID
WHERE
ZF.KONT_ID<>U.KONT_ID
in FB2.5
ZF 10 seq reads
U 10 idx reads
K 12 idx reads
in FB3.0RC1
ZF 10 seq reads
U 10 idx reads
K 20 idx reads
in real case it affects performance FB2.5 - 2.81s vs FB3.0RC1 - 13.017s
--
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
------------------------------------------------------------------------------
Presto, an open source distributed SQL query engine for big data, initially
developed by Facebook, enables you to easily query your data on Hadoop in a
more interactive manner. Teradata is also now providing full enterprise
support for Presto. Download a free open source copy now.
http://pubads.g.doubleclick.net/gampad/clk?id=250295911&iu=/4140
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
