Issue Details (XML | Word | Printable)

Key: CORE-4702
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Minor Minor
Assignee: Dmitry Yemanov
Reporter: Simonov Denis
Votes: 0
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Regression: Join order in v3 is less optimal than in v2.x

Created: 03/Mar/15 09:52 AM   Updated: 29/May/15 04:08 PM
Component/s: Engine
Affects Version/s: 3.0 Beta 1
Fix Version/s: 3.0 Beta 2

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

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))


 Description  « Hide
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



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 03/Mar/15 12:02 PM
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" ?

Simonov Denis added a comment - 03/Mar/15 12:20 PM - edited
The optimizer gives the plan with bigger cost. Order of joining of tables is the reason. Excuse not my English native language.