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

Optimizer does not use index when selecting from "unioned" view and WHERE-clause has comparison with result of singletone subquery [CORE4976] #5267

Open
firebird-automations opened this issue Oct 24, 2015 · 3 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Is related to CORE4049

Test-1.
#⁠#⁠#⁠#⁠#⁠

This sample will work FINE (it's shown here only for comparison with 2nd one):

create or alter view v_simple as
select 1 id from rdb$database;

recreate table thead(id int primary key using descending index thead_pk, x int);
recreate table tdata(x int, y int);
create index tdata_x on tdata(x);

create or alter view v_simple as ------------------ THIS IS _NOT_ "UNIONED"-VIEW, IT'S JUST SINGLE-TABLE PROJECTION
select * from tdata;

insert into thead(id, x) values(1, 1);
insert into tdata(x, y) select 1, rand()*10000 from rdb$types;
commit;

set statistics index thead_pk;
set statistics index tdata_x;

set planonly;
-- set echo on;

select v.*
from v_simple v
where
v.x = 1; ------------------------------------------------------------------------------------ [ 1 ]

select v.*
from v_simple v
where
v.x = (select h.x from thead h order by id desc rows 1); --------------------- [ 2 ]

Output:
#⁠#⁠#⁠#⁠#⁠

// for [ 1 ]:
PLAN (V TDATA INDEX (TDATA_X))

// for [ 2 ]:
PLAN (H ORDER THEAD_PK)
PLAN (V TDATA INDEX (TDATA_X))

So, index TDATA_X is used in both cases, regardless of expression form in WHERE-clause (i.e. search by literal or by result of single-tone subquery).

Test-2.
#⁠#⁠#⁠#⁠#⁠

This sample uses "UNIONED" view and optimizer will NOT use indexes of underlying tables if WHERE clause contains comparison with subquery.

create or alter view v_unioned as select 1 id from rdb$database;

recreate table thead_u(id int primary key using descending index thead_u_pk, x int);
recreate table tdata_1(x int, y int, z int);
recreate table tdata_2(x int, y int, z int);

create index tdata_1_x on tdata_1(x);
create index tdata_2_x on tdata_2(x);

--create index tdata_1_xy on tdata_1(x, y);
--create index tdata_2_xy on tdata_2(x, y);

create or alter view v_unioned as
select * from tdata_1
union all
select * from tdata_2;

insert into thead_u(id, x) values(1, 1);
insert into tdata_1(x, y) select 1, rand()*10000 from rdb$types;
insert into tdata_2(x, y) select 1, rand()*10000 from rdb$types;
commit;

set statistics index thead_u_pk;
set statistics index tdata_1_x;
set statistics index tdata_2_x;

set planonly;
--set echo on;

select v.*
from v_unioned v
where
v.x = 1; ------------------------------------------------------------------------------- [ 1 ]

select v.*
from v_unioned v
where
v.x = (select h.x from thead h order by id desc rows 1); ----------------- [ 2 ]

Output:
#⁠#⁠#⁠#⁠#⁠#⁠

// for [ 1 ] - all OK:
PLAN (V TDATA_1 INDEX (TDATA_1_X), V TDATA_2 INDEX (TDATA_2_X))

// for [ 2 ] -- get NATURAL reads:
PLAN (H ORDER THEAD_PK)
PLAN (V TDATA_1 NATURAL, V TDATA_2 NATURAL)

PS.
Tested on WI-V2.5.5.26936, WI-V3.0.0.32114

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue is related to CORE4049 [ CORE4049 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

This seems to be (at least) related to the previously reported case, if not a duplicate of same.

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