Regression: The order for joining the tables is less than optimal in Firebird
2.x.
----------------------------------------------------------------------------------
Key: CORE-4702
URL: http://tracker.firebirdsql.org/browse/CORE-4702
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 Beta 1
Reporter: Simonov Denis
Priority: Minor
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
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
