Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Regression: Non-indexed predicates may not be applied immediately after retrieval when tables are being joined [CORE5018] #5306

Closed
firebird-issue-importer opened this issue Nov 16, 2015 · 10 comments

Comments

@firebird-issue-importer

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 16, 2015

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
May be this is helpfull in finding the solution, running next query gives back the FB2.5 behaviour:

SELECT
COUNT(*)
FROM
ZF
JOIN U ON http://ZF.ID=U.ID and ZF.KONT_ID<>U.KONT_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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 16, 2015

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 16, 2015

Commented by: @dyemanov

Arno, good catch. Should be easy enough to fix.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 16, 2015

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 16, 2015

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))
PLAN JOIN (JOIN (JOIN (ZF NATURAL, U INDEX (RDB$PRIMARY3)), KZF INDEX (RDB$PRIMARY4)), KUM INDEX (RDB$PRIMARY4))

WI-V2.5.3.26738 Firebird 2.5

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 16, 2015

Commented by: @dyemanov

Already nailed it down, will be fixed soon.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 16, 2015

Modified by: @dyemanov

summary: [Regression] Too many reads FB3 vs FB2.5 => Regression: Non-indexed predicates may not be applied immediately after retrieval when tables are being joined

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 16, 2015

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC2 [ 10048 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 17, 2015

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 17, 2015

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants