FirebirdSQL
Non-Optimal plan for a left join c inner join b [CORE1239]
Submitted by: Jojakim Stahl (jstahl)
Is duplicated by CORE2937 Is duplicated by CORE2963
Votes: 4
In short: The plan for a left join c inner join b is worse than the plan for a inner join b left join c. Refer also to: http://tech.groups.yahoo.com/group/firebird-support/message/85749 (archive) which is a bit more complex than the reduced example here.
select * from t_a a left join t_c c on a.id=c.ta_id inner join t_b b on a.id=b.owner_id where b.member_id=2
results in a plan with a table scan for a: PLAN JOIN (JOIN (A NATURAL, C INDEX (PK_TC)), B INDEX (IX_TB_OWNER, IX_TB_MEMBER))
where the following select returns the same result but without table scan: select * from t_a a inner join t_b b on a.id=b.owner_id left join t_c c on a.id=c.ta_id where b.member_id=2
PLAN JOIN ( JOIN (B INDEX (IX_TB_MEMBER), A INDEX (PK_TA)), C INDEX (PK_TC))
Script for setting up test database: create table t_a ( id integer not null, uid integer not null );
create table t_b ( owner_id integer not null, member_id integer );
create table t_c ( ta_id integer not null, sub_id integer not null );
insert into t_a (id, uid) values (1, 100); insert into t_a (id, uid) values (2, 200); insert into t_a (id, uid) values (3, 300);
commit work;
insert into t_b (owner_id, member_id) values (1, 2); insert into t_b (owner_id, member_id) values (1, null); insert into t_b (owner_id, member_id) values (3, 1);
commit work;
insert into t_c (ta_id, sub_id) values (3, 1); insert into t_c (ta_id, sub_id) values (3, 2);
commit work;
alter table t_a add constraint pk_ta primary key (id); alter table t_c add constraint pk_tc primary key (ta_id, sub_id);
create index ix_ta_uid on t_a (uid); create index ix_tb_member on t_b (member_id); create index ix_tb_owner on t_b (owner_id);
====== Test Details ======
Deferred until changes in optimizer will be done. Test will be common for this ticket and core-2963
Modified by: @pcisar
Workflow: jira [ 12000 ] => Firebird [ 15558 ]
Modified by: @dyemanov
Fix Version: 3.0 Beta 1 [ 10332 ]
Fix Version: 3.0 Alpha 1 [ 10331 ] =>
Modified by: @dyemanov
Fix Version: 3.0 Beta 2 [ 10586 ]
Fix Version: 3.0 Beta 1 [ 10332 ] =>
Modified by: @pavel-zotov
status: Open [ 1 ] => Open [ 1 ]
QA Status: Deferred
Test Details: Deferred until changes in optimizer will be done. Test will be common for this ticket and core-2963