Wrong or missing IS NULL optimization (regression)
--------------------------------------------------
Key: CORE-3449
URL: http://tracker.firebirdsql.org/browse/CORE-3449
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.0
Reporter: Pavel Cisar
Test case:
/* prepare */
create table tmp_tbl1 (fld1 integer, fld2 integer, fld3 integer);
create index tmp_tbl1_fld123 on tmp_tbl1(fld1, fld2, fld3);
create index tmp_tbl1_fld2 on tmp_tbl1(fld2);
commit;
set term ^;
create or alter procedure tmp_sp1
as
declare variable I integer;
begin
i=0;
while (i<10000) do begin
i=i+1;
insert into tmp_tbl1 values (1, :i, 2);
end
end
^
set term ;^
commit;
execute procedure tmp_sp1;
commit;
SET STATISTICS INDEX TMP_TBL1_FLD123;
SET STATISTICS INDEX TMP_TBL1_FLD2;
commit;
/* test */
select count(*) from tmp_tbl1 where fld1=1 and fld2 is null;
/*
1.5:
PLAN (TMP_TBL1 INDEX (TMP_TBL1_FLD2,TMP_TBL1_FLD123))
COUNT
============
0
Current memory = 53311040
Delta memory = 16164
Max memory = 53467732
Elapsed time= 0.01 sec
Buffers = 6000
Reads = 0
Writes 0
Fetches = 55
2.5:
PLAN (TMP_TBL1 INDEX (TMP_TBL1_FLD123)) <<<<< different plan
COUNT
============
0
Current memory = 53597056
Delta memory = 21576
Max memory = 53936000
Elapsed time= 0.01 sec
Buffers = 6000
Reads = 0
Writes 0
Fetches = 20022 <<<<<<< causes significant performance degradation when
executed many times under load
*/
Under v2.5 FLD2 IS NULL predicate is either not optimized using index or is
handled wrongly. Note that there are no rows with NULL value in FLD2, so there
shouldn't be so much fetches. The number of fetches indicate that only fld1=1
condition is handled via index (there is only 1 value in all 10k rows).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Fulfilling the Lean Software Promise
Lean software platforms are now widely adopted and the benefits have been
demonstrated beyond question. Learn why your peers are replacing JEE
containers with lightweight application servers - and what you can gain
from the move. http://p.sf.net/sfu/vmware-sfemails
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
