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: Join order in v3 is less optimal than in v2.x [CORE4702] #5010

Closed
firebird-automations opened this issue Mar 3, 2015 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @sim1984

CREATE TABLE TRIAL (
CODE_TRIAL INTEGER NOT NULL,
CODE_PRIZE INTEGER NOT NULL,
BYDATE DATE
);

CREATE TABLE PRIZE (
CODE_PRIZE INTEGER NOT NULL,
NAME VARCHAR(70) NOT NULL
);

CREATE TABLE TRIAL_LINE (
CODE_TRIAL_LINE INTEGER NOT NULL,
CODE_TRIAL INTEGER NOT NULL
);

INSERT INTO PRIZE(CODE_PRIZE, NAME)
WITH RECURSIVE T (N) AS (
SELECT 1 FROM RDB$DATABASE
UNION ALL
SELECT N+1 FROM T WHERE N < 1000
)
SELECT N+8000, '' FROM T;

COMMIT;

INSERT INTO TRIAL(CODE_TRIAL, CODE_PRIZE, BYDATE)
WITH RECURSIVE T (N) AS (
SELECT 1 FROM RDB$DATABASE
UNION ALL
SELECT N+1 FROM T WHERE N < 1000
)
SELECT T1.N + (T2.N-1)*1000, MOD(T1.N, 20)+8001, DATEADD(T1.N DAY TO date '01.01.2000') FROM T T1, T T2 WHERE T1.N + (T2.N-1)*1000 < 100000;

COMMIT;

INSERT INTO TRIAL_LINE(CODE_TRIAL_LINE, CODE_TRIAL)
WITH RECURSIVE T (N) AS (
SELECT 1 FROM RDB$DATABASE
UNION ALL
SELECT N+1 FROM T WHERE N < 1000
)
SELECT T1.N + (T2.N-1)*1000, MOD(T1.N + (T2.N-1)*1000, 99998)+1 FROM T T1, T T2 WHERE T1.N + (T2.N-1)*1000 < 150000;

COMMIT;

ALTER TABLE TRIAL ADD CONSTRAINT PK_TRIAL PRIMARY KEY (CODE_TRIAL);
ALTER TABLE PRIZE ADD CONSTRAINT PK_PRIZE PRIMARY KEY (CODE_PRIZE);
ALTER TABLE TRIAL_LINE ADD CONSTRAINT PK_TRIAL_LINE PRIMARY KEY (CODE_TRIAL_LINE);

ALTER TABLE TRIAL_LINE ADD CONSTRAINT FK_TRIAL_LINE_TRIAL FOREIGN KEY (CODE_TRIAL) REFERENCES TRIAL (CODE_TRIAL);
ALTER TABLE TRIAL ADD CONSTRAINT FK_TRIAL_PRIZE FOREIGN KEY (CODE_PRIZE) REFERENCES PRIZE (CODE_PRIZE);

CREATE INDEX IDX_BYDATE ON TRIAL(BYDATE);

SELECT count(*)
FROM
TRIAL
JOIN PRIZE ON PRIZE.CODE_PRIZE = TRIAL.CODE_PRIZE
JOIN TRIAL_LINE ON TRIAL_LINE.CODE_TRIAL = TRIAL.CODE_TRIAL
WHERE TRIAL.BYDATE between date '01.01.2000' AND date '31.12.2000';

In Firebird 2.5

PLAN JOIN (TRIAL INDEX (IDX_BYDATE), PRIZE INDEX (PK_PRIZE), TRIAL_LINE INDEX (FK_TRIAL_LINE_TRIAL))

   COUNT

============
54751

Current memory = 138716136
Delta memory = 24
Max memory = 138766504
Elapsed time= 0.33 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 438079

In Firebird 3.0

PLAN JOIN (TRIAL INDEX (IDX_BYDATE), TRIAL_LINE INDEX (FK_TRIAL_LINE_TRIAL), PRIZE INDEX (PK_PRIZE))

            COUNT

=====================
54751

Current memory = 145145264
Delta memory = 0
Max memory = 145198152
Elapsed time= 0.432 sec
Buffers = 16384
Reads = 0
Writes = 0
Fetches = 511076

Commits: a14e993 FirebirdSQL/fbt-repository@0c21645

====== Test Details ======

Confirmed ineffective plan in WI-T3.0.0.31374 Firebird 3.0 Beta 1:
PLAN JOIN (TRIAL INDEX (IDX_BYDATE), TRIAL_LINE INDEX (FK_TRIAL_LINE_TRIAL), PRIZE INDEX (PK_PRIZE))

@firebird-automations
Copy link
Collaborator Author

Modified by: @sim1984

environment: WI-V2.5.3.26747 Firebird 2.5/tcp (station9)/P12
WI-T3.0.0.31687 Firebird 3.0 Beta 2/tcp (station9)/P13:C

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Regression: 3.0 Beta 1 [ 10332 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Title of this ticket make it appears the bug is in FB 2.X.

Didn't you mean instead: "The order for joining the tables is less optimal than in Firebird 2.x" ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @sim1984

The optimizer gives the plan with bigger cost. Order of joining of tables is the reason. Excuse not my English native language.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Regression: The order for joining the tables is less than optimal in Firebird 2.x. => Regression: Join order in v3 is less optimal than in v2.x

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

Test Details: Confirmed ineffective plan in WI-T3.0.0.31374 Firebird 3.0 Beta 1:
PLAN JOIN (TRIAL INDEX (IDX_BYDATE), TRIAL_LINE INDEX (FK_TRIAL_LINE_TRIAL), PRIZE INDEX (PK_PRIZE))

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