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
Non-Optimal plan for a left join c inner join b [CORE1239] #1663
Comments
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @pcisarWorkflow: jira [ 12000 ] => Firebird [ 15558 ] |
Modified by: @dyemanovFix Version: 3.0 Alpha 1 [ 10331 ] |
Modified by: @dyemanovFix Version: 3.0 Beta 2 [ 10586 ] => |
Modified by: @pavel-zotovstatus: 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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
The text was updated successfully, but these errors were encountered: