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

Full outer join cannot use available indices (very slow execution) [CORE2678] #3081

Closed
firebird-issue-importer opened this issue Oct 13, 2009 · 4 comments

Comments

@firebird-issue-importer

Submitted by: Gustavo Torres (gusta1308)

I try run this query:
SELECT
D1.C1, D2.C1,
D1.C2, D2.C2,
D1.C3, D2.C3,
COALESCE(SUM(D1.D1), 0) T1,
COALESCE(SUM(D2.D2), 0) T2
FROM TD_DATA1 D1
FULL JOIN TD_DATA2 D2
ON D2.C1 = D1.C1
AND D2.C2 = D1.C2
AND D2.C3 = D1.C3
GROUP BY D1.C1, D2.C1,
D1.C2, D2.C2,
D1.C3, D2.C3

and never return, both tables contain 100,000 rows and your structure is:

CREATE TABLE TD_DATA1 (
C1 VARCHAR(20) CHARACTER SET WIN1251 NOT NULL COLLATE WIN1251,
C2 INTEGER NOT NULL,
C3 DATE NOT NULL,
D1 FLOAT NOT NULL);

CREATE INDEX IDX_TD_DATA1 ON TD_DATA1(C1,C2,C3);

CREATE TABLE TD_DATA2 (
C1 VARCHAR(20) CHARACTER SET WIN1251 NOT NULL COLLATE WIN1251,
C2 INTEGER NOT NULL,
C3 DATE NOT NULL,
D2 FLOAT NOT NULL);

CREATE INDEX IDX_TD_DATA2 ON TD_DATA2(C1,C2,C3);

Thank you for advance

Commits: 11495a5

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 14, 2010

Modified by: @dyemanov

Version: 3.0 Initial [ 10301 ]

Version: 1.5.6 [ 10225 ]

Version: 2.1.3 [ 10302 ]

Version: 2.5 Beta 1 [ 10251 ]

Version: 2.1.2 [ 10270 ]

Version: 2.0.5 [ 10222 ]

Version: 2.1.1 [ 10223 ]

Version: 2.5 Alpha 1 [ 10224 ]

Version: 2.0.4 [ 10211 ]

Version: 2.1.0 [ 10041 ]

Version: 1.5.5 [ 10220 ]

Version: 2.0.3 [ 10200 ]

Version: 2.0.2 [ 10130 ]

Version: 2.0.1 [ 10090 ]

Version: 1.5.4 [ 10100 ]

Version: 2.0.0 [ 10091 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

assignee: Dmitry Yemanov [ dimitr ]

summary: FULL JOIN FB 2.5 Beta 2 25267 => Full outer join cannot use available indices (very slow execution)

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 14, 2010

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 14, 2011

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 28, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

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