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
Comments
Modified by: @sim1984environment: WI-V2.5.3.26747 Firebird 2.5/tcp (station9)/P12 |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovRegression: 3.0 Beta 1 [ 10332 ] |
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" ? |
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. |
Modified by: @dyemanovsummary: 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 |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 Beta 2 [ 10586 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: Done successfully Test Details: Confirmed ineffective plan in WI-T3.0.0.31374 Firebird 3.0 Beta 1: |
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))
============
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))
=====================
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))
The text was updated successfully, but these errors were encountered: