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

Conditions like WHERE <field> = <cursor>.<field> don't use existing index [CORE4675] #4984

Closed
firebird-automations opened this issue Jan 28, 2015 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

DDL:

recreate table ttt(id int primary key using index pk_ttt_id, x int, y int);
commit;
insert into ttt select row_number()over(), rand()*10, rand()*100 from rdb$types, rdb$types rows 10000;
commit;
create index ttt_x on ttt(x);
commit;

Test-1 (fast):

set term ^;
execute block as
declare a_x int;
declare v_id int;
declare v_x int;
declare v_y int;
declare c_upd cursor for (select id, x, y from ttt where x = :a_x);
begin
a_x = 5;
open c_upd;
while (1=1) do begin
fetch c_upd into v_id, v_x, v_y;
if (row_count = 0) then leave;
update ttt v set y = c_upd.x, x = c_upd.y
where http://v.id = :v_id; ------------------------------ ::: key is specified by VARIABLE which has value from FETCH statement
end
close c_upd;
end
^ set term ;^
rollback;

Trace-1:

Select Expression
-> Filter
-> Table "TTT" as "C_UPD TTT" Access By ID
-> Bitmap
-> Index "TTT_X" Range Scan (full match)
Select Expression
-> Filter
-> Table "TTT" as "V" Access By ID
-> Bitmap
-> Index "PK_TTT_ID" Unique Scan
0 records fetched
26 ms, 165 read(s), 15 write(s), 18741 fetch(es), 3034 mark(s)

Table Natural Index Update Insert
************************************************************************
RDB$INDICES 14
RDB$RELATION_CONSTRAINTS 200
TTT 2016 1008

Test-2 (VERY slow):

set term ^;
execute block as
declare a_x int;
declare v_id int;
declare v_x int;
declare v_y int;
declare c_upd cursor for (select id, x, y from ttt where x = :a_x);
begin
a_x = 5;
open c_upd;
while (1=1) do begin
fetch c_upd; -- into v_id, v_x, v_y;
if (row_count = 0) then leave;
update ttt v set y = c_upd.x, x = c_upd.y
where http://v.id = c_upd.id; --------------------------------- ::: key is specified by CURSOR field using "cursor name + dot + field" syntax
end
close c_upd;
end
^ set term ;^
rollback;

Trace-2:

Select Expression
-> Filter
-> Table "TTT" as "C_UPD TTT" Access By ID
-> Bitmap
-> Index "TTT_X" Range Scan (full match)
Select Expression
-> Filter
-> Table "TTT" as "V" Full Scan
0 records fetched
8669 ms, 20317223 fetch(es), 3015 mark(s)

Table Natural Index Update
**************************************************************
TTT 10080000 1008 1008

Commits: 0707f3a FirebirdSQL/fbt-repository@f4d2c2a FirebirdSQL/fbt-repository@67f215a

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

Test uses MON$ tables to gather statistics snapshot before and after each of query.
Database that is restored (''mon-stat-gathering-N_M.fbk') contains procedures for gathering statistics and view for displaying results in convenient form. In particular, this view has columns that show DIFFERENCE of natural and indexed reads, and thus we can estimate performance.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 Beta 1 [ 10332 ]

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Pavel, I think that a better subject for this case is:

UPDATE \.\.\. WHERE <columnname\> = <cursor\>\.<columnname\> does not use existing index

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Sean,

no problem: i dont mind if you will change it.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Update of table using WHERE id = <cursor>.<id> ignores index on field ID => Conditions like WHERE <field> = <cursor>.<field> don't use existing index

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

Test Details: Test uses MON$ tables to gather statistics snapshot before and after each of query.
Database that is restored (''mon-stat-gathering-N_M.fbk') contains procedures for gathering statistics and view for displaying results in convenient form. In particular, this view has columns that show DIFFERENCE of natural and indexed reads, and thus we can estimate performance.

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