Regression: Non-indexed predicates may not be applied immediately after retrieval when tables are being joined [CORE5018] #5306
Comments
|
Commented by: @ArnoBrinkman With a quick test it seems the boolean expression "ZF.KONT_ID<>U.KONT_ID" is not delivered to all fitting streams SELECT |
|
Commented by: @dyemanov Please specify exact FB 2.5 version you compare with. Also, please post the plans. As far as I see, plans are the same in v2.5.5 and v3.0.0. |
|
Commented by: @dyemanov Arno, good catch. Should be easy enough to fix. |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
|
Commented by: @livius2 yes, plans are the same in both FB2.5 and FB3.0 PLAN JOIN (JOIN (JOIN (ZF NATURAL, U INDEX (RDB$PRIMARY6)), KZF INDEX (RDB$PRIMARY7)), KUM INDEX (RDB$PRIMARY7)) WI-V2.5.3.26738 Firebird 2.5 |
|
Commented by: @dyemanov Already nailed it down, will be fixed soon. |
Modified by: @dyemanovsummary: [Regression] Too many reads FB3 vs FB2.5 => Regression: Non-indexed predicates may not be applied immediately after retrieval when tables are being joined |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 RC2 [ 10048 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: No test => Done successfully |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: @livius2
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 http://ZF.ID=U.ID
LEFT JOIN K KZF ON ZF.KONT_ID=http://KZF.ID
LEFT JOIN K KUM ON U.KONT_ID=http://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
Commits: e6e9e45 FirebirdSQL/fbt-repository@8959292
The text was updated successfully, but these errors were encountered: