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

Bad optimization of some operations with views containing subqueries [CORE5393] #5666

Closed
firebird-automations opened this issue Nov 8, 2016 · 4 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Boltik Evgeny (bolt)

Bug is caused by internally created derived expressions being based on all view streams, including streams burried inside subqueries, etc. This is causing various optimization issues due to nested subqueries being non-computable at the moment.

Test case may look a bit complicated as it exploits the fact that simple field references become wrapped by derived expressions if located inside an explicit cursor. But I suspect this issue may be visible in other cases too.

create table test (id int primary key, col int);
commit;

insert into test (id, col) values (1, 1);
insert into test (id, col) values (2, 2);
commit;

-- view must contain a subquery
create or alter view v_test (id1, id2, col1, col2, dummy)
as
select http://t1.id, http://t2.id, t1.col, t2.col, (select 1 from rdb$database)
from test t1 join test t2 on t1.col = http://t2.id;

-- trigger makes the view updatable
create trigger t_v_test
for v_test before update
as
begin
end;

set plan;

set term ^;

execute block
as
begin
for select id1 from v_test as cursor c do
begin
update v_test set col1 = 1
where id1 = c.id1;
-- where current of c;

update v\_test set col1 = 1
where id1 = c\.id1;

end
end^

set term ;^

PLAN (V_TEST RDB$DATABASE NATURAL)
PLAN JOIN (V_TEST T1 INDEX (RDB$PRIMARY1), V_TEST T2 INDEX (RDB$PRIMARY1))
PLAN (V_TEST RDB$DATABASE NATURAL)
PLAN JOIN (V_TEST T1 INDEX (RDB$PRIMARY1), V_TEST T2 INDEX (RDB$PRIMARY1))
PLAN JOIN (C V_TEST T1 NATURAL, C V_TEST T2 INDEX (RDB$PRIMARY1))

The only naturally-read join is cursor C, this is expected. Both updates utilize the primary key index for table T1. So far so good.

set term ^;

execute block
as
begin
for select id1 from v_test as cursor c do
begin
update v_test set col1 = 1
-- where id1 = c.id1;
where current of c;

update v\_test set col1 = 1
where id1 = c\.id1;

end
end^

set term ;^

PLAN (C V_TEST RDB$DATABASE NATURAL)
PLAN (V_TEST RDB$DATABASE NATURAL)
PLAN JOIN (V_TEST T1 NATURAL, V_TEST T2 INDEX (RDB$PRIMARY1))
PLAN JOIN (C V_TEST T1 NATURAL, C V_TEST T2 INDEX (RDB$PRIMARY1))

The first update is not reported in the plan because it's based on the same cursor as the select itself. However, the second update is unable to utilize the primary key index for table T1 anymore.

In the production database, this issue is causing 100x degradation in execution time.

Commits: 05c3ccc 0e77b3e

====== Test Details ======

1) Plans differ on 3.0.2 and 4.0 thus they are excluded from the output.
2) Performance of 4.0 significantly _WORSE_ than of 3.0.2, sent letter to dimitr, 11.11.2016 13:47.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

reporter: Dmitry Yemanov [ dimitr ] => Boltik Evgeny [ bolt ]

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0.2 [ 10785 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Done with caveats

Test Details: 1) Plans differ on 3.0.2 and 4.0 thus they are excluded from the output.
2) Performance of 4.0 significantly _WORSE_ than of 3.0.2, sent letter to dimitr, 11.11.2016 13:47.

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