Issue Details (XML | Word | Printable)

Key: CORE-1239
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Jojakim Stahl
Votes: 4
Watchers: 5
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Non-Optimal plan for a left join c inner join b

Created: 03/May/07 05:23 AM   Updated: 21/Nov/16 08:15 PM
Component/s: Engine
Affects Version/s: 2.0.1
Fix Version/s: None

Environment: SuperServer, WinXP SP2
Issue Links:
Duplicate
 

QA Status: Deferred
Test Details: Deferred until changes in optimizer will be done. Test will be common for this ticket and core-2963


 Description  « Hide
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> 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);


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.