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

Make optimizer to consider MIN/MAX optimization when making decision about join order [CORE4428] #4748

Closed
firebird-automations opened this issue May 15, 2014 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @sim1984

Is related to CORE1482
Duplicates CORE4798

it's extended ticket CORE1482

create table t1 (
id int not null
);

create table t2 (
id int not null,
t1_id int not null
);

set term ^;

execute block
as
declare i int;
begin
i = 1;
while (i <= 100) do
begin
insert into t1(id) values(:i);
i = i + 1;
end
i = 1;
while (i < 1000000) do
begin
insert into t2(id, t1_id) values(:i, :i / 100000 + 1);
i = i + 1;
end
end^

set term ;^

commit;

alter table t1
add constraint pk_t1 primary key(id);

alter table t2
add constraint pk_t2 primary key(id);

alter table t2
add constraint fk_t2_t1 foreign key(t1_id) references t1(id);

select min(http://t1.id)
from t1
join t2 on http://t1.id = t2.t1_id;

Select Expression
-> Aggregate
-> Nested Loop Join (inner)
-> Table "T2" Full Scan
-> Filter
-> Table "T1" Access By ID
-> Bitmap
-> Index "PK_T1" Unique Scan

     MIN 

============
1

Current memory = 11621440
Delta memory = 547712
Max memory = 11672536
Elapsed time= 3.302 sec
Buffers = 2048
Reads = 11421
Writes = 0
Fetches = 6026958 <-- no good

vs

select min(http://t2.id)
from t1
join t2 on http://t1.id = t2.t1_id;

Select Expression
-> Aggregate
-> Nested Loop Join (inner)
-> Table "T2" Access By ID
-> Index "PK_T2" Full Scan
-> Filter
-> Table "T1" Access By ID
-> Bitmap
-> Index "PK_T1" Unique Scan

     MIN 

============
1

Current memory = 11840168
Delta memory = 1672
Max memory = 11872248
Elapsed time= 0.058 sec
Buffers = 2048
Reads = 0
Writes = 0
Fetches = 38 <-- good

vs

same as 1 query, but much faster

select http://t1.id
from t1
join t2 on http://t1.id = t2.t1_id
order by http://t1.id rows 1;

Select Expression
-> First N Records
-> Nested Loop Join (inner)
-> Table "T1" Access By ID
-> Index "PK_T1" Full Scan
-> Filter
-> Table "T2" Access By ID
-> Bitmap
-> Index "FK_T2_T1" Range Scan (full match)

      ID 

============
1

Current memory = 11933608
Delta memory = 52288
Max memory = 11933760
Elapsed time= 0.062 sec
Buffers = 2048
Reads = 0
Writes = 0
Fetches = 137 <-- good

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE1482 [ CORE1482 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Component: Engine [ 10000 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue duplicates CORE4798 [ CORE4798 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

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