Wrong plan selected in simple join
----------------------------------
Key: CORE-3707
URL: http://tracker.firebirdsql.org/browse/CORE-3707
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.1, 2.5.2
Environment: Windows 7
Reporter: Jesus Angel Garcia Zarco
I have created a simple database with three tables, extracted the model for one
of our production database. The problem with our production database is that
the bad plan makes one query to run in a big database so much time.
CREATE DATABASE 'localhost/3054:C:\BD\Firebird25\Prueba.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET NONE COLLATION NONE;
CREATE TABLE TABLE0 (
IDPETICION INTEGER NOT NULL
);
CREATE TABLE TABLE1 (
IDPETICIONPRUEBA INTEGER NOT NULL,
IDPETICION INTEGER
);
CREATE TABLE TABLE2 (
IDPETICIONPRUEBA INTEGER
);
ALTER TABLE TABLE0 ADD CONSTRAINT PK_TABLE0 PRIMARY KEY (IDPETICION);
ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (IDPETICIONPRUEBA);
ALTER TABLE TABLE1 ADD CONSTRAINT FK_TABLE1_1 FOREIGN KEY (IDPETICION)
REFERENCES TABLE0 (IDPETICION);
ALTER TABLE TABLE2 ADD CONSTRAINT FK_TABLE2_1 FOREIGN KEY (IDPETICIONPRUEBA)
REFERENCES TABLE1 (IDPETICIONPRUEBA);
if i run
select t1.IDPETICIONPRUEBA
from table1 t1 join table2 t2 on (t1.IDPETICIONPRUEBA = t2.IDPETICIONPRUEBA )
where t1.IDPETICION = 1
the plan is PLAN JOIN (T2 NATURAL, T1 INDEX (PK_TABLE1))
With this model of tables, firebird allways select the bad plan.
--
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
------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
