Skip to content
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

Open
firebird-automations opened this issue May 3, 2007 · 9 comments
Open

Comments

@firebird-automations
Copy link
Collaborator

firebird-automations commented May 3, 2007

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 12000 ] => Firebird [ 15558 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE2937 [ CORE2937 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE2963 [ CORE2963 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 1 [ 10332 ]

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 2 [ 10586 ]

Fix Version: 3.0 Beta 1 [ 10332 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 2 [ 10586 ] =>

@firebird-automations
Copy link
Collaborator Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants