Issue Details (XML | Word | Printable)

Key: CORE-5393
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Boltik Evgeny
Votes: 0
Watchers: 1
Operations

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

Bad optimization of some operations with views containing subqueries

Created: 08/Nov/16 07:57 AM   Updated: 11/Nov/16 05:19 PM
Component/s: Engine
Affects Version/s: 4.0 Initial, 3.0.0, 3.0.1
Fix Version/s: 3.0.2, 4.0 Alpha 1

QA Status: 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.


 Description  « Hide
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 t1.id, t2.id, t1.col, t2.col, (select 1 from rdb$database)
from test t1 join test t2 on t1.col = 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.

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