|
If you were logged in you would be able to see more operations.
|
|
|
|
Environment:
|
W10 x64 FB3 build 32268
|
|
Issue Links:
|
Relate
|
|
This issue relate to:
|
|
CORE-5481
Available indices are not used in some cases if ORDER BY expression is a filtered one
|
|
|
|
|
CORE-5965
FB3 Optimiser chooses less efficient plan than FB2.5 optimiser
|
|
|
|
|
|
|
|
| QA Status: |
Done successfully
|
|
create table test1 (ia integer not null,id integer not null, it integer not null, dt date not null, primary key (ia,id));
create table test2 (ia integer not null, it integer not null, dt date not null, primary key (ia,dt,it));
alter table test1 add CONSTRAINT fk_test2 FOREIGN key (ia,dt,it) REFERENCES test2 (ia,dt,it)
select * from test1
where ia=1 and dt='01/01/2015' and it=1
order by id;
plan : PLAN (TEST1 ORDER RDB$PRIMARY249)
performance very poor
the same problem with :
select id from test1
where ia=1 and dt='01/01/2015' and it=1
group by id
where without order or group works fine
|
|
Description
|
create table test1 (ia integer not null,id integer not null, it integer not null, dt date not null, primary key (ia,id));
create table test2 (ia integer not null, it integer not null, dt date not null, primary key (ia,dt,it));
alter table test1 add CONSTRAINT fk_test2 FOREIGN key (ia,dt,it) REFERENCES test2 (ia,dt,it)
select * from test1
where ia=1 and dt='01/01/2015' and it=1
order by id;
plan : PLAN (TEST1 ORDER RDB$PRIMARY249)
performance very poor
the same problem with :
select id from test1
where ia=1 and dt='01/01/2015' and it=1
group by id
where without order or group works fine |
Show » |
made changes - 10/Jan/16 10:38 AM
| Field |
Original Value |
New Value |
|
Assignee
|
|
Dmitry Yemanov
[ dimitr
]
|
made changes - 10/Jan/16 10:41 AM
|
Summary
|
Wrong Plan Used
|
Compound index cannot be used for filtering in some ORDER/GROUP BY queries
|
|
Affects Version/s
|
|
3.0 RC1
[ 10584
]
|
|
Affects Version/s
|
3.0 Beta 2
[ 10586
]
|
|
made changes - 20/Nov/18 06:20 PM
|
Status
|
Open
[ 1
]
|
Resolved
[ 5
]
|
|
Fix Version/s
|
|
4.0 Beta 1
[ 10750
]
|
|
Fix Version/s
|
|
3.0.5
[ 10885
]
|
|
Resolution
|
|
Fixed
[ 1
]
|
made changes - 09/Dec/18 01:56 PM
|
Status
|
Resolved
[ 5
]
|
Resolved
[ 5
]
|
|
QA Status
|
No test
|
Done successfully
|
made changes - 09/Dec/18 01:56 PM
|
Status
|
Resolved
[ 5
]
|
Closed
[ 6
]
|
|
Bug
